Re: Avoid a subselect "where not equal to"?

From: Martin T. <bilbothebagginsbab5_at_freenet.de>
Date: 1 Aug 2006 23:20:44 -0700
Message-ID: <1154499644.162054.118990@m79g2000cwm.googlegroups.com>

Charles Hooper wrote:
> Martin T. wrote:
> > Ed Prochak wrote:
> > > Martin T. wrote:
> > > > andrew.fabbro_at_gmail.com wrote:
> > > > > (snipped)
> > > > >
> > > > (more snipped)
> >
> > I compared the two SELECTs though - the one with the NOT IN clause
> > seems to generate the nicer query plan with my 9i2 CBO than the one
> > with the outer join (of course it would depend on real data etc.)
> >
> > I would stick with the OPs query until someone prooves a better
> > solution (i.e. faster for real data or more readable).
> >

>

> In 8i, the method that I posted, if the data set is more than a couple
> dozen rows, will normally execute much faster than the subquery method
> - 50 to 100 times faster is not impossible. The inline view only needs
> to be resolved once, while the subquery needs to be resolved once per
> row in the result set.

>

> As indicated by Ed, different version of Oracle may handle the SQL
> statement differently, possibly rewriting the SQL statement into a more
> efficient form. Keep in mind that the query plan is just that - just
> an educated guess at the cost of the execution plan, and the
> anticipated access path. The database may not follow the query plan
> when the SQL statement is executed. A 10053 cost based optimizer trace
> of the actual SQL execution may provide clues, as should a 10046 trace.
> "Cost-Based Oracle Fundamentals" by Jonathan Lewis gives the scary
> details.

>

Thanks for sharing this info!
I'm a bit confused here though. I would have thought the NOT IN subquery would be unnested, so only be executed once.

But looking into the docs it says ...
"(...)the optimizer automatically unnests some(...) Uncorrelated IN subqueries (...)"
But then I read on and it says:
"(...)extended subquery unnesting(...) You can unnest an uncorrelated NOT IN subquery by specifying the HASH_AJ or MERGE_AJ hint in the subquery.(...)"
(9i, SQL Reference p8-14f)

Seems it doesn't do it with NOT IN, except if I explicitly tell it to. I guess we live and learn :)

best,
Martin Received on Wed Aug 02 2006 - 01:20:44 CDT