Re: Finding Orphans

From: Heinz Huber <Heinz.Huber_at_elbanet.co.at>
Date: Wed, 10 Jan 2001 07:50:40 +0100
Message-ID: <3A5C0640.5CF6C537_at_elbanet.co.at>

"Lucky Leavell [RIS]" wrote:
>
> I have a situation whereby I am trying to determine the number of orphans
> in an addresses table by checking for those address keys NOT IN the five
> tables which use it, to wit:
>
> SELECT count(*)
> FROM address
> WHERE addr_key NOT IN (SELECT addr_key
> FROM client)
> AND addr_key NOT IN (SELECT addr_key
> FROM employee)
> AND addr_key NOT IN (SELECT addr_key
> FROM groups)
> AND addr_key NOT IN (SELECT addr_key
> FROM patient)
> AND addr_key NOT IN (SELECT addr_key
> FROM provider)
>
> These tables contain anywhere from a few hundred to a few million rows and
> only the address and patient tables have an index on the addr_key column.
> After running three hours the query returns a zero count which I know is
> NOT correct.

My first suggestion would be to add indexes to the other three tables, too. If those tables are large, I think you might run out of temp space or the db server might get confused.

Another way would be to split up the query: SELECT COUNT(*)
 FROM address
 WHERE addr_key NOT IN

    (SELECT addr_key

     FROM client) AND 
       addr_key IN 
    (SELECT addr_key
     FROM address
     WHERE addr_key NOT IN 
        (SELECT addr_key
         FROM employee)) ...

Get the idea? I'm not sure whether this would make a difference, but since you're getting very strange results it might also be worth a try.

hth,
Heinz Received on Wed Jan 10 2001 - 07:50:40 CET