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 readingOnline Statistics Gathering has been introduced in 12c and is a very handy feature for ETL and batch jobs developers. However the devil is in the detail. There are some points to remember. Let’s take a closer look. Continue reading
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
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 readingSome 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
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