Hi Team,

Can you pleas eheve alook ate below scenario and suggest any better alternative to achive it

I have Staging table - Tb_Staging_0001
Columns - Card_No, Address_1, .. Address_5, Credit_amount, Debit_amount, .... so on..

And Application table (tb_card_details) having exactly same columns as that of Staging table.

Daily job which move data from Staging to Application table.

Current Logic -
For every row in Tb_Staging_0001,
for (select * from Tb_Staging_0001)
Loop
it compare all columns with Application table.
like
IF Tb_Staging_0001.Address_1 != tb_card_details.Address_1
THEN
mark it as modified
END IF;
IF Tb_Staging_0001.Address_2 != tb_card_details.Address_2
THEN
mark it as modified
END IF;
..
so on

and mark those columns as Modified for audit purpose.
end loop;

Problem here is staging tables contains around millions of records, and in loop it becomes time comsuming (IF THEN for each loop)

Can you please suggest any better way, so that I can try it out.

  merge into STAGING s
  using ( select * from APP_TABLE ) a
   on (  s.PK_COL1  = a.PK_COL1
     and s.PK_COL2  = a.PK_COL2  -- ie, primary key columns
     and ...
     )
  when matched then
  update set
    s.MODIFIED = 'YES'
  where   decode(s.COL1,a.COL1,1,0) = 0
     or   decode(s.COL2,a.COL2,1,0) = 0
     or   decode(s.COL3,a.COL3,1,0) = 0
     or   decode(s.COL4,a.COL4,1,0) = 0
     or   decode(s.COL5,a.COL5,1,0) = 0
     ...