Fastest way to identify differences between two tables?

Question

Hey all,

I have a need to check a live table against a transactional archive table and I'm unsure of the fastest way to do this...

For instance, let's say my live table is made up of these columns:

• Term
• CRN
• Fee
• Level Code

My archive table would have the same columns, but also have an archive date so I can see what values the live table had at a given date.

Now... How would I write a query to ensure that the values for the live table are the same as the most recent entries in the archive table?

PS I'd prefer to handle this in SQL, but PL/SQL is also an option if it's faster.

Thanks, Sonny

Answer 1

unload to table.unl select * from table1 order by 1,2,3,4

unload to table2.unl select * from table2 order by 1,2,3,4

diff table1.unl table2.unl > diff.unl

Answer 2

Could you use a query of the form:

SELECT your columns FROM your live table
EXCEPT
SELECT your columns FROM your archive table WHERE archive date is most recent;

Any results will be rows in your live table that are not in your most recent archive.

If you also need rows in your most recent archive that are not in your live table, simply reverse the order of the selects, and repeat, or get them all in the same query by performing a (live UNION archive) EXCEPT (live INTERSECTION archive)

Answer 3

You didn't mention how rows are uniquely identified, so I've assumed you also have an "id" column:

SELECT *
FROM livetable
WHERE (term, crn, fee, levelcode) NOT IN (
   SELECT FIRST_VALUE(term) OVER (ORDER BY archivedate DESC)
         ,FIRST_VALUE(crn) OVER (ORDER BY archivedate DESC)
         ,FIRST_VALUE(fee) OVER (ORDER BY archivedate DESC)
         ,FIRST_VALUE(levelcode) OVER (ORDER BY archivedate DESC)
   FROM   archivetable
   WHERE  livetable.id = archivetable.id
);

Note: This query doesn't take NULLS into account - if any of the columns are nullable you can add suitable logic (e.g. NVL each column to some "impossible" value).

Answer 4

SELECT term, crn, fee, level_code
FROM live_data
MINUS
SELECT term, crn, fee, level_code
FROM historical_data

Whats on live but not in historical. Can then union to a reverse of this to get whats in historical but not live.

Answer 5

Simply:

SELECT collist
  FROM TABLE A
minus 
SELECT collist
  FROM TABLE B
UNION ALL
SELECT collist
  FROM TABLE B
minus 
SELECT collist
  FROM TABLE A;