Oracle关于半连接SQL执行计划的执行路径一些有趣的实验
从摩天轮的问答里边看到的一个问题，https://www.modb.pro/issue/34573
大概有这么条SQL(包括环境构造语句)，如何强制走出nested loops的执行路径。
create table tb1 as select * from dba_objects;
create table tb2 as select * from dba_objects;
create index idx_tb1 on tb1(object_id);
create index idx_tb2 on tb2(object_id);
create table t99 as select * from (select * from tb1 order by dbms_random.value) where rownum<=99;
create view v_t1t2 as select * from tb1 union all select * from tb2;
exec DBMS_STATS.GATHER_TABLE_STATS (user, 'TB1');
exec DBMS_STATS.GATHER_TABLE_STATS (user, 'TB2');
exec DBMS_STATS.GATHER_TABLE_STATS (user, 'T99');
--原生SQL：
select a.owner,a.object_id from v_t1t2 a where exists (select 1 from t99 b where a.object_id=b.object_id);
默认SQL会走出”HASH JOIN RIGHT SEMI“的执行路径，https://www.cnblogs.com/PiscesCanon/p/18130472
-----------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)| OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 447 (100)| | | |
|* 1 | HASH JOIN RIGHT SEMI| | 196 | 447 (1)| 2293K| 2293K| 1528K (0)|
| 2 | VIEW | VW_SQ_1 | 99 | 2 (0)| | | |
| 3 | TABLE ACCESS FULL | T99 | 99 | 2 (0)| | | |
| 4 | VIEW | V_T1T2 | 109K| 444 (1)| | | |
| 5 | UNION-ALL | | | | | | |
| 6 | TABLE ACCESS FULL| TB1 | 54626 | 222 (1)| | | |
| 7 | TABLE ACCESS FULL| TB2 | 54627 | 222 (1)| | | |
-----------------------------------------------------------------------------------------
如果强制使用/*+ nl_sj */，并不能走出嵌套循环半连接，优化器会自动给你转成内连接并使用了hash join。
这里我觉得优化器是有问题的，既然我使用/*+ nl_sj */强制你走嵌套循环半连接，你不应该给我转内连接。
select a.owner,a.object_id from v_t1t2 a where exists (select /*+ unnest nl_sj */ 1 from t99 b where a.object_id=b.object_id);
-----------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)| OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 448 (100)| | | |
|* 1 | HASH JOIN | | 194 | 448 (1)| 2293K| 2293K| 1540K (0)|
| 2 | VIEW | VW_SQ_1 | 99 | 2 (0)| | | |
| 3 | HASH UNIQUE | | 98 | | 2063K| 2063K| 1322K (0)|
| 4 | TABLE ACCESS FULL| T99 | 99 | 2 (0)| | | |
| 5 | VIEW | V_T1T2 | 109K| 444 (1)| | | |
| 6 | UNION-ALL | | | | | | |
| 7 | TABLE ACCESS FULL| TB1 | 54626 | 222 (1)| | | |
| 8 | TABLE ACCESS FULL| TB2 | 54627 | 222 (1)| | | |
-----------------------------------------------------------------------------------------
其实也是有办法避免内连接的转换的，后续也进行了各种hint的实验，这里直接放结果了(情况4存在点问题)，也可以去摩天轮的那个链接里边看。
原生SQL语句：
情况1：子查询不展开，只能走filter
select a.owner,a.object_id from v_t1t2 a where exists (select /*+ no_unnest */ 1 from t99 b where a.object_id=b.object_id);
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5463 | 117K| 197K (1)| 00:39:29 |
|* 1 | FILTER | | | | | |
| 2 | VIEW | V_T1T2 | 109K| 2347K| 445 (1)| 00:00:06 |
| 3 | UNION-ALL | | | | | |
| 4 | TABLE ACCESS FULL| TB1 | 54626 | 800K| 223 (1)| 00:00:03 |
| 5 | TABLE ACCESS FULL| TB2 | 54627 | 800K| 223 (1)| 00:00:03 |
|* 6 | TABLE ACCESS FULL | T99 | 1 | 5 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------
情况2：子查询展开，走 nested loops semi 执行路径，驱动表 v_t1t2 a
_cost_equality_semi_join=false避免了优化器改写为内连接的可能。
select /*+ opt_param('_cost_equality_semi_join' 'false') */ a.owner,a.object_id from v_t1t2 a where exists (select /*+ unnest nl_sj */ 1 from t99 b where a.object_id=b.object_id);
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 196 | 8428 | 219K (1)| 00:43:51 |
| 1 | NESTED LOOPS SEMI | | 196 | 8428 | 219K (1)| 00:43:51 |
| 2 | VIEW | V_T1T2 | 109K| 3200K| 444 (1)| 00:00:06 |
| 3 | UNION-ALL | | | | | |
| 4 | TABLE ACCESS FULL| TB1 | 54626 | 800K| 222 (1)| 00:00:03 |
| 5 | TABLE ACCESS FULL| TB2 | 54627 | 800K| 222 (1)| 00:00:03 |
|* 6 | VIEW | VW_SQ_1 | 1 | 13 | 2 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | T99 | 99 | 495 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------
情况3：子查询展开，nested loops(被改为内连接)，驱动表 VW_SQ_1 (b去重后的内联视图)
select /*+ leading("VW_SQ_1"@"SEL$04A8DF8C" A) use_nl(a) semi_to_inner("VW_SQ_1"@"SEL$04A8DF8C") */ a.owner,a.object_id from v_t1t2 a where exists (select /*+ unnest */ 1 from t99 b where a.object_id=b.object_id);
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 43521 (100)| 196 |00:00:04.48 | 161K| | | |
| 1 | NESTED LOOPS | | 1 | 194 | 43521 (1)| 196 |00:00:04.48 | 161K| | | |
| 2 | VIEW | VW_SQ_1 | 1 | 99 | 2 (0)| 99 |00:00:00.01 | 3 | | | |
| 3 | HASH UNIQUE | | 1 | 98 | | 99 |00:00:00.01 | 3 | 2063K| 2063K| 1368K (0)|
| 4 | TABLE ACCESS FULL| T99 | 1 | 99 | 2 (0)| 99 |00:00:00.01 | 3 | | | |
|* 5 | VIEW | V_T1T2 | 99 | 2 | 444 (1)| 196 |00:00:04.48 | 161K| | | |
| 6 | UNION-ALL | | 99 | | | 10M|00:00:03.68 | 161K| | | |
| 7 | TABLE ACCESS FULL| TB1 | 99 | 54626 | 222 (1)| 5407K|00:00:00.60 | 80593 | | | |
| 8 | TABLE ACCESS FULL| TB2 | 99 | 54627 | 222 (1)| 5408K|00:00:00.60 | 80593 | | | |
----------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$2E82301F
2 - SEL$8F9407EC / VW_SQ_1@SEL$04A8DF8C
3 - SEL$8F9407EC
4 - SEL$8F9407EC / B@SEL$4
5 - SET$1 / A@SEL$1
6 - SET$1
7 - SEL$2 / TB1@SEL$2
8 - SEL$3 / TB2@SEL$3
情况4：子查询展开，原本想在情况3的被改写为内连接的 nested loops 下，驱动表改为 v_t1t2 a，但没成功，不知道是优化器本身不支持，还是我hint添加有问题。
只能走出：NESTED LOOPS SEMI
select /*+ leading(A) use_nl("VW_SQ_1"@"SEL$04A8DF8C") semi_to_inner(@"SEL$2E82301F" "VW_SQ_1"@"SEL$04A8DF8C") */ a.owner,a.object_id from v_t1t2 a where exists (select /*+ unnest */ 1 from t99 b where a.object_id=b.object_id);
但是理论上来说内连接的NL驱动表和被驱动表是可以通过hint来指定的。
原生SQL等价于下边的内连接改写：这个改写后的SQL就可以随意指定NL的驱动表。
select /*+ leading(a) no_merge(a) no_push_pred(a) use_nl(b) */ a.owner,a.object_id from v_t1t2 a,(select distinct object_id from t99) b where a.object_id=b.object_id;
select /*+ leading(b) no_merge(a) no_push_pred(a) use_nl(a) */ a.owner,a.object_id from v_t1t2 a,(select distinct object_id from t99) b where a.object_id=b.object_id;
情况5：子查询展开， hash join semi，驱动表v_t1t2 a
select /*+ opt_param('_cost_equality_semi_join' 'false') no_swap_join_inputs(@"SEL$2E82301F" "VW_SQ_1"@"SEL$04A8DF8C") */ a.owner,a.object_id from v_t1t2 a where exists (select /*+ unnest hash_sj */ 1 from t99 b where a.object_id=b.object_id);
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 196 |00:00:00.10 | 1644 | 189 | | | |
|* 1 | HASH JOIN SEMI | | 1 | 196 | 196 |00:00:00.10 | 1644 | 189 | 6700K| 2024K| 10M (0)|
| 2 | VIEW | V_T1T2 | 1 | 109K| 109K|00:00:00.05 | 1628 | 189 | | | |
| 3 | UNION-ALL | | 1 | | 109K|00:00:00.04 | 1628 | 189 | | | |
| 4 | TABLE ACCESS FULL| TB1 | 1 | 54626 | 54626 |00:00:00.01 | 814 | 174 | | | |
| 5 | TABLE ACCESS FULL| TB2 | 1 | 54627 | 54627 |00:00:00.01 | 814 | 15 | | | |
| 6 | VIEW | VW_SQ_1 | 1 | 99 | 99 |00:00:00.01 | 16 | 0 | | | |
| 7 | TABLE ACCESS FULL | T99 | 1 | 99 | 99 |00:00:00.01 | 16 | 0 | | | |
------------------------------------------------------------------------------------------------------------------------------
情况6：子查询展开， hash join right semi，驱动表 vw_sq_1 (b去重后的内联视图)
select /*+ opt_param('_cost_equality_semi_join' 'false') */ a.owner,a.object_id from v_t1t2 a where exists (select /*+ unnest hash_sj */ 1 from t99 b where a.object_id=b.object_id);
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 196 |00:00:00.13 | 1645 | | | |
|* 1 | HASH JOIN RIGHT SEMI| | 1 | 196 | 196 |00:00:00.13 | 1645 | 2293K| 2293K| 1427K (0)|
| 2 | VIEW | VW_SQ_1 | 1 | 99 | 99 |00:00:00.01 | 3 | | | |
| 3 | TABLE ACCESS FULL | T99 | 1 | 99 | 99 |00:00:00.01 | 3 | | | |
| 4 | VIEW | V_T1T2 | 1 | 109K| 109K|00:00:00.09 | 1642 | | | |
| 5 | UNION-ALL | | 1 | | 109K|00:00:00.07 | 1642 | | | |
| 6 | TABLE ACCESS FULL| TB1 | 1 | 54626 | 54626 |00:00:00.01 | 821 | | | |
| 7 | TABLE ACCESS FULL| TB2 | 1 | 54627 | 54627 |00:00:00.01 | 821 | | | |
---------------------------------------------------------------------------------------------------------------------
情况7：子查询展开，hash join(被改为内连接)，驱动表 vw_sq_1 (b去重后的内联视图)
select a.owner,a.object_id from v_t1t2 a where exists (select /*+ unnest nl_sj */ 1 from t99 b where a.object_id=b.object_id);
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 196 |00:00:00.13 | 1645 | | | |
|* 1 | HASH JOIN | | 1 | 194 | 196 |00:00:00.13 | 1645 | 2293K| 2293K| 1393K (0)|
| 2 | VIEW | VW_SQ_1 | 1 | 99 | 99 |00:00:00.01 | 3 | | | |
| 3 | HASH UNIQUE | | 1 | 98 | 99 |00:00:00.01 | 3 | 2063K| 2063K| 1338K (0)|
| 4 | TABLE ACCESS FULL| T99 | 1 | 99 | 99 |00:00:00.01 | 3 | | | |
| 5 | VIEW | V_T1T2 | 1 | 109K| 109K|00:00:00.09 | 1642 | | | |
| 6 | UNION-ALL | | 1 | | 109K|00:00:00.07 | 1642 | | | |
| 7 | TABLE ACCESS FULL| TB1 | 1 | 54626 | 54626 |00:00:00.01 | 821 | | | |
| 8 | TABLE ACCESS FULL| TB2 | 1 | 54627 | 54627 |00:00:00.01 | 821 | | | |
---------------------------------------------------------------------------------------------------------------------
情况8：子查询展开，hash join(被改为内连接)，驱动表 v_t1t2 a
select /*+ LEADING(@"SEL$2E82301F" "VW_SQ_1"@"SEL$04A8DF8C") semi_to_inner(@"SEL$2E82301F" "VW_SQ_1"@"SEL$04A8DF8C") swap_join_inputs(a) */ a.owner,a.object_id from v_t1t2 a where exists (select /*+ unnest */ 1 from t99 b where a.object_id=b.object_id);
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 666 (100)| 196 |00:00:00.16 | 1631 | | | |
|* 1 | HASH JOIN | | 1 | 194 | 666 (1)| 196 |00:00:00.16 | 1631 | 6700K| 2024K| 10M (0)|
| 2 | VIEW | V_T1T2 | 1 | 109K| 444 (1)| 109K|00:00:00.09 | 1628 | | | |
| 3 | UNION-ALL | | 1 | | | 109K|00:00:00.07 | 1628 | | | |
| 4 | TABLE ACCESS FULL| TB1 | 1 | 54626 | 222 (1)| 54626 |00:00:00.01 | 814 | | | |
| 5 | TABLE ACCESS FULL| TB2 | 1 | 54627 | 222 (1)| 54627 |00:00:00.01 | 814 | | | |
| 6 | VIEW | VW_SQ_1 | 1 | 99 | 2 (0)| 99 |00:00:00.01 | 3 | | | |
| 7 | HASH UNIQUE | | 1 | 98 | | 99 |00:00:00.01 | 3 | 2063K| 2063K| 1355K (0)|
| 8 | TABLE ACCESS FULL| T99 | 1 | 99 | 2 (0)| 99 |00:00:00.01 | 3 | | | |
----------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$2E82301F
2 - SET$1 / A@SEL$1
3 - SET$1
4 - SEL$2 / TB1@SEL$2
5 - SEL$3 / TB2@SEL$3
6 - SEL$8F9407EC / VW_SQ_1@SEL$04A8DF8C
7 - SEL$8F9407EC
8 - SEL$8F9407EC / B@SEL$4
839
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
