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.

Cheers
Huan

2009/5/12 Leyne, Sean <Sean@...>

>
>
> 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
>
>
>

[Non-text portions of this message have been removed]