Re: Faster non-existence check (newbie)

Try

select count(ref) from (select ident ref from parent minus

                                         select parenref ref from child);

This may be faster if ident and parentref are indexed...

Paavo Helde <helde.zapthis_at_hexagon.fi.tartu.ee> wrote:

>
>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

  -----------== Posted via Newsfeeds.Com, Uncensored Usenet News ==----------    http://www.newsfeeds.com The Largest Usenet Servers in the World! ------== Over 73,000 Newsgroups - Including Dedicated Binaries Servers ==----- Received on Wed Dec 15 1999 - 11:42:16 CST