11g 新特性IGNORE_ROW_ON_DUPKEY_INDEX提示

11g中引入一些功能强大的hint提示，其中就包括了IGNORE_ROW_ON_DUPKEY_INDEX。其官方定义为: "The IGNORE_ROW_ON_DUPKEY_INDEX hint applies only to single-table INSERT operations. It is not supported for UPDATE, DELETE, MERGE, or multitable insert operations. IGNORE_ROW_ON_DUPKEY_INDEX  causes the statement to ignore a unique key violation for a specified set of columns or for a specified index. When a unique key violation is encountered, a row-level rollback occurs and execution resumes with the next input row. If you specify this hint when inserting data with DML error logging enabled, then the unique key violation is not logged and does not cause statement termination." 针对具有唯一性约束的键，若程序设计时没有考虑到插入具有重复键值的行会引发ORA-00001 unique constraint violated错误，进而可能导致程序过程终止的问题的话；直接修改程序将会是十分复杂的工程。所幸我们在11g中有了"IGNORE_ROW_ON_DUPKEY_INDEX"提示，在INSERT单表的语句中加入该hint可以让Oracle静默地(silently)忽略那些具有重复键值的插入行，而不触发ORA-00001错误，允许程序继续运行下去，这可以说是一种十分简便的折中方案。要在生产环境中使用该特性，我们有必要对比一下其同使用Exception处理违反唯一约束间的性能差别。

SQL> drop table youyus ;

Table dropped.

SQL> create table youyus (t1 int ,t2 varchar2(20),t3 varchar2(30)) tablespace users;

Table created

SQL> create unique index youyus_uk on youyus(t1) tablespace users;

Index created
/*清理现场，添加唯一约束索引*/

SQL> alter system set optimizer_dynamic_sampling=1;

System altered.

SQL> alter system flush shared_pool;

System altered.

SQL> alter system flush buffer_cache;

System altered.

/* 以下过程在0-30000的整数内随机取200000次值，可以确保尝试INSERT大量重复t1键值的行，
以便测试使用DUP_VAL_ON_INDEX Exception时的各项性能参数;此处以及之后我们都将commit置于loop循环外，
从而避免大量commit影响我们的实验结果*/

declare
  rnd int;
begin
  /* 使用exception处理重复键值插入违反约束的问题*/
  for i in 1 .. 200000 loop
    BEGIN
      select round(dbms_random.value * 30000) into rnd from dual;
      insert into youyus
        (t1, t2, t3)
      values
        (rnd, 'DUPLICATE', 'INSERT TEST');
    exception
      when DUP_VAL_ON_INDEX then
        continue;
    end;
  end loop;
  commit;
end;

SQL> select plsql_exec_time,cpu_time,elapsed_time,user_io_wait_time from v$sql where sql_text like 'declare%exception%';

PLSQL_EXEC_TIME   CPU_TIME ELAPSED_TIME USER_IO_WAIT_TIME
--------------- ---------- ------------ -----------------
        4392268   88296566     92345066            621020
/* 逝去时间92s,CPU时间为88s,PLSQL执行时间4s*/

SQL> select count(*) from youyus;

  COUNT(*)
----------
     29958

SQL> truncate table youyus;

Table truncated.

SQL> alter system flush shared_pool;

System altered.

SQL> alter system flush buffer_cache;

System altered.

/* 在插入前判断插入值是否违反唯一约束应当是一种不错的想法，不过写起来多少有些"麻烦"*/

declare
  dup_count int;
  rnd       int;
begin
   /* 使用插入前判断(check before insert)是否违反唯一约束的方式*/
  for i in 1 .. 200000 loop
    BEGIN
      select round(dbms_random.value * 30000) into rnd from dual;
      select count(*) into dup_count from youyus where t1 = rnd;
      IF (dup_count = 0) then
        insert into youyus
          (t1, t2, t3)
        values
          (rnd, 'DUPLICATE', 'INSERT TEST');
      END IF;
    END;
  END LOOP;
  commit;
end;

SQL> select plsql_exec_time,cpu_time,elapsed_time,user_io_wait_time  from v$sql where sql_text like 'declare%check%';

PLSQL_EXEC_TIME   CPU_TIME ELAPSED_TIME USER_IO_WAIT_TIME
--------------- ---------- ------------ -----------------
        2153769   15709301     18265730            679813

/* PLSQL执行时间缩短到2s,整个过程的CPU时间大幅减少到15s*/
/***  以上对比可以得出Exception处理是一种CPU敏感操作的结论  ***/

SQL> select count(*) from youyus;

  COUNT(*)
----------
     29968

SQL> truncate table youyus;

Table truncated.

SQL> alter system flush shared_pool;

System altered.

SQL> alter system flush buffer_cache;

System altered.

declare
  rnd int;
begin
/* 使用IGNORE_ROW_ON_DUPKEY_INDEX hint方式*/
  for i in 1 .. 200000 loop
    select round(dbms_random.value * 30000) into rnd from dual;
    insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX(YOUYUS,YOUYUS_UK) */
    into youyus
      (t1, t2, t3)
    values
      (rnd, 'DUPLICATE', 'INSERT TEST');
  end loop;
  commit;
end;

SQL> select plsql_exec_time,cpu_time,elapsed_time,user_io_wait_time  from v$sql where sql_text like 'declare%IGNORE%';

PLSQL_EXEC_TIME   CPU_TIME ELAPSED_TIME USER_IO_WAIT_TIME
--------------- ---------- ------------ -----------------
        2377262   78452903     84209306            623539

SQL> select count(*) from youyus;

  COUNT(*)
----------
     29959

/*** 
IGNORE_ROW_ON_DUPKEY_INDEX hint模式下，
CPU_TIME对比Exception模式时减少11%，但仍远高于插入前预检查模式;
就修改程序的复杂度而言IGNORE_ROW_ON_DUPKEY_INDEX模式要低于使用Exception模式,
而Exception模式又要低于CHECK_BEFORE_INSERT模式；CHECK_BEFORE_INSERT模式的CPU成本最低，但修改程序时的成本时间最高 
                                                                                                  ***/

/*需要注意的是IGNORE_ROW_ON_DUPKEY_INDEX提示与我们以往使用的hint略有不同，不正确使用它将导致报错*/

declare
  rnd int;
begin
/* 使用IGNORE_ROW_ON_DUPKEY_INDEX hint方式*/
  for i in 1 .. 200000 loop
    select round(dbms_random.value * 30000) into rnd from dual;
    insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX(YOUYUS,I_AM_ERROR) */
    into youyus
      (t1, t2, t3)
    values
      (rnd, 'DUPLICATE', 'INSERT TEST');
  end loop;
  commit;
end;
/
ERROR at line 1:
ORA-38913: Index specified in the index hint is invalid
ORA-06512: at line 7

上述三者各环节耗时图示: 总结一句，IGNORE_ROW_ON_DUPKEY_INDEX为lazy developer专备。