How to compare two tables in oracle database?

select key1 from table2
minus
select key1 from table1;

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

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

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.