Re: Missing table entries
Walt Hultgren {rmy} (walt@mathcs.emory.edu) wrote:
: 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.
We had a similar problem and avoided ALL sub-queries (even the one in
the "update tflag" statement above).
Using Walt's table and column names from above, our SQL would then
look like:
select code, { Assuming that code is a UNIQUE index on table1 }
0 flag
from table1
into temp tflag;
insert into tflag
select distinct code, { Only want UNIQUE codes from table2 }
1 {flag}
from table2;
select code, count(*) cnt
from tflag
group by 1
having count(*) = 1 { Only in one table or the other, not both. }
order by 1
into temp temp_problem_codes;
Then, join the list of problem codes with tflag to find out whether
the problem code is a childless header (where flag = 0) or an orphaned
detail (where flag = 1).
select tflag.code
from temp_problem_codes, tflag
where flag = 0
and temp_problem_codes.code = tflag.code
If you want reports including other fields (as we did) then it is
probably quicker to just join temp_problem_codes directly with table1
or table2 instead of tflag.
This may speed up even further if you create indexes on the temporary
tables. We weren't joining temp_problem_codes with tflag, so both
temp tables were only selected from once; we didn't bother trying
indexes.
Background to the aside to the Aside: ( Is that a B-side or what? :-) )
In our case it was a foreign key relationship rather than a typical
header-detail (where the primary key of the header usually forms part
of the primary key of the detail). The foreign key in "table1" was
NOT indexed so the sub-query approach was REALLY bad. It ran in the
background for 4 hours before I interrupted it and started on a better
approach. Someone here (Anthony Randell, anthonyr@peace.co.nz) made
the "HAVING COUNT(*) = 1" suggestion. After coding that, it only took
a few seconds to find both the unreferenced rows and undefined
references.
I guess this approach would also work reasonably well even if neither
table had indexes on the code field(s), because it only requires a
single sort per table to find a list of distinct codes, then a sort on
the temporary table to count each code. Like Walt, we would be
interested to hear tips from anyone else who has faster ways of
accomplishing this kind of referential integrity check on un-trusted
data, because it comes up quite often when doing data conversion.
We didn't try all the combinations of indexes etc before we moved on.
Thanks in advance for any feedback,
Jim
+---------------------------------------------------------+
| Jim Mooney <jim@peace.co.nz> ph +64-4-5705739 |
| Peace Computers New Zealand Limited fax +64-4-5705760 |
+---------------------------------------------------------+