--- In firebird-support@yahoogroups.com, Hans <hhoogstraat@...> wrote:

>
> Hello All,
>
> In FB.1.5.xx
>
> DELETE FROM TICKETITEMS WHERE TICKET_UNIQUENUM
> NOT IN (SELECT TICKET_UNIQUENUM FROM TICKETS);
>
> executes in a fraction of a second, since it uses
>
> PLAN (TICKETS INDEX (RDB$PRIMARY15))
> PLAN (TICKETITEMS NATURAL)
>
> or
>
> DELETE FROM TICKETITEMS ti WHERE ti.TICKET_UNIQUENUM
> NOT IN (SELECT t.TICKET_UNIQUENUM FROM TICKETS t);
>
> executes in the same fraction of a second and uses
>
> PLAN (T INDEX (RDB$PRIMARY15))
> PLAN (TI NATURAL)
>
> ===================================================
>
> However in FB.2.0.0.0 the same delete SQL
>
> DELETE FROM TICKETITEMS WHERE TICKET_UNIQUENUM
> NOT IN (SELECT TICKET_UNIQUENUM FROM TICKETS);
>
> execution takes forever, since it selects
>
> PLAN (TICKETS NATURAL)
> PLAN (TICKETITEMS NATURAL)
>
> or
>
> DELETE FROM TICKETITEMS ti WHERE ti.TICKET_UNIQUENUM
> NOT IN (SELECT t.TICKET_UNIQUENUM FROM TICKETS t);
>
> uses
>
> PLAN (T NATURAL)
> PLAN (TI NATURAL)
>
> ===================================================
>
> How can I force a use of an index in FB2.0.0.0

Hans,

IIRC, somewhere in the Firebird beta cycle the optimiser was changed
to not automatically convert a not in to an exists that would give the
desired plan because of a bug that could lead to the wrong data being
returned. I do not recall the details and do not have a link though.

Adam