Ruan,

> Query:
>
> select
> AllFile.*
> from AllFile
> where
> allfile.filename = '1.TXT' and
> not exists (
> select 1
> from excludedfile
> where
> excludedfile.pathname = allfile.pathname and
> (excludedfile.filename = allfile.filename or
> excludedfile.filename = '*')
> )
>
> Both 2.1.2. and 2.0.3 choose this plan:
>
> PLAN (EXCLUDEDFILE INDEX (EXCLUDEDFILE_IDX1, EXCLUDEDFILE_IDX1))
> PLAN (ALLFILE NATURAL)
>
> 2.0.3 does 3 natural reads on AllFile, and 2 index reads on

ExcludedFile,

> because it applies allfile.filename = '1.TXT' first.
>
> 2.1.2 does 3 natural reads on AllFile, but also 6 index reads on
> ExcludedFile, because it applies allfile.filename = '1.TXT' last.

Your example is not "real world", the optimizer was improved in 2.1.2.

Your problem is that you need an index on ALLFile.Filename

That would improve both 2.0.3 and 2.1.2


Sean