On Fri, 7 Jul 2017 18:07:55 +0300 Dmitry Yemanov wrote
> 07.07.2017 17:51, Paul Reeves wrote:
>
> > But that doesn't answer all my questions...
> >
> > Given
> >
> > where e.EMP_NO = COALESCE(?, e.emp_no )
> >
> > and that there is an index on EMP_NO, why doesn't the optimiser
> > default to the index. After all, it is logically more likely that a
> > value will be passed in the where condition, rather than a NULL.
>
> To evaluate COALESCE, e.emp_no must be known. How it can be known
> before we start reading the table (via index scan)? Chicken and egg
> problem.
>
I understand that evaluating COALESCE(?, e.emp_no ) at prepare time
may require a circular logic and is thus impractical 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 ?
The other question I asked was...
why can't I force the plan...
PLAN (E INDEX (RDB$PRIMARY7))
This error is raised...
'cannot be used in the specified plan'
And a supplementary question (perhaps it merits a separate thread )
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?
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
