Re: Adaptive plans bypassed - but why ?

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):
>

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217


--
http://www.freelists.org/webpage/oracle-l