Hi,

> 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)
> ===================================================
>
> 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)

Change your NOT IN into a NOT EXISTS.
See also the release notes from Firebird 2.

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
General database developer support:
http://www.databasedevelopmentforum.com

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

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

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