Thank you, Adam.

Been playing a bit more with FB2.0.0.0 and discovered that oddly enough

DELETE FROM TICKETITEMS WHERE TICKET_UNIQUENUM
NOT IN (SELECT TICKET_UNIQUENUM FROM TICKETS
WHERE TICKET_UNIQUENUM >= 0)
uses
PLAN (TICKETITEMS NATURAL)

or

DELETE FROM TICKETITEMS WHERE TICKET_UNIQUENUM
NOT IN (SELECT TICKET_UNIQUENUM FROM TICKETS
WHERE TICKET_UNIQUENUM >= 0 OR TICKET_UNIQUENUM < 0)
uses
PLAN (TICKETS INDEX (RDB$PRIMARY15, RDB$PRIMARY15))
PLAN (TICKETITEMS NATURAL)
but execute for ever

however
DELETE FROM TICKETITEMS WHERE TICKET_UNIQUENUM
NOT IN (SELECT TICKET_UNIQUENUM FROM TICKETS
WHERE TICKETS.TICKET_UNIQUENUM = TICKETITEMS.TICKET_UNIQUENUM)
uses
PLAN (TICKETS INDEX (RDB$PRIMARY15))
PLAN (TICKETITEMS NATURAL)

and works perfectly and fast using the same primary index :)

Best Regards
Hans

----- Original Message -----
From: "Adam" <s3057043@...>
To: <firebird-support@yahoogroups.com>
Sent: Friday, December 01, 2006 3:32 PM
Subject: [firebird-support] Re: How can I force a use of an index in
FB2.0.0.0


> --- 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
>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>