Re: Why does NOT IT take so long

>
> Re: Why does NOT IT take so long
>
> aa651_at_ccn.cs.dal.ca (Karl Penney)
> Tue, 25 Jul 1995 01:35:27 GMT
> Chebucto Community Net
>
> Newsgroups:
> comp.databases.oracle
> References:
> <3uu80q$2nj_at_explorer.csc.com>
>
> Tom Conder (tconder_at_csc.com) wrote:
> : When writing Oracle SQL, why does the NOT IN statement take so long?
> I don't think that any NOT expressions in the where clause allow the
> use of an index. This may be why is takes longer than you think it
> should.

Actually the problem is that IN and NOT IN do a full table scan for the subquery for each row in the main query. Converting NOT/IN queries to use NOT/EXISTS, or even joins, will make them run much faster.

I tried to establish a general rule of thumb for when to use NOT/IN with a sub-query a few months ago. A did numerous queries and timed them all. The rule I came up with was NEVER user NOT/IN. It's always much slower than any other way of forming the query. Received on Tue Jul 25 1995 - 00:00:00 CEST