Just from a logical standpoint you're going to have to have 2 copies of the data, or a log of each change (including the deletes/inserts). You can use the tables/triggers like you said, use flashback query (and that will work on 11g by the way, assuming that all the archive logs for the day are still online-- and, in my experience, it's not that expensive of a task depending on the query)
You can also approach it a little differently, although this method is more complex, it might be faster to some extent, especially if there's some limit to which rows are "in scope"-- meaning, that if you can put some kind of code to ignore the vast majority of the rows in the table (like a rule that they cannot update hours that are more than a month old or something similar).
You create a second table that has all the rows that are "in scope" for the update-- it's just a plain old copy of the other table as it sits right now.
When your job runs, it first deletes records from the table that are "out of scope" and then runs a query that gets the edited/deleted rows..
select t1.a as a1, t2.a as a2, t1.b as b1, t2.b as b2
from t1
left join t2 on t2.id=t1.id
...
Start building your file with this info
Be sure to UPDATE the t2 with the new values (and perhaps delete records that were deleted)
Then you run another that gets the inserted rows
Add this info to your file as well
Likewise, insert those rows into table 2.
In the end, it's a coinflip on whether or not this is faster-- but at least it'll run when you can schedule it-- but the flashback query is basically the same thing and letting oracle handle it.
It's much more simple and perhaps less error prone to just use flashback query in my opinion.
Without making any schema change, your solution requires a table scan on both sides - that's the most efficient you'll be able to do. You simply have to LEFT JOIN A to B and compare every field.
However, if you expect to do this in the future, you can do a couple things to make life easier: you can add a calculated hash, which will almost always detect a change. Compare this field, and you can identify changes. Add an index to each table with the PK field(s) and this hash field, and you can very quickly find new and changed records:
SELECT
A.PK,
CASE
WHEN B.PK IS NULL THEN 'New'
WHEN A.Hash <> B.Hash THEN 'Changed'
ELSE 'Unchanged' END AS Status
FROM
A
LEFT JOIN B ON A.PK = B.PK
Microsoft SQL offers a ROWVERSION construct which makes this type of operation simple; it looks like the equivalent in Oracle is system change numbers: https://stackoverflow.com/a/20488598/565869.
If for any reason that doesn't work for you, you could always roll your own using triggers or by modifying the sprocs which update these tables (you are wrapping your UPDATEs in sprocs, right?)
Best Answer