--- In firebird-support@yahoogroups.com, Helen Borrie wrote:

> >Here is the Query
> >
> >select s.s_finedept, sum(I.D_Price * I.n_unitsordered) totalval
> >from gen_tabl_requisitionitem I
> >inner join gen_tabl_stockitem S on S.PKS_STOCKITEMID =
> >I.FKS_STOCKITEMID
> >where FKN_PICKSLIPID = 260874
> >or (fkn_requisitionid = 222824and n_unitsordered > 0
> > and d_datepicked is null and fkn_parentid = 0)
> >group by s.s_finedept order by s.s_finedept, pkn_requisitionitemid
> >
> >
> >Query Plan with index disabled
> >
> >Plan
> >PLAN SORT (SORT (JOIN (S NATURAL,I INDEX
> >(GEN_TABL_REQUISITIONITEM_IDX3,GEN_TABL_REQUISITIONITEM_IDX2,
> >RDBPRIMARY90))))
> >
> >Query Plan with index enabled
> >
> >Plan
> >PLAN SORT (SORT (JOIN (S NATURAL,I INDEX
> >(GEN_TABL_REQUISITIONITEM_IDX3,GEN_TABL_REQUISITIONITEM_IDX2,
> >RDBPRIMARY90,GEN_TABL_REQUISITIONITEM_IDX5))))
> >
> >If I take out the first part of the "or" out it runs fine
> >
> >select s.s_finedept, sum(I.D_Price * I.n_unitsordered) totalval
> >from gen_tabl_requisitionitem I
> >inner join gen_tabl_stockitem S on S.PKS_STOCKITEMID =
> >I.FKS_STOCKITEMID
> >where (fkn_requisitionid = 222824 and n_unitsordered > 0
> > and d_datepicked is null and fkn_parentid = 0)
> >group by s.s_finedept order by s.s_finedept, pkn_requisitionitemid
> >
> >Plan
> >PLAN SORT (SORT (JOIN (I INDEX
> >(RDB$PRIMARY90,GEN_TABL_REQUISITIONITEM_IDX5),S INDEX
> >(GEN_TABL_STOCKITEM_IDX2))))
>
> Stay tuned (if you can stay awake <g>) for others who love these
> puzzles to come on line.
>
> heLen

Yes, I do like these puzzles, even though I must admit I prefer queries with lots of ANDs over those with one or more ORs.

Generally, I think that indexes should be carefully thought out rather than just added whenever a query may benefit from it. Rather, start by tweaking your SQL statement until a reasonable plan is achieved.

I think that by changing a small part of the query, you may get the two first alteratives to yield the same performance. If I guess correctly, changing from

and n_unitsordered > 0

to

and (n_unitsordered > 0 or 2=0)

is all that is required. Still, this does not make the plan perfect. At first sight, the third plan seems a lot better than the first two (even though it redundantly uses your new index). I'd say the ideal plan probably is

PLAN SORT (SORT (JOIN (I INDEX (GEN_TABL_REQUISITIONITEM_IDX3, GEN_TABL_REQUISITIONITEM_IDX2),S INDEX(rdb$primary??))))

Helen guessed that you had separately defined indexes on keys. Is GEN_TABL_STOCKITEM_IDX2 one such index? If so, REMOVE IT.

How to force the query to use I before S in the plan? Well, that may or may not be possible. One thing you could try, is to add something indexed always evaluating to true, e.g.

WHERE ...
AND I.fkn_requisitionid < 999999999 //or other indexed field of I

This should tempt the optimizer even more to select I before S, but may or may not be what you want to do. Only trial and error will reveal the answer.

HTH,
Set