Bob Murdoch wrote:

> I have the following query:
>
> SELECT
> RETURN_TYPE, RETURN_CODE, ORIG_DOCUMENT
> FROM
> RETURN_SALE
> WHERE
> (DEL_DATE = '3/8/2003') AND (TRACER_NBR = '30462815') AND
> (ACCOUNT_ID = 1) AND (RETURN_TYPE <> '2')
>
> Which results in a 3ms execution time and the following plan:
> PLAN (RETURN_SALE INDEX (RDB$PRIMARY1,RETURN_SALE_DELDATE_IDX))
>
> if I add this to the where clause:
>
> AND (XRDS644_BUSINESS_DATE <= '3/10/2003')
>
> The execution time jumps to 254ms, and the following plan:
> PLAN (RETURN_SALE INDEX
>

(RDB$PRIMARY1,RETURN_SALE_DELDATE_IDX,RETURN_SALE_BATCH_DATE_IDX))

>
>

instead try adding :

AND ((XRDS644_BUSINESS_DATE <= '03/10/2003' OR (0<>0))



This will trick the optimizer into not using the index on that column (which in this
particular case is better to ignore)).

Usually most queries which specify all rows occuring prior to a particular date should not
use the index on that date column as most rows tend to satisfy the query, and a NATURAL scan
in more efficient if will match a large fraction of possible rows.

I dont know if this trick will work on FB, but it works on Interbase (and on DB2 incidentally).

If the optimizer gets too smart this trick will stop working.

David Z