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

From: Martin T. <bilbothebagginsbab5_at_freenet.de>
Date: 1 Aug 2006 15:03:05 -0700
Message-ID: <1154469785.286755.106290@p79g2000cwp.googlegroups.com>

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 Received on Tue Aug 01 2006 - 17:03:05 CDT