【DB笔试面试573】在Oracle中，常用Hint有哪些？

2022-11-29 15:47:51

♣ 题目部分

在Oracle中，常用Hint有哪些？

♣ 答案部分

Oracle的Hint是用来提示Oracle的优化器，用来选择用户期望的执行计划。在许多情况下，Oracle默认的执行方式并不总是最优的，只不过由于平时操作的数据量比较小，所以，好的执行计划与差的执行计划所消耗的时间差异不大，用户感觉不到而已。但对于书写操作大数据量的SQL而言，其SQL的书写则需要先了解一下执行计划是否最优或满足生产需要。通常当从开发环境迁移到生产环境下时，往往会出现此类情况。

例如：假设有一张客户表，在客户类别上有索引。如果想查找某一类别用户，而该类别用户占总数的比例高达90%，那么此时采用全表扫描方式将会比索引扫描方式快。如果不使用Hint，那么Oracle很可能会选择使用索引方式来执行。

使用Hint可以实现以下功能：

（1）改变SQL中的表的关联顺序。

（2）改变SQL中的表的关联方式。

（3）实现并行方式执行DML、DDL以及SELECT语句。

（4）改变表的访问路径（数据读取方式）。

（5）调整查询转换类型，重写SQL。

（6）调整优化器优化目标。

（7）调整优化器类型。

Oracle推出了一个隐含参数“_OPTIMIZER_IGNORE_HINTS”，取值为TRUE或FALSE，缺省值是FALSE。Oracle可以通过将该隐含参数设置为TRUE，使得Oracle优化器忽略语句中所有的Hint。显然，Oracle提供此参数的目的就是在不修改应用的前提下，忽略所有Hint，让Oracle优化器自己来选择执行路径。

Hint的语法格式如下所示：

1{SELECT | INSERT | UPDATE | DELETE | MERGE} /*+ <具体的Hint内容>*／
2

关于Hint需要注意以下几点：

l Hint中第一个星号（*）和加号（+）之间不能有空格。

l Hint中加号（+）和具体的Hint内容之间可以有空格，也可以没有空格，但通常为了规范和区别于注释建议加上空格。

l Hint中的具体内容可以是单个Hint，也可以是多个Hint的组合，如果是后者，那么各个Hint间至少需要用1个空格来彼此分隔。

l Hint必须紧随关键字SELECT、INSERT、UPDATE、DELETE或MERGE之后。

l 如果在目标SQL中使用了Hint，那么就意味着自动启用了CBO，即Oracle会以CBO来解析含Hint的目标SQL。但是对于RULE和DRIVING_SITE来说，它们可以在RBO下使用，而且不自动启用CBO。

l Hint中指定具体对象时（比如指定表名或索引名），不能带上该对象所在SCHEMA的名称，即使该SQL文本中己经有对应的SCHEMA名称。

l Hint中指定具体表名时，如果该表在对应SQL文本中有别名，那么在Hint中应该使用该表的别名。

l 对于简单的SQL语句一般只有一个查询块（Query Block），那么在其上设置Hint其作用范围就是该语句块，而对于复杂的有多个查询语句的SQL语句（例如查询中用到了子查询、内联视图、集合等操作时），各个Hint的作用域是不同的。Hint生效的范围仅限于它本身所在的查询块，如果在Hint中不指定该Hint生效的查询块，那么Oracle会默认认为它生效的范围是指该Hint所处于的查询块。

l 由于各种原因导致Hint被Oracle忽略后，Oracle并不会给出任何提示或者警告，更不会报错，目标SQL依然可以正常执行。导致Hint失效的原因通常有：

①　使用的Hint有语法或者拼写错误。

②　使用的Hint是无效的（例如，在非等值连接中使用了USE_HASH）。

③　使用的Hint是自相矛盾的（例如，即指定了FULL又指定了INDEX_FFS），但Oracle只是将自相矛盾的Hint全部忽略掉，而组合Hint中的其它Hint依然有效。

④　使用的Hint受到了查询转换的干扰。

⑤　依据Hint执行的结果是错误的（例如在非空的索引列上计算行数）。

⑥　使用的Hint受到了保留关键字的干扰。Oracle在解析Hint的时候，从左到右进行，如果遇到一个词是Oracle关键字或保留字，那么Oracle将忽略这个词以及之后的所有词。如果遇到的一个词既不是关键字也不是Hint，那么就忽略该词。如果遇到的词是有效的Hint，那么就会保留该Hint。Oracle的保留字或者关键字可以通过视图V$RESERVED_WORDS来查询。由此可以知道下面5条SQL语句中只有1和4中的APPEND提示是起作用的。

11. INSERT /*+ APPEND,PARALLEL(T1) */ INTO T1 SELECT * FROM T2;
22. INSERT /*+ PARALLEL(T1), APPEND */ INTO T1 SELECT * FROM T2;
33. INSERT /*+ THIS IS APPEND */ INTO T1 SELECT * FROM T2;
44. INSERT /*+ THIS APPEND */ INTO T1 SELECT * FROM T2;
55. INSERT /*+ NOLOGGING APPEND */ INTO T1 SELECT * FROM T2;

因为“IS”是一个关键字，“,”（逗号）也是一个关键字，所以，上面的第2和第3条SQL，Oracle解析时，当遇到“,”和“IS”时，就忽略了后面的所有Hint。在第4条SQL中，THIS并不是一个关键字，所以，APPEND提示有效。为了避免这样的情况发生，当在SQL中书写Hint时，在/*+ */这种结构内只写Hint，而不要写逗号，或者是其它的注释。如果要对SQL写注释，那么可以在专门的注释结构中书写。

在SQL语句优化过程中，经常会用到Hint，通过在SQL语句中设置Hint从而影响SQL的执行计划，通过V$SQL_HINT视图可以查询所有的Hint，下表是一些常用的Hint：

表 3-19 常用Hint介绍

建议对上表中每个Hint都做相关的实验来深入学习，而本书不再详述。最后需要说明一下有关NOLOGGING的错误使用情况。下面几条SQL都是使用NOLOGGING时的错误用法：

1INSERT INTO T1 NOLOGGING;
2INSERT INTO T1 SELECT * FROM T2 NOLOGGING;
3INSERT /*+ NOLOGGING */ INTO T1 VALUES ('0');
4INSERT /*+ NOLOGGING */ INTO T1 SELECT * FROM T2;
5DELETE /*+ NOLOGGING */ FROM T1;
6UPDATE /*+ NOLOGGING */ T1 SET A='1';

实际上，上述所有的SQL没有一个能够实现“不产生”日志的数据更改操作。事实上，NOLOGGING并不是Oracle的一个有效的Hint，而是一个SQL关键字，通常用于DDL语句中。这里NOLOGGING相当于给SELECT的表指定了一个别名为“NOLOGGING”。下面是NOLOGGING的一些正确用法：

1CREATE TABLE T1 NOLOGGING AS SELECT * FROM T2;
2CREATE INDEX T1_IDX ON T1(A) NOLOGGING;
3ALTER INDEX T1_IDX REDUILD ONLINE NOLOGGING;
4ALTER TABLE T1 NOLOGGING;

若面试官问如何强制一个SQL语句使用索引，此时就可以回答使用Hint，/*+INDEX(TABLE INDEX_NAME)*/来完成。

& 说明：

有关Hint的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2125011/、http://blog.itpub.net/26736162/viewspace-2125709/