Hi,

> I think you are looking at the wrong "first" select. The 2 selects in
> question are found after the line:
>
> delete from TEMP$PARTS;
>
> The first select statement is directly after that line. The second is
> directly after:
>
> if (DPI <> :D3) then begin

^

btw, i recommand to use "if (DPI <> D3) then begin"

Are you talking about those two (remove unneeded info):

1)

for select distinct
<fields>
from PARTS P
right outer join CROSS_REFERENCE CR on (P.SKU = CR.SKU)
right outer join SPECS S on (CR.DPINUMBER = S.DPI)
where
S.DPI = :DPI
into
<variables>

with PLAN

> SORT (JOIN (S INDEX (SPECS_IDX1),JOIN (CR INDEX (CROSS_REFERENCE_IDX1),P
> INDEX (PARTS_IDX2))))

2)

for select distinct
<fields>
from PARTS P
right outer join CROSS_REFERENCE CR on (P.SKU = CR.SKU)
right outer join SPECS S on (CR.DPINUMBER = S.DPI)
where
S.DPI = :D3
into
<variables>

with PLAN

> SORT (JOIN (S INDEX (SPECS_IDX1),JOIN (CR NATURAL,P INDEX (PARTS_IDX2))))

It's indeed very weird that two different PLANs are picked by the engine.
I personally prefer always LEFT JOIN above RIGHT JOIN, but it shouldn't make
any difference. Just to see if the behaviour of the LEF JOIN is the same
could you change it too :

for select distinct
<fields>
from
SPECS S
LEFT JOIN CROSS_REFERENCE CR on (CR.DPINUMBER = S.DPI)
LEFT JOIN PARTS P on (P.SKU = CR.SKU)

If you're willing to give the metadata for this SP with the necessary
tables/indexes/etc.. i'm interested in it.

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://newsgroups.firebirdsql.info