On 18/06/2020 02:10, Dmitry Yemanov wrote:
> 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:
>
Looks like an empty table causes different behavior for (n1 = 1 and (n2
= 2 or n2 = 3)) to ((n1 = 1 and n2 = 2) or (n1 = 1 and n2 = 3)).

I suppose something like a Conditional Bitmap may work, where T12 is
index on T (N1, N2):

select * from t where (n1 = 1 and (n2 = 1 or n2 is null)) or (n1 = 1 and
? is null)

Select Expression
        -> Filter
            -> Table "T" Access By ID
                -> ***Conditional*** Bitmap Or    // was Bitmap Or
                    -> Bitmap Or
                        -> Bitmap
                            -> Index "T12" Range Scan (full match)
                        -> Bitmap
                            -> Index "T12" Range Scan (full match)
                    -> Bitmap
                        -> Index "T12" Range Scan (partial match: 1/2)


Adriano



Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Reply via email to