Hi,

> Change your query to
>
> SELECT B.TB_INSPECT_ID
> FROM TB_INSPECT_GROUP A inner JOIN TB_INSPECT B
> ON (B.INSPECTION_GROUP_ID = A.TB_INSPECT_GROUP_ID)
> WHERE A.DEST_LOT_ID IS NULL
> AND A.SO_DET_ID IS NOT NULL
> AND (B.WRITE_UP_DET_ID IS NULL
> AND B.STATUS IN ('PASSED','USE AS IS') OR 2=0)
>
> This should prevent IDX_TB_INSPECT_WUP_DET and

IDX_TB_INSPECT_STATUS

> from being used with this query.
>

PLAN JOIN (A INDEX (RDB$FOREIGN132),B INDEX (RDB$FOREIGN112)):
PLAN JOIN (A INDEX (RDB$FOREIGN132),B INDEX (RDB$FOREIGN112))

Adapted plan:
PLAN JOIN (A INDEX
(FK_INSPECT_GROUP_LOT_DESTFK_INSPECT_GROUP$FOREIGN112))

Yes that was the problem with "inner join"! Now is as fast as LEFT
JOIN!

>
> I think your issue largely is due to the assumption that left

joins

> are better than inner joins combined with the creation of too many
> indexes.

Now, the big issue, the original one!

How can I expand this same query to join another table (FK) from A
or B and visit it ONLY 237 times?????

An IN Clause sounds great but doesn't work! :(

Thanks

Juan Jose.