往期回顾：

左右SQL执行计划妙招 ①——子查询的应用范围

引言

在上一篇文章中，我们深入探讨了子查询中应用HINT的三种方法，包括查询块内应用、全局别名引用以及qb_name定义方式。

本篇将继续这个主题，探究一个常见却令人困惑的问题：为什么精心设置的HINT有时会失效？

很多DBA和开发人员都曾遇到这样的情况：我们根据分析添加了看似完美的HINT，却发现执行计划完全没有变化，好像Oracle优化器完全忽略了我们的建议。这不仅让人沮丧，更会影响性能优化的效果和信心。

2 . HINT无效原因

HINT在使用过程中时常会遇到无法生效的情况，这时我们要冷静分析判断，一般来说，就是算法无法支持、Hint有矛盾、根据Hint的结果执行会错、书写语法错这几个原因。

（1）算法没能够支持

环境准备：

DROP TABLE t1 CASCADE CONSTRAINTS PURGE; 
DROP TABLE t2 CASCADE CONSTRAINTS PURGE; 
CREATE TABLE t1 (
     id NUMBER NOT NULL,
     n NUMBER,
     contents VARCHAR2(4000)
   ); 
CREATE TABLE t2 (
     id NUMBER NOT NULL,
     t1_id NUMBER NOT NULL,
     n NUMBER,
     contents VARCHAR2(4000)
   ); 
execute dbms_random.seed(0); 
INSERT INTO t1
     SELECT  rownum,  rownum, dbms_random.string('a', 50)
       FROM dual
     CONNECT BY level <= 100
      ORDER BY dbms_random.random; 
INSERT INTO t2 SELECT rownum, rownum, rownum, dbms_random.string('b', 50) FROM dual CONNECT BY level <= 100000
    ORDER BY dbms_random.random; 
COMMIT;

语句1，使用use_hash的Hint希望能走Hash连接，结果实际是NL连接，因为Hash连接不支持连接条件是t1.id > t2.t1_id 这样不等的写法，如下：

set linesize 1000
set autotrace traceonly explain
--语句1
SELECT /*+ leading(t1) use_hash(t2)*/ *
  FROM t1, t2
  WHERE t1.id > t2.t1_id
  AND t1.n = 19;
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    50 |  6150 |   276   (1)| 00:00:04 |
|   1 |  NESTED LOOPS      |      |    50 |  6150 |   276   (1)| 00:00:04 |
|*  2 |   TABLE ACCESS FULL| T1   |     1 |    57 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T2   |    50 |  3300 |   273   (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------------------------------
   2 - filter("T1"."N"=19)
   3 - filter("T1"."ID">"T2"."T1_ID")

语句2的连接条件是Like，同样只能适用于NL，而不能适用于其他，这里试验use_merge，一样以失败告终：

SELECT /*+ leading(t1) use_merge(t2)*/ *
  FROM t1, t2
  WHERE t1.id like t2.t1_id
  AND t1.n = 19;    
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  5000 |   600K|   276   (1)| 00:00:04 |
|   1 |  NESTED LOOPS      |      |  5000 |   600K|   276   (1)| 00:00:04 |
|*  2 |   TABLE ACCESS FULL| T1   |     1 |    57 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T2   |  5000 |   322K|   273   (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------------------------------
   2 - filter("T1"."N"=19)
   3 - filter(TO_CHAR("T1"."ID") LIKE TO_CHAR("T2"."T1_ID"))

（2）组合Hint有矛盾

我们接上面的例子，继续运行一个SQL语句，如下：

Set linesize 1000
set pagesize 2000
alter session set statistics_level=all ;
SELECT /*+ leading(t2) use_nl(t2) */ *
FROM t1, t2
WHERE t1.id = t2.t1_id;
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |    100 |00:00:00.11 |    1019 |
|*  1 |  HASH JOIN         |      |      1 |    100 |    100 |00:00:00.11 |    1019 |
|   2 |   TABLE ACCESS FULL| T2   |      1 |  98778 |    100K|00:00:00.02 |    1005 |
|   3 |   TABLE ACCESS FULL| T1   |      1 |    100 |    100 |00:00:00.01 |      14 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
-------------------------------------------------------------------------------------
   1 - access("T1"."ID"="T2"."T1_ID")
Note
-----
   - dynamic sampling used for this statement (level=2)

已选择25行。

这里我们发现，我们明明使用use_nl的Hint，走的却是Hash join，这是为啥呢？因为这里use_nl(t2)表示t2被驱动，也就是t2表后访问，而leading(t2)却告诉我们这是表示t2表要前驱，先访问。这不是矛盾吗？所以这个hint失效了。

（3）依据Hint结果执行会错

请看如下语句，使用index(t,idx_object_id)希望语句强制走索引，结果如何呢？请看：

drop table t purge;
create table t as select * from dba_objects;
create index idx_object_id on t(object_id);
set linesize 1000
set pagesize 2000
explain plan for
select /*+index(t,idx_object_id)*/ count(*) from t;
select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   292   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    | 74811 |   292   (1)| 00:00:04 |
-------------------------------------------------------------------

发现依然走的是全表扫描，Hint失效了。真正原因是，如果走索引，那就要依赖索引来回答条数的问题。这里有巨大风险，因为索引不存储空值，而索引列并没有保证非空，这里的值会不正确。

（4）书写出现了错误

如果SQL的表有别名，必须用别名而不能用原表名，否则无法生效，这点要牢记。比如如下：

drop table test purge;
create table test as select * from dba_objects;
create index idx_test_objid on test(object_id);
set linesize 1000
set pagesize 2000
explain plan for
select /*+index(test,idx_test_objid)*/ * from test t where object_id>0;
select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 67742 |    13M|   293   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| TEST | 67742 |    13M|   293   (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------------------------------
   1 - filter("OBJECT_ID">0)
Note
----
   - dynamic sampling used for this statement (level=2)

很显然，这里如果是如下两种写法都不会出现问题：要么用别名t，要么就干脆这个SQL语句本身就没有别名。

select /*+index(t,idx_test_objid)*/ * from test t where object_id>0;
select /*+index(test,idx_test_objid)*/ * from test  where object_id>0;

当然，还有的是把index(t,t_idx) 不小心写成了 indexx(t,t_idx)，把/*+full(t)*/ 写成了/*full(t)*+/ 等诸如此类的低级错误，一定要细心哦，这里就无须举例说明了。

最佳实践与总结

通过以上案例分析，我们可以总结出以下使HINT生效的最佳实践：

 了解算法限制：不同的连接方法适用于不同类型的连接条件，使用前需了解这些限制。

 - Hash Join适用于等值连接条件

 - Merge Join适用于等值连接且需要排序

 - Nested Loop适用范围最广，包括非等值连接

 - 避免矛盾HINT：确保多个HINT之间逻辑一致，特别是表访问顺序和连接方法。

 - 考虑正确性影响：某些HINT可能会影响结果的正确性，优化器会忽略这类HINT。

 正确使用语法：

 - 使用正确的表别名

 - 检查HINT的拼写和格式

 - 确保HINT位置正确

 - 验证HINT生效：每次添加HINT后，检查执行计划确认其是否生效。

掌握这些原因和最佳实践，将大大提高你控制执行计划的成功率，为SQL性能优化打下坚实基础。

未完待续..

左右SQL执行计划妙招③ ——执行计划SQL写法差异改变【with子句】

公众号：收获不止数据库

微信：ljbyxl1213

• “大白话人工智能” 系列

• “数据库拍案惊奇” 系列

• “世事洞明皆学问” 系列