Re: OR Expansion

BTW, I ran the example and added the following hints: /*+

                no_ansi_rearch
                no_decorrelate(_at_sel$1)

This produced a different result set (and I don't mean the same result set in a different order).
Adding hints to a query should not change the result set, so this is a bug. I was running 19.11, so it's possible that the bug has been fixed in more recent RUs - would anyone care to test this

Regards
Jonathan Lewis

On Tue, 30 Apr 2024 at 09:53, Cornea D.C. <dmarc-noreply_at_freelists.org> wrote:

> 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))
>
>
>
>
>
> SETUP:
>
>
>
> create table t1_dc
>
> as
>
> with generator as (
>
> select
>
> rownum id
>
> from dual
>
> connect by level <= 1000
>
> )
>
> select
>
> rownum col1,
>
> DBMS_RANDOM.string('x',10) col2
>
>
>
> from
>
> generator v1
>
> ;
>
>
>
> create table t2_dc
>
> as
>
> with generator as (
>
> select
>
> rownum id
>
> from dual
>
> connect by level <= 10000
>
> )
>
> select
>
> trunc(DBMS_RANDOM.value(1,10000)) col1,
>
> SYSDATE-1000+DBMS_RANDOM.VALUE(0,1000) start_date,
>
> SYSDATE+DBMS_RANDOM.VALUE(0,1000) end_date,
>
> rownum col2
>
> from
>
> generator v1
>
> ;
>
>
>
>
>
> begin
>
> dbms_stats.gather_table_stats(
>
> ownname => user,
>
> tabname =>'T1_DC'
>
> );
>
> end;
>
> /
>
>
>
>
>
> begin
>
> dbms_stats.gather_table_stats(
>
> ownname => user,
>
> tabname =>'T2_DC'
>
> );
>
> end;
>
> /
>
>
>
>
>
> select /*+ gather_plan_statistics */
>
> t1_dc.*
>
> ,t2_dc.*
>
> from
>
> t1_dc
>
> left outer join
>
> t2_dc
>
> on
>
> (t1_dc.col1 = t2_dc.col1
>
> and
>
> (t2_dc.start_date <= date'2023-06-12' and t2_dc.end_date >=
> date'2026-06-07'
>
> or (t2_dc.col2 < 1000)
>
> )
>
> );
>
>
>
>
>
> SQL_ID dbhzcndrm514d, child number 0
>
> -------------------------------------
>
> select /*+ gather_plan_statistics */ t1_dc.* ,t2_dc.* from
>
> t1_dc left outer join t2_dc on (t1_dc.col1 = t2_dc.col1 and
>
> (t2_dc.start_date <= date'2023-06-12' and t2_dc.end_date >=
>
> date'2026-06-07' or (t2_dc.col2 < 1000) ) )
>
> Plan hash value: 1185599713
>
>
> ------------------------------------------------------------------------------------------------------------------------------------
>
> | Id | Operation | Name | Starts | E-Rows |
> A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
>
>
> ------------------------------------------------------------------------------------------------------------------------------------
>
> | 0 | SELECT STATEMENT | | 1 |
> | 1025 |00:00:00.01 | 48 | | | |
>
> |* 1 | HASH JOIN OUTER | | 1 | 1000
> | 1025 |00:00:00.01 | 48 | 3363K| 3363K| 5321K (0)|
>
> | 2 | TABLE ACCESS STORAGE FULL | T1_DC | 1 | 1000
> | 1000 |00:00:00.01 | 4 | 1028K| 1028K| |
>
> | 3 | VIEW | VW_DCL_96CC1ED3 | 1 | 2416
> | 2436 |00:00:00.01 | 44 | | | |
>
> |* 4 | TABLE ACCESS STORAGE FULL| T2_DC | 1 | 2416
> | 2436 |00:00:00.01 | 44 | 1028K| 1028K| |
>
>
> ------------------------------------------------------------------------------------------------------------------------------------
>
> Query Block Name / Object Alias (identified by operation id):
>
> -------------------------------------------------------------
>
> 1 - SEL$34B01962
>
> 2 - SEL$34B01962 / T1_DC_at_SEL$1
>
> 3 - SEL$B0BF363E / VW_LAT_14837165_at_SEL$14837165
>
> 4 - SEL$B0BF363E / T2_DC_at_SEL$1
>
> Outline Data
>
> -------------
>
> /*+
>
> BEGIN_OUTLINE_DATA
>
> IGNORE_OPTIM_EMBEDDED_HINTS
>
> OPTIMIZER_FEATURES_ENABLE('19.1.0')
>
> DB_VERSION('19.1.0')
>
> ALL_ROWS
>
> OUTLINE_LEAF(_at_"SEL$B0BF363E")
>
> DECORRELATE(_at_"SEL$09043755")
>
> OUTLINE_LEAF(_at_"SEL$34B01962")
>
> DECORRELATE(_at_"SEL$B0BF363E")
>
> OUTLINE(_at_"SEL$09043755")
>
> OUTLINE(_at_"SEL$AC486781")
>
> MERGE(_at_"SEL$7BFBC4E4" >"SEL$B11BECBC")
>
> OUTLINE(_at_"SEL$14837165")
>
> OUTLINE(_at_"SEL$B11BECBC")
>
> ANSI_REARCH(_at_"SEL$2")
>
> OUTLINE(_at_"SEL$7BFBC4E4")
>
> ANSI_REARCH(_at_"SEL$1")
>
> OUTLINE(_at_"SEL$1")
>
> OUTLINE(_at_"SEL$2")
>
> FULL(_at_"SEL$34B01962" "T1_DC"_at_"SEL$1")
>
> NO_ACCESS(_at_"SEL$34B01962" "VW_LAT_14837165"_at_"SEL$14837165")
>
> LEADING(_at_"SEL$34B01962" "T1_DC"_at_"SEL$1"
> "VW_LAT_14837165"_at_"SEL$14837165")
>
> USE_HASH(_at_"SEL$34B01962" "VW_LAT_14837165"_at_"SEL$14837165")
>
> FULL(_at_"SEL$B0BF363E" "T2_DC"_at_"SEL$1")
>
> END_OUTLINE_DATA
>
> */
>
> Predicate Information (identified by operation id):
>
> ---------------------------------------------------
>
> 1 - access("T1_DC"."COL1"="ITEM_1")
>
> 4 - storage((("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))
>
> filter((("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))
>
>
>
> Regards,
>
> Dragos C
>
>
>
> -----------------------------------------------------------------------------------------------
> 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