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

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 1 Aug 2006 16:36:57 -0700
Message-ID: <1154475417.072018.216610@i3g2000cwc.googlegroups.com>

Martin T. wrote:
> Ed Prochak wrote:
> > Martin T. wrote:
> > > andrew.fabbro_at_gmail.com wrote:
> > > > (snipped)
> > > >
> > > > And that worked fine. However, someone else referred to this as a
> > > > "nasty" query, opining that "where not in (select...) queries are some
> > > > of the worst" in a performance sense.
> > > >
> > >
> > > But ... thats exactly what you want to know, isn't it?
> > > -> "Give me all new ones where no open ones exist."
> > > So I guess it's OK to ask Oracle exactly that :)
> > >
> > > I think this query can scale very well, with the right indexes ...
> > >
> > > best,
> > > Martin
> >
> > Indices alone won't help. Charles Hooper gave a good solution. An outer
> > join where you pick the rows with NULL works. But it all depends on
> > the version of ORACLE.
> >
> > Ed

>

> I wouldn't have been able to do what he did, so I didn't dare comment
> on it ;)

>

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

>

> whatever - just my 002

>

> best,
> Martin

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.

Note that there is a potential bug in the original poster's SQL statement:
Table1.id != (SELECT table1id FROM table2 WHERE status = 'open');

If the above select returns more than one row... Also, there is no restriction on the subquery to return only the one table1id in the subquery that is relevant to the row.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Tue Aug 01 2006 - 18:36:57 CDT