On Mon, 10 Jul 2017 12:31:43 +0300 Dmitry Yemanov wrote
>
> > The other question I asked was...
> >
> > why can't I force the plan...
> >
> > PLAN (E INDEX (RDB$PRIMARY7))
>
> Because it's invalid in this case.
>
I can understand that this plan might appear to be invalid from the
perspective of the optimiser. But surely the whole point of adding the
PLAN clause is because I think I know better than the optimiser what I
want. So to return to the original query:
where e.EMP_NO = COALESCE( ?, e.emp_no )
surely I ought to be able to add
PLAN (E INDEX (RDB$PRIMARY7))
Then if the parameter resolves to a value at run-time the index will be
used. Obviously if the parameter is null performance would be much
worse than a natural scan because the engine must walk the index pages
and then walk the data pages. But the customer is always right,
surely :-)
( I also understand there is a downside to the customer always being
right argument - one bad customer could screw everything up for all the
others. But is that argument being used here or is it just because of
the way the optimiser works? )
> > What are the rules for deciding when a PLAN statement will be
> > rejected?
> >>From one logical point of view if have this sort of query
> >
> > select * from mytable m where afield = whatever;
> >
> > and I add
> >
> > plan (m index(myindex ))
> >
> > the engine should accept that. Shouldn't it?
>
> It depends on "whatever". If it's literal or independent expression
> or priorly evaluated field, index scan can be used. If it includes a
> field from the same stream, it cannot.
>
Why not create a second stream in that case?
that seems to be what happens when I create this query:
SELECT *
FROM employee
WHERE
EMP_NO = 2
OR
EMP_NO is null
this plan is chosen...
PLAN (EMPLOYEE INDEX (RDB$PRIMARY7, RDB$PRIMARY7))
Because there are two streams the fetches and the indexed reads more or
less double because of the OR clause.
(Again, a subject for another day is why does the optimiser even bother
with the second stream when EMP_NO can never be null ? )
Anyway, none of the above is any sort of criticism. I'm just trying to
get a better understanding of the rules and maybe find out where things
are broken that could be fixed easily (or hacked around).
Paul
--
Paul Reeves
http://www.ibphoenix.com
Supporting users of Firebird
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
