忽略 Oracle DUP_VAL_ON_INDEX 异常有多糟糕？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/350860/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How bad is ignoring Oracle DUP_VAL_ON_INDEX exception?
提问by James Curran
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.
这两个字段都不是 NULL，共同构成主键。
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.
我的问题是，因为我不在乎有人查看了多少次对象（在第一次之后），所以我有两种处理插入的选项。
- Do a SELECT count(*) ... and if no records are found, insert a new record.
- Always just insert a record, and if it throws a DUP_VAL_ON_INDEX exceptions (indicating that there already was such a record), just ignore it.
- 执行 SELECT count(*) ... 如果没有找到记录，则插入一条新记录。
- 总是只插入一条记录，如果它抛出 DUP_VAL_ON_INDEX 异常（表明已经有这样的记录），只需忽略它。
What's the downside of choosing the second option?
选择第二个选项的缺点是什么？
UPDATE:
更新：
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?"
我想最好的说法是：“异常引起的开销是否比初始选择引起的开销更糟？”
回答by Tony Andrews
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.
我通常只会插入并捕获 DUP_VAL_ON_INDEX 异常，因为这是最简单的编码。这比插入前检查是否存在更有效。我不认为这样做是“难闻的”（可怕的短语！），因为我们处理的异常是由 Oracle 引发的 - 这不像将您自己的异常作为一种流程控制机制引发。
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.
感谢 Igor 的评论，我现在对此运行了两个不同的 benchamrks：（1）除了第一个之外的所有插入尝试都是重复的，（2）所有插入都不是重复的。现实将介于这两种情况之间。
Note: tests performed on Oracle 10.2.0.3.0.
注意：在 Oracle 10.2.0.3.0 上执行的测试。
Case 1: Mostly duplicates
案例 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
Case 2: no duplicates
案例 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;
/
Results:
结果：
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
In this case DUP_VAL_ON_INDEX wins by a mile. Note the "select before insert" is the slowest in both cases.
在这种情况下，DUP_VAL_ON_INDEX 领先一英里。请注意，“插入前选择”在这两种情况下都是最慢的。
So it appears that you should choose option 1 or 3 according to the relative likelihood of inserts being or not being duplicates.
因此，您似乎应该根据插入重复或不重复的相对可能性来选择选项 1 或 3。
回答by kurosch
I don't think there is a downside to your second option. I think it's a perfectly valid use of the named exception, plus it avoids the lookup overhead.
我不认为你的第二个选择有缺点。我认为这是对命名异常的完全有效的使用，而且它避免了查找开销。
回答by EvilTeach
Try this?
尝试这个？
SELECT 1
FROM TABLE
WHERE OBJECTID = 'PRON_172.JPG' AND
USERID='JCURRAN'
It should return 1, if there is one there, otherwise NULL.
如果有，它应该返回 1，否则返回 NULL。
In your case, it looks safe to ignore, but for performance, one should avoid exceptions on the common path. A question to ask, "How common will the exceptions be?" Few enough to ignore? or so many another method should be used?
在您的情况下，忽略似乎是安全的，但为了性能，应该避免公共路径上的异常。一个要问的问题，“例外情况有多普遍？” 少到可以无视？或者应该使用许多其他方法？
回答by rob
Usually, exception handling is slower; however if it would happen only seldom, then you would avoid the overhead of the query.
I think it mainly depends on the frequency of the exception, but if performance is important, I would suggest some benchmarking with both approaches.
通常，异常处理较慢；但是，如果它很少发生，那么您将避免查询的开销。
我认为这主要取决于异常的频率，但如果性能很重要，我会建议对这两种方法进行一些基准测试。
Generally speaking, treating common events as exception is a bad smell; for this reason you could see also from another point of view.
If it is an exception, then it should be treated as an exception - and your approach is correct.
If it is a common event, then you should try to explicitly handle it - and then checking if the record is already inserted.
一般来说，将普通事件视为异常是一种糟糕的气味；出于这个原因，你也可以从另一个角度来看。
如果是异常，则应将其视为异常 - 您的方法是正确的。
如果它是一个常见事件，那么您应该尝试显式处理它 - 然后检查记录是否已经插入。
回答by user961954
IMHO it is best to go with Option 2: Other than what is already been said, you should consider thread safety. If you go with option 1 and If multiple threads are executing your PL/SQL block then its possible that two or more threads fire select at the same time and at that time there is no record, this will end up leading all threads to insert and you will get unique constraint error.
恕我直言，最好选择选项 2：除了已经说过的内容，您应该考虑线程安全。如果您使用选项 1 并且如果多个线程正在执行您的 PL/SQL 块，那么可能有两个或多个线程同时触发 select 并且当时没有记录，这最终将导致所有线程插入和你会得到唯一的约束错误。
