10.07.2017 14:20, Paul Reeves wrote:
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.
Unless the engine physically cannot execute your plan. This was exactly
the case.
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 :-)
You seem to believe that the engine can execute the INDEX plan without
lower/upper bounds (which require e.emp_no to be known in advance). In
theory, it could. In practice, it's pointless and thus prohibited by the
optimizer.
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))
This is still a single stream and bitmap based on two index scans.
Solution with two streams (chosen conditionally at runtime) is
implemented in FB3 but only for some specific syntax. Perhaps your
COALESCE trick could join this special category, but it doesn't yet.
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 ? )
The optimizer don't take NOT NULL constraints into account. They don't
have existence locks and thus can affect query results if dropped in the
meantime.
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
