At 14:24 02.10.2002 +0000, you wrote:

>No - that contents.item line can even be removed completely - same
>story - but no matter how 'wrong' the SQL is - you'd still expect the
>same plan ?

Yes, I would - but I would be less angry with the optimizer if I was partly
guilty myself because I confused it. The only reason I can think of why it
would choose the second plan was if WAYBILL contained a lot less records
than ROUTING and there were many duplicates on ROUTING.MANIFEST so that
index alone wasn't very useful. You could possibly make plan B slightly
faster by eliminating the use of the index for ROUTING.MANIFEST altogether,
but it would at best be a marginal improvement and never get anywhere near
plan A.

I hate it when FB/IB does this kind of things and I have no idea how to
improve the query short of including the plan (which may have to change
after database restore if the index names are changed).

Maybe Ann or someone could give you useful advice if you provided some more
statistics about each database?

Set