How can I prevent UNPIVOT from being transformed into UNION ALL?

Question

I have a somewhat complex Oracle query which is taking about half an hour to complete. If I take the slow part of the query and run it separately it finishes in a few seconds. Here's is a screenshot of the SQL Monitor report for the isolated query:

Here is the same logic when run as part of the full query:

The colors correspond to the same tables in both screenshots. For the slow query, Oracle is doing a MERGE JOIN between two tables that do not have an equality condition in the JOIN. As a result of that, about 150 million intermediate rows are unnecessarily processed.

I am able to work around this problem with query hints or rewrites, but I want to understand as much of the root cause as I can so that I can avoid this problem in the future and possibly submit a bug report to Oracle. Every time I get the bad plan the UNPIVOT in the query text is transformed into a UNION ALL in the plan. To further investigate I would like to prevent that query transformation from happening. I have been unable to find a name for this transformation. I also have not been able to find a query hint or underscore parameter that will prevent it. I'm testing on a development server so anything goes.

Is there anything that I can do to prevent the query transformation of the UNPIVOT to UNION ALL? I am on Oracle 12.1.0.2.

I am not able to share the query, table names, or data for IP reasons. I was not able to come up with a simple reproduction. With that said, it's unclear to me why that information is needed to answer the question. Here is an example of an UNPIVOT query along with the same query implemented as UNION ALL.

Answer 1

Try the following Oracle optimizer hint:

NO_EXPAND

This hint disables OR-expansion. OR-expansion transforms combined OR-conditions or IN-lists in the where clause into a compound query with the union all.

We don't have your SQL so this is a guess but seems to a viable option.

As a second option, since you are in a development environment, you can try the Oracle optimizer hint:

NO_QUERY_TRANSFORMATION

but this hint disables all query transformations that the optimizer can perform other than transformations that the optimizer always can apply.