Hint的常见错误使用方式
提起Oracle数据库的Hint,几乎每一个DBA都知道这一强大工具。在Oracle中,Hint可以用来改变SQL的执行计划、固定SQL的执行计划。Oracle数据库内部的很多特性也依赖于Hint,比如Outline、Profile等。
但是在日常工作中,很多开发人员或DBA,对Hint的使用仍然存在一些错误的方式。下面将列举主要的2种。(本文不讨论Hint的滥用即过度使用问题)。
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 hint,对于索引,仍然会产生日志,也就是说append hint对索引是没有效果的。
另外,DDL中使用的nologging关键字和inset语句中使用的append hint,并不是说完全不产生日志,只是对表的数据块的数据部分的更改不会有日志产生,但是SQL执行过程中数据字典的更改、空间分配等递归SQL、段头和位图块的更改、将数据块标记为unrecoverable等仍然会产生少量日志。
2. Hint的不正确写法。
这是一个比较不容易发现的问题。下面几条SQL,哪一条SQL的append hint会生效:
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;
要回答这个问题,请先看下面的测试(测试环境: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) */
--end end.
建议继续学习:
扫一扫订阅我的微信号:IT技术博客大学习
- 作者:老熊 来源: 老熊的三分地-Oracle、UNIX、数据恢复
- 标签: Hint
- 发布时间:2012-04-09 13:42:24
- [46] 界面设计速成
- [43] 视觉调整-设计师 vs. 逻辑
- [43] Oracle MTS模式下 进程地址与会话信
- [42] IOS安全–浅谈关于IOS加固的几种方法
- [42] android 开发入门
- [39] 图书馆的世界纪录
- [38] 程序员技术练级攻略
- [38] 【社会化设计】自我(self)部分――欢迎区
- [38] 如何拿下简短的域名
- [34] 读书笔记-壹百度:百度十年千倍的29条法则