Re: Faster non-existence check (newbie)

Hi !
Try it with a sync. subquery :

SELECT COUNT(*) FROM parent WHERE PARENT.ident NOT IN (SELECT parentref FROM child where PARENT.ident = CHILD.ident);

Hth, Reiner

Paavo Helde <helde.zapthis_at_hexagon.fi.tartu.ee> schrieb in im Newsbeitrag: 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 - 04:51:29 CST