Re: Faster non-existence check (newbie)

select {parent key columns} from parent_table MINUS
select {matching child column} from child_table ;

The correlated existence test already
mentioned by others is usually the
fastest, but the above set-wise operation can sometimes be faster.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Paavo Helde wrote in 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 Fri Dec 10 1999 - 03:06:56 CST