Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
Comparing Tables
59 views
Skip to first unread message
The Magnet
Sep 21, 2012, 7:29:48 PM9/21/12
to
Hi,
I am using this SQL to perform some simple table compares:
data_rec=`sqlplus -s $user/${password} << "EOF"
set heading off feedback off verify off timing off
SELECT COUNT(*) FROM
(SELECT * FROM master_table MINUS SELECT * FROM
master_table@dblink);
exit
EOF`
However, one of the table a complex data type, with nested tables. I
cannot use the above method on nested tables. Does anyone know a good
set of code to compare 2 identical tables, that contain nested tables?
Thanks!
The Magnet
Sep 21, 2012, 7:36:30 PM9/21/12
to
databases, as you can see by the link. There are several regular
columns in the table, then a few nested tables. I want to compare the
two tables to make sure they contain the exact same data.
Help!
John Hurley
Sep 21, 2012, 7:43:33 PM9/21/12
to
The Magnet
Sep 21, 2012, 7:48:05 PM9/21/12
to
Mladen Gogala
Sep 22, 2012, 1:41:42 PM9/22/12
to
On Fri, 21 Sep 2012 16:29:48 -0700, The Magnet wrote:
>
> However, one of the table a complex data type, with nested tables. I
> cannot use the above method on nested tables. Does anyone know a good
> set of code to compare 2 identical tables, that contain nested tables?
>
> Thanks!
Dump both tables into flat files and compute MD5 sum or sha1sum. The
>
> However, one of the table a complex data type, with nested tables. I
> cannot use the above method on nested tables. Does anyone know a good
> set of code to compare 2 identical tables, that contain nested tables?
>
> Thanks!
latter is 160 bit, but there is also a 512 bit version, in sha512sum.
--
Mladen Gogala
The Oracle Whisperer
http://mgogala.byethost5.com
Jonathan Lewis
Sep 22, 2012, 2:15:26 PM9/22/12
to
"The Magnet" <a...@unsu.com> wrote in message
news:cb932f27-ed7f-4e5c...@n9g2000yqn.googlegroups.com...
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.
--
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com/all_postings
Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543
news:cb932f27-ed7f-4e5c...@n9g2000yqn.googlegroups.com...
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.
--
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com/all_postings
Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543
The Magnet
Sep 22, 2012, 3:26:23 PM9/22/12
to
On Sep 22, 1:15 pm, "Jonathan Lewis" <jonat...@jlcomp.demon.co.uk>
wrote:
> Jonathan Lewishttp://jonathanlewis.wordpress.com/all_postings
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.
wrote:
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.
Mladen Gogala
Sep 22, 2012, 8:14:51 PM9/22/12
to
On Sat, 22 Sep 2012 12:26:23 -0700, The Magnet wrote:
> Oracle really needs to come up with some decent utilities. Even to this
> day, every other language has a SPLIT function, except PL/SQL.
PL/SQL is not Perl. Their purpose is not the same. Split and join are
> Oracle really needs to come up with some decent utilities. Even to this
> day, every other language has a SPLIT function, except PL/SQL.
formatting functions which are unnecessary in procedural DB extensions
like Oracle's PL/SQL. Almost all languages also have sprintf and
equivalent, but not PL/SQL. That is because PL/SQL has no input/output
functions and is not a general purpose programming language. Language
developed by Oracle which does have all of the above is called "Java" and
can be used from within the database. As a matter of fact, Perl can be
used from within the database, too:
http://www.smashing.org/extproc_perl/
Nobody is using neither Java nor Perl from within the database, despite
Oracle's intense push for Java. There is a reason for that: procedural DB
extensions like PL/SQL adopted by both DB2 and Oracle or Transact-SQL or
PL/PgSQL (PostgreSQL PL/SQL clone) are not meant to be general purpose
programming languages. They do what they were intended to do: provide
procedural logic to business transactions. If you need Perl or PHP, use
them. If you think you need Python, use Perl or PHP.
Also, my solution to the problem would be much faster than Jonathan's
because it wouldn't involve row by row comparison and cross-instance
chatter, but the checksum comparison. The only thing that you need to be
careful about is ordering. The files need to be identical in order for
the comparison to produce equality. That means that the rows have to have
the same order.
This solution is also very reliable, it is used by backup manufacturers
for de-duplication.
Jonathan Lewis
Sep 23, 2012, 10:38:04 AM9/23/12
to
news:df8aa75d-1230-46d2...@e18g2000yqo.googlegroups.com...
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
On Sep 22, 1:15 pm, "Jonathan Lewis" <jonat...@jlcomp.demon.co.uk>
wrote:
> "The Magnet" <a...@unsu.com> wrote in message
>
> news:cb932f27-ed7f-4e5c...@n9g2000yqn.googlegroups.com...
> |
> | Hi,
> |
> | I am using this SQL to perform some simple table compares:
>
wrote:
> "The Magnet" <a...@unsu.com> wrote in message
>
> news:cb932f27-ed7f-4e5c...@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.
>
> 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
|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.
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
Mark D Powell
Sep 26, 2012, 11:02:32 AM9/26/12
to
On Saturday, September 22, 2012 2:15:24 PM UTC-4, Jonathan Lewis wrote:
> "The Magnet" <a...@unsu.com> wrote in message news:cb932f27-ed7f-4e5c...@n9g2000yqn.googlegroups.com... | | Hi, | | I am using this SQL to perform some simple table compares: | | data_rec=`sqlplus -s $user/${password} << "EOF" | set heading off feedback off verify off timing off | SELECT COUNT(*) FROM | (SELECT * FROM master_table MINUS SELECT * FROM | master_table@dblink); | exit | EOF` | | | However, one of the table a complex data type, with nested tables. I | cannot use the above method on nested tables. Does anyone know a good | set of code to compare 2 identical tables, that contain nested tables? | | Thanks! 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. -- Regards Jonathan Lewis http://jonathanlewis.wordpress.com/all_postings Author: Oracle Core (Apress 2011) http://www.apress.com/9781430239543
Skipping non-supported data type coliumns is exactly what Oracle recommends in the PL/SQL Packages and Types manual for the new with 11g dbms_comparison package which is designed to compare objects between databases.
> "The Magnet" <a...@unsu.com> wrote in message news:cb932f27-ed7f-4e5c...@n9g2000yqn.googlegroups.com... | | Hi, | | I am using this SQL to perform some simple table compares: | | data_rec=`sqlplus -s $user/${password} << "EOF" | set heading off feedback off verify off timing off | SELECT COUNT(*) FROM | (SELECT * FROM master_table MINUS SELECT * FROM | master_table@dblink); | exit | EOF` | | | However, one of the table a complex data type, with nested tables. I | cannot use the above method on nested tables. Does anyone know a good | set of code to compare 2 identical tables, that contain nested tables? | | Thanks! 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. -- Regards Jonathan Lewis http://jonathanlewis.wordpress.com/all_postings Author: Oracle Core (Apress 2011) http://www.apress.com/9781430239543
If there are several tables to be compared then perhaps the package can be used form some of them and customer code for the tables with unsupported data types if the column cannot be left out of the comparison.
HTH -- Mark D Powell --
0 new messages