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
UNION ALL? I am on Oracle 220.127.116.11.
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.