Re: "NOT IN" predicate hangs result

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.