如果用户至少查看过一个对象,我有一个桌面,我正在录制,因此：
HasViewed
ObjectID number (FK to Object table)
UserId number (FK to Users table)
两个字段都不为空,并且一起形成主键.
我的问题是,由于我不在乎有人看过一个对象多少次(在第一个之后),我有两个处理插入的选项.
>执行SELECT count(*)…如果没有找到记录,请插入新记录.
>永远只是插入一个记录,如果它抛出一个DUP_VAL_ON_INDEX异常(表示已经有这样的记录),只要忽略它.
选择第二个选项的缺点是什么？
更新：
我猜想最好的办法是：“异常情况下的开销是否比初始选择造成的开销更糟？”
解决方法
通常我会插入和捕获DUP_VAL_ON_INDEX异常,因为这是最简单的代码.这比插入之前检查存在更有效.我不认为这是一个“糟糕的气味”(可怕的短语！),因为我们处理的异常是由Oracle引发的 – 它不像将自己的异常作为一个流程控制机制.
感谢Igor的评论,我现在运行两个不同的benchamrks：(1)除了第一个以外的所有插入尝试都是重复的,(2)所有插入不是重复的.两种情况之间的现实情况.
注意：对Oracle 10.2.0.3.0执行的测试.
案例1：大部分是重复的
看来,最有效的方法(通过一个重要因素)是检查WHILE插入的存在：
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;
/
结果：
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.
