#StackBounty: #oracle #oracle-12c #unpivot How can I prevent UNPIVOT from being transformed into UNION ALL?

Bounty: 200

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:

ok plan

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

bad plan

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

Get this bounty!!!