How to compare two tables in oracle database?

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.
pooja74Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

boriskalavskyCommented:
select key1 from table1
minus
select key1 from table2;

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


0
boriskalavskyCommented:
select key1 from table2
minus
select key1 from table1;

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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
morphmanCommented:
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

alexfrlCommented:
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
yorenCommented:
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
izblankCommented:
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
boriskalavskyCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.