Re: OR Expansion

An important aspect to be aware of or expansion is not equivalent with writing the query using UNION ALL as you can change the query result. You must include filtering using LNNVL function.

Now in regards to what you want to achieve, it is highly probable to be technically possible (if I understood correctly the question). What you can do is either build the query yourself by replacing all the view names with the select definition or you do a run of the query using 10053 enabled and extract final query after transformation (ideally to do some checks to be sure is correct you might have surprises)

Once you have this query you play around with hints in the code and do quick runs until you obtain the plan you need , you extract the outline of the resulted plan and try it into the original code. If works you can either create profile with hints required (or even with full outline if just the or_expand hints are ignored )

Good luck.

În mar., 30 apr. 2024 la 13:13, Cornea D.C. <dmarc-noreply_at_freelists.org> a scris:

> Hi Stefan,
>
> That's what I was afraid of :)
> Initially I tried to use concatenation (USE_CONCAT) but that one also
> require an indexed access path available to drive every branch of the
> rewrite (
> https://jonathanlewis.wordpress.com/2020/08/19/subquery-with-or-3/),
> hence decided to give a try to this 'new' OR expansion (creating indexes is
> out of discussion).
>
> Regards,
> Dragos C
>
>
> -----Original Message-----
> From: Stefan Koehler <contact_at_soocs.de>
> Sent: Tuesday, April 30, 2024 12:47 PM
> To: Cornea, D.C. (Dragos - Cristian) <dragos-cristian.cornea_at_ing.com>;
> oracle-l_at_freelists.org
> Subject: Re: OR Expansion
>
> Hello Dragos,
> I guess your questions are regarding your test case setup as the provided
> hints fit to this example. The reason why no hint works is because your
> object setup is way too simple - snippets from CBO trace:
> --------------------8<----------------------
> ORE: Checking validity of OR Expansion for query block SEL$34B01962 (#1)
> ...
> ORE: bypassed - No valid predicate for OR expansion.
>
> ORE: Checking validity of OR Expansion for query block SEL$B0BF363E (#2)
> ...
> ORE: Bypassed for disjunct chain: No Index or Partition driver found.
>
> Query Block Registry:
> SEL$2 0x83f69870 (PARSER)
> SEL$B11BECBC 0x83f69870 (QUERY ANSI REARCHiTECTURE SEL$2; SEL$2)
> SEL$AC486781 0x83f69870 (VIEW MERGE SEL$B11BECBC; SEL$7BFBC4E4;
> SEL$B11BECBC)
> SEL$34B01962 0x83f69870 (QUERY VIEW DECORRELATED SEL$AC486781;
> SEL$B0BF363E) [FINAL]
> SEL$B11BECBC 0x83f69870 (QUERY ANSI REARCHiTECTURE SEL$2; SEL$2)
> ...
> SEL$1 0x83c8ac88 (PARSER)
> SEL$7BFBC4E4 0x83c8ac88 (VIEW ANSI REARCHiTECTURE SEL$14837165; SEL$1)
> SEL$AC486781 0x83f69870 (VIEW MERGE SEL$B11BECBC; SEL$7BFBC4E4;
> SEL$B11BECBC)
> ...
> SEL$14837165 0x83c8ac88 (QUERY BLOCK TABLES CHANGED SEL$1)
> SEL$7BFBC4E4 0x83c8ac88 (VIEW ANSI REARCHiTECTURE SEL$14837165; SEL$1)
> ...
> SEL$09043755 0x83c874c8 (SPLIT/MERGE QUERY BLOCKS SEL$14837165)
> SEL$B0BF363E 0x83c874c8 (VIEW DECORRELATED SEL$09043755;
> SEL$09043755) [FINAL]
> SEL$34B01962 0x83f69870 (QUERY VIEW DECORRELATED SEL$AC486781;
> SEL$B0BF363E) [FINAL]
> SEL$B0BF363E 0x83c874c8 (VIEW DECORRELATED SEL$09043755;
> SEL$09043755) [FINAL]
> ...
> --------------------8<----------------------
>
> For the latter bypass check you find a more detailed explanation here:
> https://nenadnoveljic.com/blog/or-expansion-of-subqueries-limitations/
>
>
> Best Regards
> Stefan Koehler
>
> Independent Oracle performance consultant and researcher
> Website: http://www.soocs.de/
> Twitter: _at_OracleSK
>
> > Cornea D.C. <dmarc-noreply_at_freelists.org> hat am 30.04.2024 10:52 CEST
> geschrieben:
> >
> >
> > Hello list,
> >
> > RDBMS: 19.18 (on Exadata)
> >
> > Having multiple OR predicates in some very complex queries, I tried to
> force OR expansion (by default optimizer is not choosing it) to see how it
> performs.
> > Optimizer chose to do a full table scan on a big partition and later in
> the plan used a FILTER operation to apply OR predicates.
> > However, if I manually split the statement into an UNION ALL, execution
> time is much better as storage indexes are used and even if a FTS is still
> used on both branches, elapsed time is very low.
> > Entire logic is encapsulated in views (multiple view layers are
> involved) and is almost impossible to change the source code of these views
> (only the main statement can be altered; it's a simple SELECT ... FROM
> view_x WHERE ...).
> >
> > My testing scenario is somehow similar with the one described in this
> page: https://hourim.wordpress.com/2017/06/17/12cr2-or-expansion/ (LEFT
> join and DCL).
> >
> > I have two questions:
> > 1. What query block name should I use with OR_EXPAND hint to force OR
> expansion ?
> > 2. Is it possible to push OR expansion into views (SELECT ... FROM
> view_A left join view_B ON view_A.col1 = view_B.col1 AND (view_A.col2 like
> '%x%' or view_A.col2 like '%y%') ? In this case OR_EXPAND should be applied
> at the view level or at base table(s) level inside the view(s) ?
> >
> > No matter what query block combination I use, I get either No valid
> predicate for OR expansion or U - Unused (1) in Hint Report:
> >
> > Total hints for statement: 1 (U - Unused (1))
> > ----------------------------------------------------------------------
> > -----
> >
> > 1 - SEL$34B01962
> > U - OR_EXPAND(_at_"SEL$34B01962" (1) (2)) / No valid predicate for OR
> > expansion
> >
> > Total hints for statement: 1 (U - Unused (1))
> > ----------------------------------------------------------------------
> > -----
> >
> > 3 - SEL$B0BF363E
> > U - OR_EXPAND(_at_"SEL$B0BF363E" (1) (2))
> >
>
> -----------------------------------------------------------------------------------------------
> ATTENTION:
> The information in this electronic mail message is private and
> confidential, and only intended for the addressee.
>
> The information in this e-mail does not constitute
> Investment Advice as defined in Art 4 of the Markets
> in Financial Instruments Directive 2004/39/EC. The Markets
> in Financial Instruments Directive 2004/39/EC is not applicable to
> Life insurance and Private pensions products.
>
> Should you receive this message by mistake, you are hereby
> notified that any disclosure, reproduction, distribution or use of this
> message is strictly prohibited. Please inform the sender by
> reply transmission and delete the message without copying or
> opening it.
>
> Messages and attachments are scanned for all viruses known.
> If this message contains password-protected attachments, the
> files have NOT been scanned for viruses by the ING mail domain.
> Always scan attachments before opening them.
>
> -----------------------------------------------------------------------------------------------
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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