Re: Missing table entries
In article <366u06$6c7@emory.mathcs.emory.edu> rob@dssmktg.com (Robert Minter)
writes:
>From irl3b2c.att.com!bt@uunet.uu.net:
>*
>* Does anyone know of a way WITHIN sql for identifying missing entries in a
>* table?
>* I can see the missing rows if I do something like:
>*
>* select table1.code,
>* table2.code
>* from table1, outer table2
>* where table1.code=table2.code
>*
>Try just like you stated:
>
> SELECT code FROM table1
> WHERE code NOT IN ( SELECT code FROM table2 )
As an interesting aside, we have found here that using an approach that
avoids NOT IN or NOT EXISTS results in much faster execution times.
In essence, you create a temp table of codes from the master table. The
temp table also contains a flag column to indicate which codes are found
in the detail table:
select code,
0 flag
from table1
into temp tflag;
Then, mark all of the codes found in the detail table:
update tflag
set flag = 1
where code in ( select code
from table2
where tflag.code = table2.code );
Then, report all of the codes from the temp table that weren't marked:
select *
from tflag
where flag = 0;
You'd think that all of this processing would take longer, but we have
found that not to be the case.
The fact that this method is much faster may just be a quirk of our
database design or tuning. However, I've had good results with this in
several similar but unrelated situations. Maybe the first approach would
work as well if we indexed *everything*, but that's a bad idea obviously.
As an aside to the aside, we came across this scheme while trying to
improve the execution time on a script that looked for missing rows. I had
originally started trying to improve things by investigating the execution
times of a script using NOT IN versus one that used NOT EXISTS. The
method I described above worked so much better, I stopped tinkering with
the two NOT's before I found any clear indication that one might generically
better than the other. If anyone can give me any guidelines in that area,
I'd appreciate hearing from you.
Thanks,
Walt.
--
Walt Hultgren Internet: walt@rmy.emory.edu (IP 128.140.8.1)
Emory University UUCP: {...,gatech,rutgers,uunet}!emory!rmy!walt
954 Gatewood Road, NE BITNET: walt@EMORY
Atlanta, GA 30329 USA Voice: +1 404 727 0648