Oracle blog

How to update joining column using MERGE.

Updating joining column in MERGE command:

Let's say you want to merge "SOURCE" table into "TARGET" table with "ID" column as joining condition and you want to update "ID" column too.

merge into TARGET using
(select target.rowid rid, source.* from SOURCE, TARGET
where source.id = target.id(+)
) SOURCE
on (TARGET.rowid = SOURCE.rid)
when matched then
update set target.id = source.id, target.column2 = source.column2, ....
when not matched then
insert values (source.id, source.column2, ....)
/


In fact this is the only to come up with a plan using INDEX_FFS.

Say, you have about 90% updates and 10% inserts to go into TARGET table from SOURCE table, and you do have an index on "TARGET.ID" column, but you are not intending to update "ID" column.

If doing full table scan of "TARGET" table is more expensive than doing index fast full scan on "TARGET.ID" with table lookups by rowid, then above mentioned merge command would give you the best plan that Oracle is unable to come up with if you dont use inline view subquery in place of "SOURCE" table.

posted by Saibabu Devabhaktuni @ 11:40 PM