sql – 忽略Oracle DUP_VAL_ON_INDEX异常有多糟糕？

HasViewed
     ObjectID  number (FK to Object table)
     UserId    number (FK to Users table)

两个字段都不为空,并且一起形成主键.

我的问题是,由于我不在乎有人看过一个对象多少次(在第一个之后),我有两个处理插入的选项.

>执行SELECT count(*)…如果没有找到记录,请插入新记录.
>永远只是插入一个记录,如果它抛出一个DUP_VAL_ON_INDEX异常(表示已经有这样的记录),只要忽略它.

选择第二个选项的缺点是什么？

更新：

我猜想最好的办法是：“异常情况下的开销是否比初始选择造成的开销更糟？”

解决方法

感谢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.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!