如果用户至少查看过一个对象,我有一个桌面,我正在录制,因此：
@H_403_2@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插入的存在：
@H_403_2@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; /结果(运行一次以避免解析开销)：
@H_403_2@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：没有重复
@H_403_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; /结果：
@H_403_2@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.
