Thursday, June 15, 2006

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.

2 Comments:

At July 13, 2012 at 11:55:00 PM PDT, Anonymous iPhone4S shell telepon said...

I still thought it had been practical. Excellent post!

 
At September 21, 2012 at 9:40:00 AM PDT, Blogger Mike said...

This is very informative post. I wasn't aware of these things.This is great feature provided by oracle. This post surely enhances your knowledge about this concept. I also got to know something new :).
sap upgrades

 

Post a Comment

Links to this post:

Create a Link

<< Home