Re: Faster non-existence check (newbie)

select * from parent a, child b
where a.a_key = b.b_key(+) and b.key is null

Assuming that you have keys/indexes on both tables, this hauls

Paavo Helde <helde.zapthis_at_hexagon.fi.tartu.ee> wrote in message news: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 Thu Dec 09 1999 - 16:38:53 CST