Re: Faster non-existence check (newbie)

No, NOT IN is in general the *slowest* way because it uses a full child table scan for each row in parent table. Best is NOT EXISTS:
select count(*) from parent
where not exists (select null from child where child.parentref = parent.ident);

child.parentref has to be indexed.

--
Have a nice day
Michel

Paavo Helde <helde.zapthis_at_hexagon.fi.tartu.ee> a écrit dans le message : 384CE55C.557EED28_at_hexagon.fi.tartu.ee...
>
> There are two Oracle tables in 1:N relation, both about 50,000 records.
> I have to find the records in the parent table for which there is no
> record
> in the child table. I am told that subqueries are in general the fastest
> way
> in Oracle. However, it seems to me that query
>
> SELECT COUNT(*) FROM parent WHERE ident NOT IN (SELECT parentref FROM
> child);
>
> is unacceptably slow (actually I have not yet succeeded to wait the
> completion
> of it). The result should be about 10,000 records. Other queries not
> involving "NOT IN" seem to work ok. I have done a lot of programming in
> FoxPro
> and there a query like this could take a couple of seconds at most.
>
> Is there any way to speed up the query or to check if there is something
> wrong
> with the server settings?
>
> TIA
> Paavo
Received on Tue Dec 07 1999 - 08:11:46 CST