17.06.2020 21:34, Adriano dos Santos Fernandes wrote:
So it's about this, correct?
-- Uses two index segments
select registro_sec_roest from dp_registro_oest where registro_pri_roest
= 1 and registro_sec_roest = 1;
-- Uses one index segment - more generalization of IN problem
select registro_sec_roest from dp_registro_oest where registro_pri_roest
= 1 and registro_sec_roest in (1, 2);
-- Uses one index segment - IN problem
select registro_sec_roest from dp_registro_oest where registro_pri_roest
= 1 and (registro_sec_roest = 1 or registro_sec_roest is null);
All cases use two segments. However, this:
select registro_sec_roest from dp_registro_oest
where registro_pri_roest = 1 and
(registro_sec_roest = 1
or registro_sec_roest is null
or ?param is null);
will use only one segment.
Because whether second one can be used depends on (?param is null).
When choice is between no matches at all (NATURAL plan),
ConditionalStream is injected to solve that. However, it's not injected
when the choice is between good and bad indices, or between one-segment
or multi-segment matches.
Dmitry
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
