1

Hint的常见错误使用方式

老熊 2019-03-23
94
摘要:提起Oracle数据库的Hint,几乎每一个DBA都知道这一强大工具。在Oracle中,Hint可以用来改变SQL的执行计划、固定SQL的...

问题描述

提起Oracle数据库的Hint,几乎每一个DBA都知道这一强大工具。在Oracle中,Hint可以用来改变SQL的执行计划、固定SQL的执行计划。Oracle数据库内部的很多特性也依赖于Hint,比如Outline、Profile等。

但是在日常工作中,很多开发人员或DBA,对Hint的使用仍然存在一些错误的方式。下面将列举主要的2种。

专家解答

1. NOLOGGING的不正确使用。

很多人知道,在进行数据处理时,如果不产生日志或只产生少量的日志,将会有明显的、甚至是巨大的效率提升。下面有几条不同的SQL:

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没有一个能够实现“不产生”日志的数据更改操作。第1-2条SQL语句虽然没有将NOLOGGING写为Hint的形式,但是也是很多人的错误写法,一并列在此处。事实上,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中,最后一条SQL只是将表的LOGGING属性改为"NO"。而之前的几条SQL能够有效地减少DDL操作时减少的日志量。

在DML操作中,只有下面一种方式能够在大数据量时仍然只会产生极少量的日志:

INSERT /*+ APPEND */ INTO T1 SELECT * FROM T2;

也就是使用append hint。但是这个hint要达到目的,需要以下几个条件:

使用INSERT /*+ APPEND */ INTO .. SELECT .. FROM形式的INSERT SQL。

如果是在归档模式下,需要将表的LOGGING属性置为NO。

表空间或数据库的FORCE LOGGING属性为NO。注意在非归档模式下也是可以设置FORCE LOGGING的。

这里提到的insert语句中的append hint,对于索引,仍然会产生日志,也就是说append hint对索引是没有效果的。
另外,DDL中使用的nologging关键字和inset语句中使用的append hint,并不是说完全不产生日志,只是对表的数据块的数据部分的更改不会有日志产生,但是SQL执行过程中数据字典的更改、空间分配等递归SQL、段头和位图块的更改、将数据块标记为unrecoverable等仍然会产生少量日志。

2. Hint的不正确写法。

这是一个比较不容易发现的问题。下面几条SQL,哪一条SQL的append hint会生效:

INSERT /*+ append,parallel(t1) */ INTO T1 SELECT * FROM T2;  
INSERT /*+ parallel(t1), append */ INTO T1 SELECT * FROM T2;  
INSERT /*+ this is append */ INTO T1 SELECT * FROM T2;  
INSERT /*+ this append */ INTO T1 SELECT * FROM T2;

要回答这个问题,请先看下面的测试(测试环境:10.2.0.1 for Windows):

SQL> INSERT /*+ append,parallel(t1) */ INTO T1 SELECT * FROM T2;  
已创建55640行。  
统计信息  
----------------------------------------------------------  
      12304  redo size  
SQL> COMMIT;  
  
SQL> INSERT /*+ parallel(t1), append */ INTO T1 SELECT * FROM T2;  
已创建55640行。  
统计信息  
----------------------------------------------------------  
    5739584  redo size  
SQL> COMMIT;  
  
SQL> INSERT /*+ this is append */ INTO T1 SELECT * FROM T2;  
已创建55640行。  
统计信息  
----------------------------------------------------------  
    5746604  redo size  
SQL> COMMIT;  
  
SQL> INSERT /*+ this append */ INTO T1 SELECT * FROM T2;  
已创建55640行。  
统计信息  
----------------------------------------------------------  
      12052  redo size  
SQL> COMMIT;

从上面的输出可以看到,通过insert语句执行产生的redo size判断,4条SQL语句中,1和4这2条SQL中的append hint起了作用,而2和3这2条SQL中的append hint没有起作用。我们看看第1和第2条SQL,只不过是parallel和append换了个位置,结果就截然不同;而第3和第4条SQL,只是一个多了"is"这个词,另一个没有,其结果也完全不同。这里有什么玄机吗?

这里就需要了解Oracle在解析SQL时,是怎样解析hint的。
Oracle在解析hint,从左到右进行,如果遇到一个词是oracle关键字或者说是保留字,将忽略这个词以及之后的所有词。如果遇到的一个词即不是关键字也不是hint,就忽略该词。如果遇到的一个词是有效的hint,那么就会保留该hint。

Oracle的保留字或者说是关键词(虽然二者在意义不一样,但这里不将其区分),可以通过视图v$reserved_words来查询。"is"正是一个关键词,甚至连","(逗号)也是一个关键词。这样,上面的第2和第3条SQL,Oracle解析时当遇到","和"is"时,就忽略了后面的所有hint。在第4条SQL中,this并不是一个关键词,所以append hint有效。基于这个原理,下面的一条SQL中的hint也是不起作用的:

INSERT /*+ NOLOGGING APPEND */ INTO T1 SELECT * FROM T2;

在9.2.0.8和11.2.0.2这2个版本下进行同样的测试,结果完全一样。
为了避免这样的情况,在SQL中书写hint时,在/*+ */和--+这2种结构内只写hint,而不要写逗号,或者是其他的注释。如果要对SQL写注释,在专门的注释结构中写入。比如/* test comment */。如果与hint混写注释,虽然当时没有关键词在里面,但随着版本升级,很可能会加入新的关键词。

另外,一些很常见的hint形式,比如/*+ parallel(t,8) */,/*+ index(t,t_idx) */,虽然当前没有问题,但标准的写法应该是:
/*+ parallel(t 8) */,/*+ index(t t_idx) */

「喜欢文章,快来给作者赞赏墨值吧」

评论

0
1
最新发布
暂无内容,敬请期待...
数据库资讯
最新 热门 更多
本月热门
近期活动
全部
暂无活动,敬请期待...
相关课程
全部
暂无课程,敬请期待...