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