You could try for FB 2:

select h.h_ronumber
from header h
where not exists (select 1 from account_totals a where a.header_id=h.h_header_id)

Regards,
Paul

>

--- In firebird-support@yahoogroups.com, "Paul R. Gardner" <gardnerp@...> wrote:
>
> I have a query that behaves differently in FB 2.0 vs. 1.5. The
> statement is:
>
> select h.h_ronumber from header h
> where h.h_header_id not in (select a.header_id from account_totals a)
>
> The FB 2.0 plan is: PLAN (A NATURAL)PLAN (H NATURAL)
> The FB 1.5 plan is: PLAN (A INDEX (IDX_ACCOUNT_TOTALS))PLAN (H NATURAL)
>
> FB 1.5 takes 0.047 seconds to run and FB 2.0 takes 21 minutes to run.
> It does return the exact same data.
>
> Of course the statement is much better written as:
>
> select h.h_ronumber from header h
> where not exists(select 1 from account_totals a where a.header_id =
> h.h_header_id)
>
> Is this an intentional change in the optimizer? I do know the statement
> was very poorly written whenever it was first done. It just seems that
> FB 2.0 should be able to generate the same or better plan than 1.5. My
> concern is only that upgrading to FB 2.0 might expose some other
> 'newbie' SQL code in the application that was working fine, and now
> won't be.
>
> Paul
>