Re: Query Transformation

Jonathan,
Back to your initial guess, yes it does appear that LORE has been applied twice, per extracts from 10053 below.
Patrick

(newjo-save) [1 2 0 ]
LORE: Trying or-Expansion on query block SEL$1 (#0)

LORE: ******** Next OR predicate ********

LORE: Trying or-Expansion on query block SEL$1 (#0) on OR chain:

id=0  predicate=("C2"."GMOBJ"=U'2299' OR "C2"."GMSUB"=U'IDT' AND
"C2"."GMOBJ"=U'2019') AND ("C1"."RZVR01"=:B1 AND "C2"."GMOBJ"=U'2299' OR
"C1"."RZVR01"=:B2 AND "C2"."GMOBJ"=U'2019' AND "C2"."GMSUB"=U'IDT') AND

(  ( C1.RZVR01 = :nc1 AND c2.GMOBJ = '2299')       OR
   ( C1.RZVR01 = :nc2 AND C2.GMOBJ = '2019' AND C2.GMSUB = 'IDT'  )   )  AND
    ( P.RYPYID = C1.RZPYID AND P.RYGLBA = C2.GMAID )

---
LOTS OF STUFF OMITTED
---

LORE: or-expansion-subheap (delete addr=0x7fb0bdceff00, in-use=119328,
alloc=126408)

LORE: ******** OR-branching ********

LORE: or-expansion-subheap (create addr=0x7fb0bdceff00)
****************
QUERY BLOCK TEXT
****************
SELECT  null FROM P, C1, C2 WHERE
(  ( C1.RZVR01 = :nc1 AND c2.GMOBJ = '2299')       OR
   ( C1.RZVR01 = :nc2 AND C2.GMOBJ = '2019' AND C2.GMSUB = 'IDT'  )   )  AND
    ( P.RYPYID = C1.RZPYID AND P.RYGLBA = C2.GMAID )

On Mon, 25 Jan 2021 at 11:46, Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:



>

> "Doesn't work at all" might be a little bit of hyperbole, but I've just


> done a quick search on MOS for "_optimizer_cbqt_or_expansion" and it

> reported a significant number of documents and bugs which suggested it be

> set to OFF.

>

> Regards

> Jonathan Lewis

>

>

> On Mon, 25 Jan 2021 at 11:13, Moustafa Ahmed <moustafa_dba_at_hotmail.com>

> wrote:

>

>> I heard from someone in oracle real world performance team that

>>

>> _optimizer_cbqt_or_expansion

>>

>> Must be disabled at 19c

>> As it has a bug and it does not work at all!

>>

>>

>> On Jan 25, 2021, at 5:44 AM, Patrick Jolliffe <jolliffe_at_gmail.com> wrote:

>>

>> cost-based or-expansion

>>

>>


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