我通常会插入并捕获 DUP_VAL_ON_INDEX 异常,因为这是最简单的编码。这比插入之前检查是否存在更有效。我不认为这样做是“坏味道”(可怕的短语!),因为我们处理的异常是由 Oracle 引发的 - 这不像引发您自己的异常作为流程控制机制。
感谢 Igor 的评论,我现在对此运行了两个不同的基准:(1) 除第一次之外的所有插入尝试都是重复的,(2) 所有插入都不重复。现实情况介于两种情况之间。
注:测试在 Oracle 10.2.0.3.0 上进行。
情况 1:大部分是重复的
似乎最有效的方法(通过一个重要因素)是在插入时检查是否存在:
prompt 1) Check DUP_VAL_ON_INDEX
begin
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 inserting
declare
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 inserting
begin
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_INDEX
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.54
2) Test if row exists before inserting
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.59
3) Test if row exists while inserting
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.20
情况2:没有重复项
prompt 1) Check DUP_VAL_ON_INDEX
begin
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 inserting
declare
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 inserting
begin
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;
/
Results:
1) Check DUP_VAL_ON_INDEX
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.15
2) Test if row exists before inserting
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.76
3) Test if row exists while inserting
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.71
在这种情况下,DUP_VAL_ON_INDEX 优势明显。请注意,“插入前选择”在这两种情况下都是最慢的。
因此,您似乎应该根据插入重复或不重复的相对可能性来选择选项 1 或 3。