10.07.2017 12:17, Paul Reeves пишет:
On Fri, 7 Jul 2017 18:07:55 +0300 Dmitry Yemanov wrote
07.07.2017 17:51, Paul Reeves wrote:
I understand that evaluating COALESCE(?, e.emp_no ) at prepare time
may require a circular logic and is thus impractical
It cannot be done at runtime either (without deducting that e.emp_no is
the same in both paths of the condition).
but surely
the more important piece of information is in the
where e.emp_no =
A unique index exists on this column so surely the optimiser should
choose it instead of a natural scan ?
No, it cannot. Imagine "where T.A = T.B", it cannot use an index for
either A or B. Only full table scan is possible.
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.
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.
Dmitry
------------------------------------------------------------------------------
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
