Find differences between two large tables in oracle

Question

I have two different tables, say table A and B in oracle with around 15 million records in each. Table A has columns (a,b,c,d) and Table B has columns (e,f,g,h).

The objective is to write a stored procedure to check if every record present in table A is also present in table B and vice versa. Differences between these two should be inserted into a third table.

My problem is that column a in Table A should be compared with concatenate of column e and f in table B if column e contains a certain string (0311), if not I have to compare it with just column f.

Column b should be compared with column g in table B and I also have to compare column c in the table A with column g in table B, if the two aren't a match column d should be compared with column g.

What's the fastest way to do so?

for example these two are a match:

Table A: 9353456789,03117884657,12082200003035,12082123595535
Table B: 9353456789,0311,7884657,12082200003035

or:

Table A: 9353456789,03117884657,12082200003035,12082123595535
Table B: 9353456789,0311,7884657,12082123595535

example of records that do not need concatenation and are a match:

Table A: 9353456789,03617884657,12082200003035,12082123595535
Table B: 9353456789,0361,03617884657,12082200003035

Answer 1

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE TableA ( a VARCHAR2(20), b VARCHAR2(20), c VARCHAR2(20), d VARCHAR2(20) );
CREATE TABLE TableB ( e VARCHAR2(20), f VARCHAR2(20), g VARCHAR2(20), h VARCHAR2(20) );
CREATE TABLE TableC ( i VARCHAR2(20), j VARCHAR2(20), k VARCHAR2(20), l VARCHAR2(20) );

INSERT INTO TableA
          SELECT '9353456789','03117884657','12082200003035','12082123595535' FROM DUAL
UNION ALL SELECT '9353456789','03617884657','12082200003035','12082123595535' FROM DUAL
UNION ALL SELECT '9353456789','03617884657','12082200003034','12082123595534' FROM DUAL;

INSERT INTO TableB
          SELECT '9353456789','0311','7884657','12082200003035' FROM DUAL
UNION ALL SELECT '9353456789','0311','7884657','12082123595535' FROM DUAL
UNION ALL SELECT '9353456789','0361','03617884657','12082200003035' FROM DUAL
UNION ALL SELECT '9353456789','0361','03617884657','12082200003036' FROM DUAL;

Query 1:

To insert the rows - perform an INSERT INTO... SELECT using a FULL OUTER JOIN between both tables using your requirements as the join condition; then for the rows which do not match either TableA(a, b, c, d) will all be NULL or TableB(e, f, g, h) will all be NULL and this can be used in the WHERE condition to only get the non-matched rows. Finally, so as not to return NULL values, COALESCE() is used for the returned values.

INSERT INTO TableC
    SELECT COALESCE( ta.a, tb.e ) AS i,
           COALESCE( ta.b, tb.f ) AS j,
           COALESCE( ta.c, tb.g ) AS k,
           COALESCE( ta.d, tb.h ) AS l
    FROM TableA ta
         FULL OUTER JOIN
         TableB tb
         ON (    ta.a = tb.e
             AND ta.b = CASE tb.f WHEN '0311' THEN tb.f || tb.g ELSE tb.g END
             AND ( ta.c = tb.h OR ta.d = tb.h )
            )
    WHERE ta.a IS NULL
    OR    tb.e IS NULL;

Query 2:

SELECT * FROM TableC

Results:

|          I |           J |              K |              L |
|------------|-------------|----------------|----------------|
| 9353456789 | 03617884657 | 12082200003034 | 12082123595534 |
| 9353456789 |        0361 |    03617884657 | 12082200003036 |