Re: High volume table comparison in Oracle

From: Joel Garry <joel-garry_at_home.com>
Date: 24 Feb 2003 12:07:10 -0800
Message-ID: <91884734.0302241207.484d7d74_at_posting.google.com>

h.manjunath_at_iflexsolutions.com (Manjunath) wrote in message news:<68c7ea85.0302232234.1286eb88_at_posting.google.com>...
> Hi,
> I have to compare two tables( Each column in row) in Oracle table.
> These table contains around 10 to 15 millions of record with each row
> contains around 60 column. I tried to compare this using PL/SQL But
> this is very slow. Is there any other ways to do this. Could you
> please send me the code for this ? One of my friend suggested to use
> Unix script ? But how to use Unix in this Scenario ?

The other posts using built-in SQL are probably the way to go.

But since you ask about how to use unix, basically you have a sql statement that spools each table to a separate file (remembering that the order of fields is not guaranteed, so you either have to specify them or write a script to specify them in the same order for each), run sort on each and then do a diff.

Off the top of my head, something like this: #!/bin/ksh
if [ $# -ne 2 ]
then echo "tester.ksh - wrong argument count, 2 required."

     echo "usage: tester.ksh schema password"
     exit 1

fi
schema=$1
password=$2
nohup echo "$schema/$password
set colsep |
set verify off
set echo off
set feedback off
set pages 0
set heading off
set termout off
set linesize 1000
-- important to get the linesize right!
-- the select statement goes here
/" | sqlplus|sort > $$tester1.out &

# same thing with the other table to $$tester2.out not shown wait
diff $$tester1.out $$tester2.out > tester.out

rm $$tester*.out

# Then you can load tester.out using sqlload or do whatever you want.

See orafaq.com or Unix Power Tools for some other syntax. Depending on your configuration, it may be faster to run the extracts serially (without the nohup and &).

jg

--
_at_home.com is bogus.
But very useful to stay killfiled.

Received on Mon Feb 24 2003 - 21:07:10 CET