Re: Faster non-existence check (newbie)

Paavo Helde schrieb:

> 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

Try

select count(*)
from parent p
where not exists (select 'x'

                          from child
                          where parentref = p.ident);

ident is the primary key of <parent>, parentref is the foreign key in <child>.
Be sure that there exists an index on child.parentref....

This should be faster than your statement; let me know...

Regards, Stephan

--

Dipl.-Inf. (FH) Stephan Born   | beusen Consulting GmbH
fon: +49 30 549932-0           | Landsberger Allee 392
fax: +49 30 549932-29          | 12681 Berlin
mailto:stephan.born_at_beusen.de  | Germany
---------------------------------------------------------------
       PGP-Key verfügbar       |      PGP-Key available
---------------------------------------------------------------