强制用HINT让查询走NESTED LOOP可以吗

我来答

2024-03-30

EXPLAIN PLAN SET STATEMENT_ID='TSH' FOR
select a.owner,a.object_id from v_t1t2 a where exists (select 1 from t99 b where a.object_id=b.object_id);
Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(statement_id=>'TSH'));

执行计划见附件，走的是HASH JOIN SEMI。现在我想改走NESTED LOOP，是不是只要加HINT如下：

select /*+ use_nl */ a.owner,a.object_id from v_t1t2 a where exists (select 1 from t99 b where a.object_id=b.object_id);

加完后重新运行EXPLANIE PLAN：

EXPLAIN PLAN SET STATEMENT_ID='TSH' FOR
select a.owner,a.object_id from v_t1t2 a where exists (select 1 from t99 b where a.object_id=b.object_id);

但发现执行计划仍未改变，请问如何解决？

我来答

添加附件

问题补充

9条回答

默认

最新

PiscesCanon

早点给出具体环境啊，根据你构造的环境，优化器很容易给你改写为内连接。

谓词推入即可，

12:15:27 SYS@xxxdb(927)> explain plan for select a.owner,a.object_id from v_t1t2 a where exists (select /*+ push_pred */ 1 from t99 b where a.object_id=b.object_id);

Explained.

Elapsed: 00:00:00.01
12:15:42 SYS@xxxdb(927)> select * from table (dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 152958765

-----------------------------------------------------------------------------------
| Id  | Operation               | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |         |   196 |  4704 |   219K  (1)| 00:43:51 |
|   1 |  NESTED LOOPS SEMI      |         |   196 |  4704 |   219K  (1)| 00:43:51 |
|   2 |   VIEW                  | V_T1T2  |   109K|  2347K|   444   (1)| 00:00:06 |
|   3 |    UNION-ALL            |         |       |       |            |          |
|   4 |     TABLE ACCESS FULL   | TB1     | 54625 |   800K|   222   (1)| 00:00:03 |
|   5 |     TABLE ACCESS FULL   | TB2     | 54626 |   800K|   222   (1)| 00:00:03 |
|   6 |   VIEW PUSHED PREDICATE | VW_SQ_1 |     1 |     2 |     2   (0)| 00:00:01 |
|*  7 |    TABLE ACCESS FULL    | T99     |     1 |     5 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   7 - filter("B"."OBJECT_ID"="A"."OBJECT_ID")

19 rows selected.

Elapsed: 00:00:00.01

更保险点可用下边的hint：

select /*+ no_merge(a) */ a.owner,a.object_id from v_t1t2 a where exists (select /*+ push_pred no_semi_to_inner unnest nl_sj */ 1 from t99 b where a.object_id=b.object_id);

有用 4

广州_老虎刘

2024-04-01

因为题主没有把问题描述清楚, 所以得到上面这个回复也正常. 我帮他补充一下吧, 他想得到的是t99做NL 驱动表, 把谓词推入到view的执行计划, 因为这样的执行计划才是效率最高的.

Thomas

有用 2

PiscesCanon

select a.owner,a.object_id from v_t1t2 a where exists (select /*+ nl_sj */ 1 from t99 b where a.object_id=b.object_id);

有用 1

Thomas

EXPLAIN PLAN SET STATEMENT_ID='TSH' FOR
select /*+ leading (t99,v_t1t2) use_nl(v_t1t2)*/
a.owner,a.object_id from v_t1t2 a where exists (select /*+ leading (t99,v_t1t2) use_nl(v_t1t2)*/ 1 from t99 b where a.object_id=b.object_id);

加了leading和hint, 但仍然不对，结果如下图

有用 1

Thomas

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 198 | 18216 | 853 (1)| 00:00:01 |
|* 1 | HASH JOIN | | 198 | 18216 | 853 (1)| 00:00:01 |
| 2 | VIEW | VW_SQ_1 | 99 | 1287 | 2 (0)| 00:00:01 |
| 3 | HASH UNIQUE | | 99 | 594 | | |
| 4 | TABLE ACCESS FULL| T99 | 99 | 594 | 2 (0)| 00:00:01 |
| 5 | VIEW | V_T1T2 | 199K| 15M| 849 (1)| 00:00:01 |
| 6 | UNION-ALL | | | | | |
| 7 | TABLE ACCESS FULL| TB1 | 99730 | 1168K| 425 (1)| 00:00:01 |
| 8 | TABLE ACCESS FULL| TB2 | 99731 | 1168K| 425 (1)| 00:00:01 |

仍然是HASH JOIN

有用 0

Thomas

原始语句如下，大家可以自己测试下

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 t1 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');

有用 0

Thomas

还是刘老师比较了解我的需求，神奇神奇。

有用 0

广州_老虎刘

2024-04-01

加hint调执行计划的目的是为了让SQL执行更高效, 你最终得到了NL的执行计划, 但是这个执行计划比不加hint的执行计划更差. 这个SQL, 靠hint是无法实现优化目的的.

Thomas

题主

2024-04-01

嗯，有空再琢磨下。

Thomas

谢谢HINT专家PiscesCanon和刘老师！

有用 0

PiscesCanon

又再次翻了起来，因为当时草草试了下推入谓词就出来NL了，还是存在点疑问的，此环境下NL不应该和谓词推入有强关联，昨天下午抽空又测试了下，下边还是分别列下几个情况（情况4存在点问题）：

原生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);

情况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);

Plan hash value: 4254236357

----------------------------------------------------------------------------------------------------------------------------------
| 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

有用 1

Thomas

题主

2024-04-11

非常感谢，给出这么详细的测试例子。

回答交流

提交

问题信息

请登录之后查看

邀请回答

暂无人订阅该标签，敬请期待~~

墨值悬赏