Hi Martin!
The query seems to be huge and heavily hinted. The
"TEMP_TABLE_TRANSFORMATION" is most likely caused by the "MATERIALIZE"
hint. Basically, such queries are unmanageable. This query should be a
candidate for a rewrite, using slice & dice CTE. The "WITH" clause makes
the queries more logical and readable. You seem to be dealing with a
monolithic query of frightening proportions. Such queries are usually
not a part of COTS applications, they are reporting queries written by
the customer. Using materialized view proves that beyond reasonable
doubt. That means that the query is an in-house monstrosity which can be
rewritten. I understand that this is not an answer to your question, but
this is the best advice I can give you.
Regards
On 5/18/20 5:01 PM, Martin Berger wrote:
Hi Jonathan,--
this query seems to have something of all, but I can't bring it into right order:
The Plan (obfuscated):
