Re: OR Expansion
Date: Tue, 30 Apr 2024 12:28:23 +0100
Message-ID: <CAGtsp8neC0Yot0Ycnk++osWSN8BFskOpJwCPz6jGtBVMXxgAgA_at_mail.gmail.com>
I don't really have anything to add to Stefan's comments, although Nenad's article (a) applies to different query structure and (b) may be out of date by now (more than 3 years later) so if you do want to spend a little more time trying to work around the problem I suggest starting with the simplification:
elect
*
from t2_dc
where
( t2_dc.end_date >= to_date(' 2026-06-07 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
and t2_dc.start_date <= to_date(' 2023-06-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
)
or t2_dc.col2<1000
/
If you can't get that to use or expansion you won't be able to get or expansion when the optimizer has rewritten you ON condition to be a decorrelation view. (If you can get it to use or expansion then that might give you some clues about things you could try with the more complex example.)
Regards
Jonathan Lewis
On Tue, 30 Apr 2024 at 10:48, Stefan Koehler <contact_at_soocs.de> wrote:
> 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: 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))
> >
> --
> http://www.freelists.org/webpage/oracle-l
> > >
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Apr 30 2024 - 13:28:23 CEST