RE: Checking for Orphans
You could always use NOT EXISTS instead of NOT IN which is a dam site
faster.
Peter
__________________________________
Peter Marley
Data Warehouse Developer, Acco Europe
Tel: +44 (0)1296 732184
Fax: +44 (0)1296 732185
email: peter.marley@acco-uk.co.uk <mailto:peter.marley@acco-uk.co.uk>
-----Original Message-----
From: Carlos Benjamin [SMTP:benj@firstlinux.net]
Sent: Tuesday, January 09, 2001 1:37 PM
To: Lucky Leavell [RIS]; informix-list@iiug.org
Subject: Re: Checking for Orphans
--- "Lucky Leavell [RIS]" <ris@iglou.com>
> wrote:
>Version: 7.31
>OS: AIX 4.3.3
>
>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.
>
>After pulling hair (and I don't have that much left to pull!), I
still
>cannot see what I am missing. Any ideas? Is there a better way to
do
>this?
Pull someone else's hair?
You could use a series of SQL queries where the first one selects
keys into a temp table where that key doesn't appear in one of the other
tables. Then go through the other tables and delete anything from temp that
appears in each one of those in turn. If you're Lucky (and you seem to be)
that should work.
>
>Thank you,
>Lucky
>
>Lucky Leavell Phone: (800) 481-2393 or (812)
366-4066
>UniXpress - Your Source for SCO FAX: (888) 231-9640 or (812)
366-3618
>1560 Zoar Church Road NE Email: lucky@UniXpress.com
>Corydon, IN 47112-7374 WWW Home Page: http://www.UniXpress.com
==
"Outlook not so good."
That magic 8-ball knows everything!
I'll ask about Exchange Server next.
_____________________________________________________________
Want a new web-based email account ? ---> http://www.firstlinux.net