OraFAQ Forum: SQL & PL/SQL » How to compare two huge tables in Oracle?

Home » SQL & PL/SQL » SQL & PL/SQL » How to compare two huge tables in Oracle?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to compare two huge tables in Oracle? [message #181970]

Wed, 12 July 2006 07:02

anil_bogala
Messages: 3
Registered: May 2006
Location: Hyderabad

Junior Member

Dears,

I have two tables with huge data with them. I want to compare both the tables (row by row comparison). So I have fired a query like the following:

SELECT * FROM TAB1
MINUS
SELECT * FROM TAB1@DBLINK;

SELECT * FROM TAB1@DBLINK;
MINUS
SELECT * FROM TAB1;

But the tables are having very huge data (25 Crores). So I am getting the following error after a long time:

ORA-01652: unable to extend temp segment by 512 in tablespace TEMP1

Is there any other way to compare these tables (row by row)?
Please let me know if any alternatives are there??

Regards,
Anil.

Report message to a moderator

Re: How to compare two huge tables in Oracle? [message #181977 is a reply to message #181970]

Wed, 12 July 2006 07:21

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

I ***beleive*** there is no better method ( for row-by-row search).
May be you can add UNION ALL like this

SELECT * FROM TAB1
MINUS
SELECT * FROM TAB1@DBLINK
Union all
SELECT * FROM TAB1@DBLINK
MINUS
SELECT * FROM TAB1

In a certain case i found DBMS_RECTIFIER to be useful ( Because It was REQUIRED to use Pl/sql).
http://download-east.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_recdif.htm#93762
But generally, sql does better job.
So, make your temp space bigger. Look into sort_area_size.

[Updated on: Wed, 12 July 2006 07:21]

Report message to a moderator

Re: How to compare two huge tables in Oracle? [message #181979 is a reply to message #181970]

Wed, 12 July 2006 07:26

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

Firstly, the process will run much quicker of you can get both tables onto the same database. At the moment you're having to drag the entire table acros the database link before you an do anything with it.

Do you need to compare the entire row, or can you do a comparison on a limited subset of columns? If so then using that list of columns instead of 'SELECT *' will speed things up somewhat, and resuce the emount of TEMP storage you need.

I strongly suspect that a row by row process will be substantially slower than your current approach, and that you should make your temp tablespace huge (or set it to auto extend) and let it run.

If there is a unique key for each table, then what you could do is:

Open a cursor on each table, ordered by the unique key.

Get the first row from each cursor.

Loop

If the unique keys are the same, do a column by column comparison of the rows.

If the unique keys are different, then discard the row with the lowest unique key value and fetch the next record from that cursor.

End Loop;

Report message to a moderator

Re: How to compare two huge tables in Oracle? [message #182072 is a reply to message #181977]

Wed, 12 July 2006 19:20

wagnerch
Messages: 58
Registered: July 2006

Member

Mahesh Rajendran wrote on Wed, 12 July 2006 08:21

SELECT * FROM TAB1
MINUS
SELECT * FROM TAB1@DBLINK
Union all
SELECT * FROM TAB1@DBLINK
MINUS
SELECT * FROM TAB1

That should actually be:

(SELECT * FROM TAB1
 MINUS
 SELECT * FROM TAB1@DBLINK)
UNION ALL
(SELECT * FROM TAB1@DBLINK
 MINUS
 SELECT * FROM TAB1)

Oracle processes sets in a top down fashion, as I recall. Another way to do it with two full table scans is http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:2151582681236#15364732017705.

Report message to a moderator

Re: How to compare two huge tables in Oracle? [message #182073 is a reply to message #181970]

Wed, 12 July 2006 19:23

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Do both tables have the same columns as PK?

Report message to a moderator

Re: How to compare two huge tables in Oracle? [message #182075 is a reply to message #182073]

Wed, 12 July 2006 21:50

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

@JRowbottoms advice is solid gold. Use it.

It performs almost exactly the same algorithm as the MINUS SQL (internally), but it will perform the sort of the remote table on the remote database! Not on the local database.

The problem with the MINUS is that the remote table is shipped across and THEN sorted, so you need enough temp space locally to sort both tables.

There is no guarantee that the two-cursor approach will work - you may not even have enough space to sort one table, let alone both. It will get you a darn site closer though.

Ross Leishman

Report message to a moderator

Re: How to compare two huge tables in Oracle? [message #182077 is a reply to message #182075]

Wed, 12 July 2006 22:06

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

@wagnerch
You were Absolutely right.
That was a typo from me.

Report message to a moderator

Re: How to compare two huge tables in Oracle? [message #547103 is a reply to message #181970]

Mon, 12 March 2012 05:25

victoryhendry
Messages: 96
Registered: January 2007
Location: Bangalore

Member

Select ID, COL1, COL2, COL3, ...
FROM TABLE1
WHERE NOT EXISTS
(SELECT 1 FROM TABLE2
WHERE TABLE1.ID = TABL2.ID
AND COL1.TABLE1 = COL2.TABLE2
AND COL2.TABLE1 = COL2.TABLE2
AND COL3.TABLE1 = COL3.TABLE2

)

Thanks & Regards,

VETRIVEL K(SARU).DPI

Report message to a moderator

Re: How to compare two huge tables in Oracle? [message #547104 is a reply to message #547103]

Mon, 12 March 2012 05:33

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

@victoryhendry - Will you please read and follow How to use [code] tags and make your code easier to read? You've been asked several times before.
You do realize this thread is 6 years old?

Report message to a moderator

Previous Topic:	IN clause is not working for stored function (merged 2)
Next Topic:	How to write single WITH for a update and delete?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Aug 08 02:33:12 CDT 2025