Tag Archives: Merge

MERGE and DML RETURNING clause in Oracle 23ai

For a long time, we all “knew” that MERGE did not support a RETURNING clause. You can ask ChatGPT, Gemini, or Google, and the answer would still be the same: Nope, no luck! LLMs respond with confidence, and Google’s top results show various workarounds for the problem. I swear I saw the lifting of this restriction in the New Features Guide back when the version was still called 23c :-). Since I’m currently preparing a conference talk where this is relevant, I naturally wanted to check whether my memory was just playing tricks on me — or if it’s actually true.

Continue reading

ORA-30926 in Oracle 23ai: What’s Changed?

One of the topics I’ve found very interesting as I started blogging was the sometimes strange behavior of the MERGE statement, the reasons for ORA-30926 and what it has to do with write consistency. My first blog post about it was almost exactly ten years ago. Time for a follow up! Especially because there are some changes in Oracle 23ai.

Continue reading

MERGE and ORA-30926 – Part 2 or differences in write consistency between update and merge

In the first part we’ve seen how Oracle actually executes the Merge Statement three times if it hits ORA-30926. To understand what’s going on, we should refresh the concept behind so called “update restarts” sometimes also called “mini rollbacks”. This concept is very well explained by Tom Kyte: Part I, Part II and Part III. If you are not familiar with this “write consistency” concept, I strongly encourage you to follow this links. Continue reading

MERGE and ORA-30926

If you’ve ever used MERGE, then it is quite probable that you are familiar with ORA-30926 – unable to get a stable set of rows in the source tables. You have also probably noticed, that in case of hitting this error, your query takes longer. Three times longer, because the whole query is executed three times, as following tests show.

Continue reading

Merging temporal intervals using MATCH_RECOGNIZE

Some time ago Philipp Salvisberg has posted several thoughts about joining and merging temporal intervals. Recently I was looking for some examples of using the new MATCH_RECOGNIZE clause introduced in 12c and found that these queries can be significantly simplified using the new clause.
Continue reading

BYE-BYE, BYPASS_UJVC!

It was that hint /*+ BYPASS_UJVC */ that ten years ago already taught me a lesson on how to deal with undocumented hints in productive environments. Admittedly, I still have used it since then for ad hoc data manipulation a few times. Because of a strange behavior in 11gR2 database, I decided to look again at the issue of “one-pass” updates of a join and their alternative using merge sql statement. Continue reading