Re: OR Expansion

From: Stefan Koehler <contact_at_soocs.de>
Date: Tue, 30 Apr 2024 11:47:05 +0200 (CEST)
Message-ID: <1530840348.4011170.1714470425136_at_ox.hosteurope.de>

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

Received on Tue Apr 30 2024 - 11:47:05 CEST