Re: "NOT IN" predicate hangs result

Thanks Stephan,
  For an operation with 330k entries in the inner select (and 310k entries on the outer table), it would cause a
major problem. As long as it is a know problem, and there is a work around, this is not a problem.

Thanks for the response(s).
Rick

>>> Stephan Szabo <sszabo@megazone23.bigpanda.com> 04/11/02 12:42PM >>>

On Thu, 11 Apr 2002, Rick Szeto wrote:

> Hi all,
>    I recently tried this select statement and it hung my tool:
>
> 1)  select count(*) from user_profile where address_id not in (select
> address_id from address);
>
> Originally, I just thought that it was slow, so I left it over night
> and when I can back the next morning it just hung there(eating up CPU
> cycles). I looked in Celko's book and then tried this and it
> worked(quite fast):
>
> 2)  select count(*) from user_profile up where not exists (select *
> from address addr where up.address_id = addr.address_id);
>
> Is this a known problem?

It probably would have finished eventually, but right now NOT IN has
serious performance problems.  I believe that it's running the inner
select once for every row in user_profile or something to that effect.
And it's a known deficiency, it's even got its own FAQ question.