Delivered-To: sfeuer@enteract.com
From: Jonathan Gennick
Reply-To: jonathan@gennick.com
To: "Feuerstein, Steven"
Subject: Comparing tables or equality
Date: Sun, 13 Aug 2000 16:33:26 -0400
X-Mailer: Forte Agent 1.8/32.548
X-IMSTrailer: __IMail_5__


Steven,


There's a flaw in the table comparision logic that you write
about in the August issue of Oracle Professional. I think
your technique will work in cases where there are no
duplicate rows in either table, but there's no doubt that
the presence of dupes will cause it to generate erroneous
results. Note in the following example that A has 2 rows and
B has only one. Most people would not consider them "equal",
yet they pass the test:


SQL> l
1 (select * from a
2 minus select * from b)
3 union
4 (select * from b
5* minus select * from a)
SQL> /


no rows selected


SQL> select * from a
2 ;


X
-
Z
Z


SQL> select * from b;


X
-
Z


Even using UNION ALL didn't affect the results. There may be
other solutions when it comes to comparing tables, but if
you use this technique, I don't see any other option but to
count the rows in either table to be sure the counts are
identical. Actually, that wouldn't even work. You could get
duplicates that canceled each other out, and that kept the
counts idenitical. Hmm.... Hmm..... Hmm.... Is there a
reliable way to compare two tables when either may have
duplicate records that does not involve writing procedural
code to scan each record? I'll have to sleep on that a bit.


Jonathan


_____________________________________________________
jonathan@gennick.com
http://gennick.com
Brighten the Corner Where You Are


Jonathan


_____________________________________________________
jonathan@gennick.com
http://gennick.com
Brighten the Corner Where You Are