RE: Find Difference between 2 Tables, 22 GB each, on unique key

I don't know if it's the fastest way but check http://www.oracle.com/technology/oramag/oracle/05-jan/o15asktom.html And search for "Comparing the Contents of Two Tables"

Vlado Barun, M.Sc.
Senior Data Architect, Cadre5
www.cadre5.com

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of VIVEK_SHARMA
Sent: Tuesday, April 12, 2005 4:29 AM
To: oracle-l_at_freelists.org
Cc: oracledba_at_lazydba.com
Subject: RE: Find Difference between 2 Tables, 22 GB each, on unique key

Folks

Need to get=A0the difference records between 2 tables each of size 22GB =
(each containing 32 Million rows). The temp tablespace is 30GB.

Unique index exists on the respective fieldname on which to check for = difference.

Which of the following approaches is faster/most efficient?

1. Select <fieldname> from Table 1 MINUS Select <fieldname> from Table = 2? - If so what would be the consumption of TEMP Tablespace?=20 Additionally how to concurrently monitor consumption of Temp tablespace =
   (used/free) while the job is running?
   
2. using some Other SQL script
3. using a PL/SQL script

Qs Will doing a FULL index ONLY Scan be faster than a FULL Table scan?

Machine configuration - 4 cpu's and 1GB DIMM RAM.=20 OS version - Unix Sun Solaris machine Version not known. Oracle version -=A0 8.1
Nature of application -=A0Joins and inserts production issue -=A0 Yes
Tables being accessed by other applications - YES, during certain time = slot. We have abt 12-14hrs when they are not touched by any application. =A0
Thanks

--
http://www.freelists.org/webpage/oracle-l



--
http://www.freelists.org/webpage/oracle-l