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.