At 02:26 PM 12/05/2009, you wrote:

>Hi Sean
>
>I agree this query is not very well designed, but it's a simplified version
>of a query from one of our applications. I would have put an index on
>excludedfile.pathname to speed this example up.
>
>However, there are many other filters other than allfile.filename = '1.TXT'
>in real life. The point is no matter these filters have index on them or
>not, the optimiser should apply them to eliminate unwanted records before
>running the subquery which basically is a JOIN.

No, it's not a JOIN. Your search condition is a correlated subquery and, furthermore, it's a NOTed correlated subquery. If you want a JOIN query then write it as a JOIN query. Test it (both ways) with typical data to figure out which method gets the better performance, since the actual conditions will determine the plan heuristically.

And, as Sean said, the heuristics "intelligence" was improved between 2.0 and 2.1.

./heLen