Re: Comparing Tables

"The Magnet" <art_at_unsu.com> wrote in message news:df8aa75d-1230-46d2-b61b-6c68831f13e8_at_e18g2000yqo.googlegroups.com... On Sep 22, 1:15 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> "The Magnet" <a..._at_unsu.com> wrote in message
>
> news:cb932f27-ed7f-4e5c-97fa-afd9327e9a15_at_n9g2000yqn.googlegroups.com...
> |
> | Hi,
> |
> | I am using this SQL to perform some simple table compares:
>
> Your SQL is probably deficient. If you want to find differences between
> the
> tables you need to do something symmetrical like:
>
> select
> from
> (select * from table1 minus select * from table2)
> union all
> (select * from table2 minus select * from table1)
>
> You could write your code to take this approach for tables where
> user_tab_columns shows no "illegal" columns such as LOBs, longs, nested
> tables etc.
> For the remaining tables you could look at the package
> dbms_rectifier_diff
> (which exists to sort out replication errors) from dbmsrctf.sql. This
> MIGHT
> cover all the options you want but, as you might guess, it's likely to be
> slow because it has to do row by row comparison.
>

|Oracle really needs to come up with some decent utilities.  Even to
|this day, every other language has a SPLIT function, except PL/SQL.
|If 2 tables are identical in structure, then I should be able to
|compare them.  The fact these these tables also have nested tables,
|Oracle should be able to deal with that easily.
|
|The MD5 idea is the only one I can think of too.   Although, being
|able to compare each nested table with the same nested table in the
|other table would help identify where the differences are.

I don't see how a pl/sql built-in SPLIT function would help you find the differences between two tables. However, since you said every other language has got it I did check SS Server 2008 to see how its procedural language implemented it - and discovered that it doesn't and that there are several places on the web with "the definitive" implementation of split() for SQL Server, using code that looks remarkably like the type of code people produce in PL/SQL. (This isn't intended as a "compare SS with Oracle" argument, it's simply supposed to make you think that perhaps the designers of database support languages don't really expect to have much truck with string-manipulation functions - as Mladen pointed out in his post.)

The main problem with the nested table compare is that Oracle implemented such a bad idea in a relation database. What you've got are two tables with a parent/child relationship and an invisble, generated, unique key on the parent table used by Oracle to link parent and child.

If you've minimised the damage due to the design, you will have created an identifier that uniquely identifies child rows within parent (and will have created a unique index on (meaningless_id, your_id) on the child. If this is the case then you can use the minus with union all on the join between the parent and child tables - repeating once for each nested table that a parent has.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com/all_postings

Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543