忽略Oracle DUP_VAL_ON_INDEX异常有多严重？

忽略Oracle DUP_VAL_ON_INDEX异常有多严重？

我通常只插入并捕获DUP_VAL_ON_INDEX异常，因为这是最简单的编码。这比插入之前检查是否存在更为有效。我不认为这样做是“难闻的气味”（可怕的短语！），因为我们处理的异常是Oracle提出的-
这不像提出自己的异常作为流控制机制。

感谢Igor的评论，我现在在此方面运行了两个不同的Benchamrk：（1）除第一次插入尝试之外的所有插入尝试都是重复的；（2）所有插入都不是重复的尝试。现实将介于这两种情况之间。

注意：在Oracle 10.2.0.3.0上执行的测试。

情况1：大部分重复

似乎（最重要的因素）最有效的方法是在插入时检查是否存在：

prompt 1) Check DUP_VAL_ON_INDEXbegin   for i in 1..1000 loop      begin         insert into hasviewed values(7782,20);      exception         when dup_val_on_index then null;      end;   end loop   rollback;end;/prompt 2) Test if row exists before insertingdeclare   dummy integer;begin   for i in 1..1000 loop      select count(*) into dummy      from hasviewed      where objectid=7782 and userid=20;      if dummy = 0 then         insert into hasviewed values(7782,20);      end if;   end loop;   rollback;end;/prompt 3) Test if row exists while insertingbegin   for i in 1..1000 loop      insert into hasviewed      select 7782,20 from dual      where not exists (select null  from hasviewed  where objectid=7782 and userid=20);   end loop;   rollback;end;/

结果（运行一次以避免解析开销之后）：

1) Check DUP_VAL_ON_INDEXPL/SQL procedure successfully completed.Elapsed: 00:00:00.542) Test if row exists before insertingPL/SQL procedure successfully completed.Elapsed: 00:00:00.593) Test if row exists while insertingPL/SQL procedure successfully completed.Elapsed: 00:00:00.20

情况2：无重复

prompt 1) Check DUP_VAL_ON_INDEXbegin   for i in 1..1000 loop      begin         insert into hasviewed values(7782,i);      exception         when dup_val_on_index then null;      end;   end loop   rollback;end;/prompt 2) Test if row exists before insertingdeclare   dummy integer;begin   for i in 1..1000 loop      select count(*) into dummy      from hasviewed      where objectid=7782 and userid=i;      if dummy = 0 then         insert into hasviewed values(7782,i);      end if;   end loop;   rollback;end;/prompt 3) Test if row exists while insertingbegin   for i in 1..1000 loop      insert into hasviewed      select 7782,i from dual      where not exists (select null  from hasviewed  where objectid=7782 and userid=i);   end loop;   rollback;end;/

结果：

1) Check DUP_VAL_ON_INDEXPL/SQL procedure successfully completed.Elapsed: 00:00:00.152) Test if row exists before insertingPL/SQL procedure successfully completed.Elapsed: 00:00:00.763) Test if row exists while insertingPL/SQL procedure successfully completed.Elapsed: 00:00:00.71

在这种情况下，DUP_VAL_ON_INDEX赢得一英里。请注意，在两种情况下，“插入前选择”是最慢的。

因此，您似乎应该根据插入重复或不重复的相对可能性来选择选项1或3。