Oracle关于半连接SQL执行计划的执行路径一些有趣的实验
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
