Using Oracle's MERGE for pure deletes

chulett · Post by **chulett** » Wed May 23, 2007 1:54 pm

Just curious if any Oracle peoples out there have any thoughts on using a MERGE for pure deletes? While it is great for "upsert" type operations, it doesn't really seem to provide a pure delete functionality.

From what I can tell, you have an optional DELETE WHERE clause that can be added to the WHEN MATCHED operation. Problem is it seems to want to do some kind of an update first and then possibly also delete after the update if the delete where clause is also true. It's not an 'either/or' but more of a 'one or both' thing.

That doesn't seem all that efficient when you want to do are pure deletes. I'm not looking for alternatives to the MERGE for deletes, I've got them. I'm just looking for some verification of my understanding as to How It Works, especially wrt the DELETE WHERE part of the game.

Thanks!

ray.wurlod · Post by **ray.wurlod** » Wed May 23, 2007 2:41 pm

Have you thought about asking Tom?

chulett · Post by **chulett** » Wed May 23, 2007 3:05 pm

I've been all over Tom's you-know-what but couldn't really seem to find a definitive answer. And it's not like you can ask anything new, there's always a huge backlog it seems.

So I thought I would ask here.

narasimha · Post by **narasimha** » Wed May 23, 2007 3:07 pm

Craig,

Got this from the web-

Code: Select all

The DELETE where_clause can be used to clean up data in a table while populating or updating it. The only rows affected by this clause are those rows in the destination table that are updated by the merge operation. That is, the DELETE WHERE condition evaluates the updated value, not the original value that was evaluated by the UPDATE SET ... WHERE condition. If a row of the destination table meets the DELETE condition but is not included in the join defined by the ON clause, then it is not deleted.

To complement your views, we can say that MERGE is not a good option for pure Deletes.

My two cents..

chulett · Post by **chulett** » Wed May 23, 2007 3:09 pm

That's more like what I was looking for... curious where you found it. Can you post a link?

narasimha · Post by **narasimha** » Wed May 23, 2007 3:11 pm

Sure..

http://oraclesvca2.oracle.com/docs/cd/B ... s_9016.htm

chulett · Post by **chulett** » Wed May 23, 2007 3:23 pm

Dang, right out of the freaking manual.

narasimha · Post by **narasimha** » Wed May 23, 2007 5:05 pm

ivannavi · Post by **ivannavi** » Sun May 27, 2007 3:01 am

No need for MERGE if you want pure delete. MERGE has been with us only since Oracle 9something. You could search for "EXISTS" statement combined with DELETE.

chulett · Post by **chulett** » Sun May 27, 2007 6:59 am

Thanks. As I noted, I have plenty of other solutions, was curious about MERGE specifically in this context. And even though I don't think I specifically said so, 'this context' was high-speed deletes, alternatives to 'normal' DML that would take ages to delete the million plus records (from multiple tables) I may need to do at any given time.