DSXchange

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!

Have you thought about asking Tom?

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.

Craig,

Got this from the web-
To complement your views, we can say that MERGE is not a good option for pure Deletes.

My two cents..

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

Sure..

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

Dang, right out of the freaking manual.

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.

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.