Oracle Expert

Let’s say that we need to reconcile data between 2 tables, where we literally need to compare values of each columns and highlight differences between two.

I found the use of MINUS to be very beneficial in letting us do a very quick, yet meaningful reconciliation.

Say you need to reconcile content of TAB1 against content of TAB2.

The outline of the query will look like this
- find items that are in TAB1, but not in TAB2
combine that with
- find items that arein TAB2, but no in TAB1

So your SQL might look like this

(select ‘missing from TAB2′ as status, col1,col2,col3,col4,col5,col6 from TAB1
minus
select ‘missing from TAB2′ as status col1,col2,col3,col4,col5,col6 from TAB2
)
union all
(
select ‘missing from TAB1′ as status, col1,col2,col3,col4,col5,col6 from TAB2
minus
select ‘missing from TAB1′ as status, col1,col2,col3,col4,col5,col6 from TAB2
from TAB1)

Easy. I have recently tested to reconcile a 500k row table against another, where each table has 15 columns (even spread between number and varchar2 fields), the reconciliation took just around 10 seconds.