Faster non-existence check (newbie)

From: Paavo Helde <helde.zapthis_at_hexagon.fi.tartu.ee>
Date: Tue, 07 Dec 1999 12:45:48 +0200
Message-ID: <384CE55C.557EED28@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:45:48 CST