sql - 忽略Oracle DUP_VAL_ON_INDEX异常有多严重？

Question

我有一个表要记录，如果用户至少查看一次对象，因此:

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

这两个字段都不为空，并且一起构成主键。

我的问题是，由于我不在乎某人查看对象的次数(在第一次之后)，因此我有两种选择来处理插入。

执行SELECT count(*)...，如果未找到任何记录，则插入新记录。

始终只插入一条记录，并且如果它引发DUP_VAL_ON_INDEX异常(表明已经有这样的记录)，则将其忽略。

选择第二种选择的不利之处是什么？

更新:

我认为最好的表达方式是:“异常引起的开销是否比初始选择引起的开销更糟？”

Answer 1

我通常只插入并捕获DUP_VAL_ON_INDEX异常，因为这是最简单的编码。这比插入之前检查是否存在更为有效。我不认为这样做是“难闻的气味”(可怕的短语!)，因为我们处理的异常是Oracle提出的-这不像提出自己的异常作为流控制机制。

感谢Igor的评论，我现在在此上运行了两个不同的Benchamrk:(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;
/

结果:

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。