Using Oracle's MERGE for pure deletes
Moderators: chulett, rschirm, roy
Using Oracle's MERGE for pure deletes
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!
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!
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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..
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.
My two cents..
Narasimha Kade
Finding answers is simple, all you need to do is come up with the correct questions.
Finding answers is simple, all you need to do is come up with the correct questions.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers