Re: Adaptive plans bypassed - but why ?
Date: Mon, 18 May 2020 17:39:47 -0400
Message-ID: <9a249f97-e36f-56c4-0636-85d5d76c3231_at_gmail.com>
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-lReceived on Mon May 18 2020 - 23:39:47 CEST