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

  • 2019 年 10 月 10 日
  • 笔记

题目部分

在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的语法格式如下所示:

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

关于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提示是起作用的。

1. INSERT /*+ APPEND,PARALLEL(T1) */ INTO T1 SELECT * FROM T2;  2. INSERT /*+ PARALLEL(T1), APPEND */ INTO T1 SELECT * FROM T2;  3. INSERT /*+ THIS IS APPEND */ INTO T1 SELECT * FROM T2;  4. INSERT /*+ THIS APPEND */ INTO T1 SELECT * FROM T2;  5. 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时的错误用法:

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

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

CREATE TABLE T1 NOLOGGING AS SELECT * FROM T2;  CREATE INDEX T1_IDX ON T1(A) NOLOGGING;  ALTER INDEX T1_IDX REDUILD ONLINE NOLOGGING;  ALTER 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/

本文选自《Oracle程序员面试笔试宝典》,作者:李华荣。