Tom,

> Can anyone explain why FB uses all 3 in the OR query yet only 2 in the AND
> query (See definitions below)?


> select * from target
> where
> upper(company) starting with 'A' and
> upper(firstname) starting with 'B' and
> upper(lastname) starting with 'C'
>
> PLAN (TARGET INDEX (I_TARGET_LASTNAME, I_TARGET_FORENAME))

The engine looked at the selectivity value of the available indexes and decided that these 2 indexes provided the best outcome.

Remember that even if the engine uses indexes to identify target rows, due to the multi-generational architecture/MVCC, the engine still reads the rows to confirm that the rows have the defined values. So, the engine decided that evaluating the company name value at this stage would provide the best query performance.


Sean