Re: Finding Orphans
Date: Tue, 9 Jan 2001 20:15:23 -0800
Message-ID: <93gnkp$8qr$1_at_bob.news.rcn.net>
"Heinz Huber" <Heinz.Huber_at_elbanet.co.at> wrote in message
news:3A5B2907.F2B60833_at_elbanet.co.at...
> Paul Keister wrote:
> >
> > One thing you could try is to group the child tables before you do the
> > nested select, i.e.
> >
> > SELECT count(*)
> > FROM address
> > WHERE addr_key NOT IN
> > (
> > SELECT addr_key FROM client
> > UNION
> > SELECT addr_key FROM employee
> > UNION
> > SELECT addr_key FROM groups
> > UNION
> > SELECT addr_key FROM patient
> > UNION
> > SELECT addr_key FROM provider
> > )
> >
> > I'm not sure this will help because I'm not sure why your original
version
> > isn't working right. This query should be more efficient, unless the
query
> > optimizer has already figured things out: you only want to scan all the
> > child tables once, then form a single unique set of IDs to compare
against.
> > That's what this should do.
> >
> > The first question I always ask myself when I start bloodying my
forehead
> > is: is this really wrong? Perhaps you don't actually have any Orphans.
> >
> > "Lucky Leavell [RIS]" <ris_at_iglou.com> wrote in message
> > news:Pine.GSO.4.31.0101081607350.21269-100000_at_shell1...
> > > 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)
>
> Shouldn't the original version provide better performance as long as
> there are foreign keys defined?
You're probably right, especially since there is not going to be much, if any overlap, i.e. very few records will share and address. Received on Wed Jan 10 2001 - 05:15:23 CET