1. Assuming you're accessing real tables (not views) the query is pretty simple, so there's not much to do.
I'd check the EXISTS subquery - is this necessary? It means you're reading the rows in ia_loan_raw_stage (at least) twice.
It may also be unnecessary from a logical perspective. Essentially it says:
"Remove the values from STAGE that are already in RAW"
So you're only left with new values. Meaning the UPDATE also will have nothing to do.
Is this correct?
If so, going further - as this is a staging table - is it possible to NOT insert the existing values from RAW into STAGE in the first place? This means you can skip the MINUS check altogether.
I'd also check if there's index on:
im.ia_loan_data_raw ( recorded_date_end, recorded_date_start )
and - if not - try creating one.
There may also be optimizations you can do based on how likely it is the values in the staging table already exist in ia_loan_data_raw.
If 99% of the time
2. It might be faster to change it to use NOT EXISTS instead, e.g.:
select * from im.ia_loan_raw_stage
where not exists (
select * from im.ia_loan_data_raw
where ...
);
This is something you'll need to try out though.
3. You should be able to rewrite it to a single MERGE:
merge into im.ia_loan_data_raw r
using (
select * from im.ia_loan_raw_stage
minus
select * from im.ia_loan_data_raw
where ...
) s
on ( ... )
when matched then
update set ...
when not matched then
insert ( ... ) values ( ... );