OR Expansion
Date: Tue, 30 Apr 2024 08:52:48 +0000
Message-ID: <GVXPR03MB10248915B7C17F6252C599515D71A2_at_GVXPR03MB10248.eurprd03.prod.outlook.com>
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:
- What query block name should I use with OR_EXPAND hint to force OR expansion ?
- 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"@"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-lReceived on Tue Apr 30 2024 - 10:52:48 CEST