[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to compare two tables in oracle database?

Posted on 2004-11-16
10
Medium Priority
?
10,716 Views
Last Modified: 2011-10-03
How to get the difference in records from two table.Tables are being formed from the queries.Tables are very big about  2 billion records each of them.We need the to find where the tables are same or different.
0
Comment
Question by:pooja74
7 Comments
 
LVL 4

Expert Comment

by:boriskalavsky
ID: 12601728
select key1 from table1
minus
select key1 from table2;

will show you if there any recodes exist in table1 but not in table2


0
 
LVL 4

Accepted Solution

by:
boriskalavsky earned 172 total points
ID: 12601734
select key1 from table2
minus
select key1 from table1;

will show you if there any recodes exist in table1 but not in table2

0
 
LVL 6

Expert Comment

by:morphman
ID: 12602610
First, I would perform a count on both tables. Assuming they are the same, you can

select * from table1
intersect
select * from table2

will give you all records that match between table 1 and 2. (on all columns). This should also be the same as the count from tables 1 and 2. If it is, everything is identical.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 5

Assisted Solution

by:alexfrl
alexfrl earned 164 total points
ID: 12602801
We should consider the fact of billions of records in each table. There are no rollback segment which would be enogh to make any of the above transactions.

I suppose the following can work:

1st) These tables should be PARTITIONed into as many as better parts. Use the same partitions.


2nd) Create in both tables an INDEX on an entire series of columns which you are going to compary.
       The index columns should START with the PARTITIONing columns placed in the order of the partition


3rd) Add new indicating column (COMPATIBLE_ROWS_NO number) in both tables.


4th) Build a procedure which uses cursor for tableA with a hint of using the above index.

       For each record:
          FOR next_one IN <YOUR CURSOR> LOOP

    a)  UPDATE the indicating column of the 2nd table rows which "EQUAL" to the current row of the 1st table.

         update tableB
              set COMPATIBLE_ROWS_NO := nvl(COMPATIBLE_ROWS_NO,1)+1
          where <your comparision>;

         !!! Assure using the above index in the update statement.

    b)  UPDATE the 1st table's indicating column using its ROWID -- do not deal with "WHERE CURRENT OF <your cursor>" !

         update tableA
              set COMPATIBLE_ROWS_NO = SQL%ROWCOUNT -- The number of compatible rows in the 2nd table
          where rowid = next_one.ROW_ID;


5th) Do not forget commiting (COMMIT) each 10K records.
         l_counter := nvl(l_counter,0)+1; if mod(l_counter,10000)=0 then commit; end if;

6th) As the result you should get your 1st table's indicating column
Hope it helps
0
 
LVL 7

Expert Comment

by:yoren
ID: 12605892
Rather than using "minus," the following should work on any size data set:

Records missing from table1:
select * from table1 where not exists (select * from table2 where key = table1.key)


Records missing from table2:
select * from table2 where not exists (select * from table1 where key = table2.key)

..assuming you don't have too many differences. If you do, then you'll want to make the above statements cursors in a procedure and write each record to a table, committing every 10k records or so as alexfrl suggests. No need to do all the other complicated stuff he suggests, though.


0
 
LVL 6

Assisted Solution

by:izblank
izblank earned 164 total points
ID: 12606436
Do your tables have  primary keys?  If not, none of the above solutions are guaranteed to work, because they will not account for duplicates.  You'll have to do something like this:

    SELECT Col1,Col2,...Coln,count(*)
    FROM A
    GROUP BY Col1,Col2,...Coln
  MINUS
    SELECT Col1,Col2,...Coln,count(*)
    FROM B
    GROUP BY Col1,Col2,...Coln
UNION ALL
    SELECT Col1,Col2,...Coln,count(*)
    FROM B
    GROUP BY Col1,Col2,...Coln
  MINUS
    SELECT Col1,Col2,...Coln,count(*)
    FROM A
    GROUP BY Col1,Col2,...Coln


If size counts (like in your case), then you run this query several times with a WHERE clause added to restrict the number of rows in each comparison session.
0
 
LVL 4

Expert Comment

by:boriskalavsky
ID: 12608839
select key1 from table1
minus
select key1 from table2;

will show you if there any recodes exist in table1 but not in table2

select key1 from table2
minus
select key1 from table1;

will show you if there any recodes exist in table1 but not in table2


select key1 from table1
intersect
select key1 from table2;

will get too much data.

You can also try:
select count(1) form
(select key1 from table1
intersect
select key1 from table2);

result should be the same as a count from table1 or table2.

There are going to be a lot of tunning issues involved like parallel execution, partitioning, using indexes (or not), using FFS of the index, etc.
 


 
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question