忽略Oracle DUP_VAL_ON_INDEX异常有多严重? [英] How bad is ignoring Oracle DUP_VAL_ON_INDEX exception?

问题描述

如果用户至少浏览过一次对象，我有一个正在记录的表，因此:

I have a table where I'm recording if a user has viewed an object at least once, hence:

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

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

Both fields are NOT NULL and together form the Primary Key.

我的问题是，由于我不在乎某人查看过多少次对象(在第一次之后)，因此我有两个选项来处理插入内容.

My question is, since I don't care how many times someone has viewed an object (after the first), I have two options for handling inserts.

• 执行SELECT count(*)...，如果未找到任何记录，则插入新记录.
• 总是只插入一条记录，如果它抛出DUP_VAL_ON_INDEX异常(表明已经有这样一条记录)，则将其忽略.

选择第二个选项有何弊端?

What's the downside of choosing the second option?

更新:

我猜最好的表达方式是:异常引起的开销是否比初始选择引起的开销更严重?"

I guess the best way to put it is : "Is the overhead caused by the exception worse than the overhead caused by the initial select?"

推荐答案

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

I would normally just insert and trap the DUP_VAL_ON_INDEX exception, as this is the simplest to code. This is more efficient than checking for existence before inserting. I don't consider doing this a "bad smell" (horrible phrase!) because the exception we handle is raised by Oracle - it's not like raising your own exceptions as a flow-control mechanism.

由于Igor的评论，我现在对此进行了两种不同的测试:(1)除第一次插入外，所有插入尝试均重复，(2)所有插入均不重复.现实将介于这两种情况之间.

Thanks to Igor's comment I have now run two different benchamrks on this: (1) where all insert attempts except the first are duplicates, (2) where all inserts are not duplicates. Reality will lie somewhere between the two cases.

注意:在Oracle 10.2.0.3.0上执行的测试.

Note: tests performed on Oracle 10.2.0.3.0.

案例1:大多数重复

看来(最重要的因素)最有效的方法是在插入时检查是否存在:

It seems that the most efficient approach (by a significant factor) is to check for existence WHILE inserting:

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;
/

结果(运行一次以避免分析开销后):

Results (after running once to avoid parsing overheads):

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英里.请注意，在两种情况下，插入前选择"是最慢的.

In this case DUP_VAL_ON_INDEX wins by a mile. Note the "select before insert" is the slowest in both cases.

因此，您似乎应该根据插入物是否重复的相对可能性选择选项1或3.

So it appears that you should choose option 1 or 3 according to the relative likelihood of inserts being or not being duplicates.

这篇关于忽略Oracle DUP_VAL_ON_INDEX异常有多严重?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！