Its database version 11.2.0.4 of Oracle. We have a requirement for our ETL process to UPDATE the changed data in the target table as compared with incoming source data. We do have primary key/alternate key in the tables. We are expecting ~1 to ~5% of change in the data in daily basis.
So one straight approach we can think of is, If we have ~10 columns but primary key is on one column C1, so we have to compare change in each of those ~9 column data for each row of the source data with reference to the primary key, and if any changes visible then only we have to update the changed row in the target table. But in this case we have to compare each and every column of all the rows of source and target table and will be slow operation. Is there any better way to achieve above logic?
Update tab1
set (c2.. c10) IN
(SELECT c2,.. c10 from TAB2
WHERE tab1.c1= tab2.c1
AND (TAB1.c2<>=tab2.c2 or TAB1.c3<>tab2.c3 or tab1.c4<>tab2.c4.....or tab1.c10<>tab2.c10)
)
Update:-
We are using informatica as ETL tool, and team suggests adding a new column to the table in database, which will store the calculated checksum/hexadecimal number of each row in the target table,(and for this informatica will use MD5 function). And then it will only compare the checksum column with the source checksum for that same row, if any change found, then only it will Update that row. Will this design be more efficient, as because it will avoid comparing each and every column value?