Re: Comparing Tables

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Sun, 23 Sep 2012 00:14:51 +0000 (UTC)
Message-ID: <pan.2012.09.23.00.14.06_at_gmail.com>



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 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.

-- 
Mladen Gogala
The Oracle Whisperer
http://mgogala.byethost5.com
Received on Sat Sep 22 2012 - 19:14:51 CDT

Original text of this message