我通常只插入并捕获DUP_VAL_ON_INDEX异常,因为这是最简单的编码。这比插入之前检查是否存在更为有效。我不认为这样做是“难闻的气味”(可怕的短语!),因为我们处理的异常是Oracle提出的- 这不像提出自己的异常作为流控制机制。
感谢Igor的评论,我现在在此方面运行了两个不同的Benchamrk:(1)除第一次插入尝试之外的所有插入尝试都是重复的;(2)所有插入都不是重复的尝试。现实将介于这两种情况之间。
注意:在Oracle 10.2.0.3.0上执行的测试。
似乎(最重要的因素)最有效的方法是在插入时检查是否存在:
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
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;
/
结果:
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。