技术头条 - 一个快速在微博传播文章的方式     搜索本站
您现在的位置首页 --> Oracle --> Hint的常见错误使用方式

Hint的常见错误使用方式

浏览:1323次  出处信息

    提起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 */ 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会生效:

    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.

    建议继续学习:

    1. hint指定index的深入理解    (阅读:1722)
    QQ技术交流群:445447336,欢迎加入!
    扫一扫订阅我的微信号:IT技术博客大学习
    © 2009 - 2024 by blogread.cn 微博:@IT技术博客大学习

    京ICP备15002552号-1