How bad is ignoring Oracle DUP_VAL_ON_INDEX exception?

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.

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.

Note: tests performed on Oracle 10.2.0.3.0.

Case 1: Mostly duplicates

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

prompt 1) Check DUP_VAL_ON_INDEXbegin   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 insertingdeclare   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 insertingbegin   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_INDEXPL/SQL procedure successfully completed.Elapsed: 00:00:00.542) Test if row exists before insertingPL/SQL procedure successfully completed.Elapsed: 00:00:00.593) Test if row exists while insertingPL/SQL procedure successfully completed.Elapsed: 00:00:00.20

Case 2: no duplicates

prompt 1) Check DUP_VAL_ON_INDEXbegin   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 insertingdeclare   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 insertingbegin   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_INDEXPL/SQL procedure successfully completed.Elapsed: 00:00:00.152) Test if row exists before insertingPL/SQL procedure successfully completed.Elapsed: 00:00:00.763) Test if row exists while insertingPL/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.

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

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.

Try this?

SELECT 1FROM TABLEWHERE OBJECTID = 'PRON_172.JPG' AND      USERID='JCURRAN'

It should return 1, if there is one there, otherwise 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?