I am not going to dig in the details of disjunctive subqueries and their inability to be unnested by the CBO for releases prior to 12c. I will be writing in a near future (I hope) a general article in which disjunctive subqueries will be explained and popularized via a reproducible model. The goal of this brief blog post is just to show how I have been successful to trouble shoot the above web service performance issue by transforming a disjunctive subquery into an UNION ALL SQL statement so that I gave the CBO an opportunity to choose an optimal plan.

Note the apparition of the FILTER operation (n°2) which is less efficient. One of the dramatic consequences of that is the NESTED LOOP operation (n°4) which has been started 937,000 times without producing any rows but nevertheless generating almost 4 millions of buffer gets. Because of this disjunctive subquery, Oracle is not able to merge the subquery clause with the rest of the query in order to consider another optimal path.

There is a simple technique if you want to re-write the above query in order to get rid of the disjunctive subquery: use of an UNION ALL as I did for my original query (bear in mind that in my actual case COL_UK column is NOT NULL)

I went from a massif 3,771,234 of consistent gets to just 8 logical I/O. The subquery has been transformed into an in-line view (VW_SQ_1) while the not always good FILTER operation disappeared letting the place to rapid operations accurately estimated by the CBO without re-computing the statistics in between

Bottom Line: Trouble shooting a query performance problem can sometime be achieved by reformulating the query so that you give the CBO a way to circumvent a transformation it can’t do with the original query.

I found that it is always educative and nice to name the technique we have used or have circumvented using its known name in the Oracle community or in the ANSI SQL nomenclature. As such, it became easy to say disjunctive subquery instead of a subquery that is preceded by an OR predicate. If you want to know more about the subquery terminology then I will encourage you to read this article : http://www.vldb.org/pvldb/2/vldb09-423.pdf

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.