我通常只插入并捕获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。
