【Oracle】优化器选择提示“Hint”

文中使用的Oracle版本为10g。

关于Oracle的Hint大家有没有了解过呢？它不常用，但很有用。

“Hint”顾名思义就是暗示的意思，也就是说写这种语法会对SQL的操作起到“暗示”的作用。那我们究竟要暗示些什么？众所周知，Oracle数据库在执行SQL前会根据优化器选择执行计划。但有时候SQL并没有按照“合理”的方式执行（譬如明明有索引，它就没走索引。明明CBO(Cost Based Optimizer)更优，偏偏走了全表扫描），这个时候可以使用Hint方法给优化器一个提示，一般情况下如果优化器认为Hint给的方案更合理，就会根据Hint提出的方案执行。

PS：至于为什么可以通过Hint来优化查询效能，这个应该是跟时代背景有关。优化器会结合多个因素去判断该去执行那个执行计划，但是某些特殊情况下优化器是无法判断的，这个时候就需要使用Hint来处理了。或许不久的将来人工智能发展足够成熟了，Hint也就退出历史舞台了。

/*+ <<Hint内容>> */ 

Hint是一种特殊的注释，因此是使用“/*...*/”来括住内容的。而且这个Hint的写法一定要在SELECT、INSERT、DELETE、UPDATE和MERGE的关键字后面。

​

-------------------------------- 常用Hint（之前在项目中也用到） --------------------------------

-- 基于开销的优化方法，吞吐量最大化，资源消耗最小化
SELECT /*+ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

-- 基于开销的优化方法，响应时间最短，资源消耗最小化
SELECT /*+FIRST_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

-- 基于开销的优化方法，吞吐量最大化
SELECT /*+CHOOSE*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

-- 基于规则的优化方法
SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

-- 基于全表扫描方法
SELECT /*+FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO='SCOTT';

-- 显式指定根据ROWID进行查询
SELECT /*+ROWID(BSEMPMS)*/ * FROM BSEMPMS WHERE ROWID>='AAAAAAAAAAAAAA' AND EMP_NO='SCOTT';

-- 选择索引列扫描方法
SELECT /*+INDEX(BSEMPMS SEX_INDEX) */ FROM BSEMPMS WHERE SEX='M';

-- 选择索引升序的扫描方法
SELECT /*+INDEX_ASC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='SCOTT';

-- 选择索引降序的扫描方法
SELECT /*+INDEX_DESC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='SCOTT';

-- 明确命令优化器使用索引作为访问路径
SELECT /*+INDEX_JOIN(BSEMPMS SAL_HMI HIREDATE_BMI)*/ SAL,HIREDATEFROM BSEMPMS WHERE SAL<60000;

-- 快速全索引扫描（注意：不是全表扫描）
SELECT /*+INDEX_FFS(BSEMPMS IN_EMPNAM)*/ * FROM BSEMPMS WHERE DPT_NO='TEC305';

-- 直接给出执行规划选择，单索引联合扫描
SELECT /*+INDEX_FFS(BSEMPMS IN_DPTNO,IN_EMPNO,IN_SEX)*/ * FROM BSEMPMS WHERE EMP_NO='SCOTT' AND DPT_NO='TDC306';

-- 查询中的WHERE后面的OR条件进行转换为UNION ALL的组合查询
SELECT /*+USE_CONCAT*/ * FROM BSEMPMS WHERE DPT_NO='TDC506' AND SEX='M';

-- 直接插入到表的最后,可以提高速度
INSERT /*+APPEND*/ INTO TEST1 SELECT * FROM TEST4 ;

-- 插入语句生存期内停止并行模式来启动常规插入
INSERT /*+NOAPPEND*/ INTO test1 SELECT * FROM test4 ;

-------------------------------- 高级Hint用法 --------------------------------

-- 簇扫描方法，只对簇对象有效
SELECT /*+CLUSTER */ BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS WHERE DPT_NO='TEC304' AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

-- 位图索引的布尔组合方式
SELECT /*+INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI)*/ * FROM BSEMPMSWHERE SAL<5000000 AND HIREDATE;

-- 对于WHERE后面的OR 或者IN-LIST的查询语句,NO_EXPAND将阻止其基于优化器对其进行扩展
SELECT /*+NO_EXPAND*/ * FROM BSEMPMS WHERE DPT_NO='TDC506' AND SEX='M';

-- 能够对视图的各个查询进行相应的合并
SELECT /*+MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO 
FROM BSEMPMS A (
  SELET DPT_NO,AVG(SAL) AS AVG_SAL 
  FROM BSEMPMS B 
  GROUP BY DPT_NO
  ) V 
WHERE A.DPT_NO=V.DPT_NO AND A.SAL>V.AVG_SAL;

-- 对于有可合并的视图不再合并
SELECT /*+NO_MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO 
FROM BSEMPMS A (
   SELECT DPT_NO,AVG(SAL) AS AVG_SAL 
   FROM BSEMPMS B 
   GROUP BY DPT_NO
   ) V 
WHERE A.DPT_NO=V.DPT_NO AND A.SAL>V.AVG_SAL;

-- 根据表出现在FROM中的顺序,ORDERED使ORACLE依此顺序对其连接
SELECT /*+ORDERED*/ A.COL1,B.COL2,C.COL3 FROM TABLE1 A,TABLE2 B,TABLE3 C WHERE A.COL1=B.COL1 AND B.COL1=C.COL1;

-- 将指定表与嵌套的连接的行源进行连接,并把指定表作为内部表
SELECT /*+ORDERED USE_NL(BSEMPMS)*/ BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAM FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

-- 将指定的表与其他行源通过合并排序连接方式连接起来
SELECT /*+USE_MERGE(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

-- 表与其他行源通过哈希连接方式连接起来
SELECT /*+USE_HASH(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

-- 强制与ORACLE所选择的位置不同的表进行查询执行
SELECT /*+DRIVING_SITE(DEPT)*/ * FROM BSEMPMS,DEPT@BSDPTMS WHERE BSEMPMS.DPT_NO=DEPT.DPT_NO;

-- 全表扫描时,CACHE提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU的最近使用端
SELECT /*+FULL(BSEMPMS) CAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;

-- 全表扫描时,CACHE提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU的最近使用端
SELECT /*+FULL(BSEMPMS) NOCAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;

但话说回来，如果数据库涉及是没有问题的话一般情况下使用Hint的几率不大，使用之前先考虑一下是不是设计出了问题，从源头上解决可能会更好。