At 04:51 PM 29/07/2004 -0700, you wrote:

>Hello,
>
>I have a procedure that is using an index in one select statement, but not
>the another identical one. The index for the select statement defined in
>lines 63 through 77 is used and as indicated with the following plan:
>
>Plan:
>----------------------------------------------------------------------------
>----
>SORT (JOIN (S INDEX (SPECS_IDX1),JOIN (CR INDEX (CROSS_REFERENCE_IDX1),P
>INDEX (PARTS_IDX2))))
>
>The odd thing is that the select statement defined in lines 122 through 136
>(which is identical) does not use an index as indicated with this plan:
>
>Plan:
>----------------------------------------------------------------------------
>----
>SORT (JOIN (S INDEX (SPECS_IDX1),JOIN (CR NATURAL,P INDEX (PARTS_IDX2))))
>
>Can someone explain why that is and how I can force FB to use the correct
>index? Thanks a million.

They aren't identical. The first query has an inner join; whereas the
second query has an outer join. The assessment of the relative costs of
stream formation for inner and outer joins will have different
outcomes. You also might like to investigate whether you actually need
SELECT DISTINCT...in either or both queries.

The optimizer usually knows best; but you can force a plan by including a
PLAN statement at the end of the SELECT statement.

/heLen

>