So I've been working on this query for days, learning about explain plans, trying everything anyone suggests, and it's not much better than when I started. I'm trying to refactor an expensive update that joins two very large tables to use a temp table. After getting the data correct on the temporary table, I use the following update to move the data from the temp table to the target table. I don't want to update the entire table with nulls if they don't have anything matching the SET clause, thus the IN clause.
It's performing much more poorly than the initial update that was replaced, so is there something I can do to better how this processes?
update target_table tt
set (tt.tt_id,
tt.dt_tm,
tt.parent_ident,
tt.type_cd,
tt.fk_id,
tt.fk_id2) =
(select temp.tt_id,
temp._dt_tm,
temp.parent_ident,
temp.type_cd,
temp.fk_id,
temp.fk_id
from temp_table temp
where temp.tt_rowid = tt.rowid)
where tt.rowid in (select temp2.tt_rowid
from temp_table temp2);