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 reading
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.
One of the new query optimizations in 12c are the null-accepting semi-joins. They make subquery unnesting and a semi-join possible in a situation which led to FILTER operation in older versions. Continue reading
Actually, the CBO usually does a good job optimizing “NOT IN” subqueries. Unless something goes wrong. Recently it took a long time on a 10.2.0.5 database to search for the reason. At last looking in the CBO trace files (event 10053) has given the right hint.
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