sql - example - raise_application_error
¿Qué tan grave es ignorar la excepción Oracle DUP_VAL_ON_INDEX? (5)
Tengo una tabla donde estoy grabando si un usuario ha visto un objeto al menos una vez, por lo tanto:
HasViewed
ObjectID number (FK to Object table)
UserId number (FK to Users table)
Ambos campos NO son NULL y juntos forman la clave principal.
Mi pregunta es, dado que no me importa cuántas veces alguien haya visto un objeto (después del primero), tengo dos opciones para manejar las inserciones.
- Haga un conteo SELECCIONADO (*) ... y si no se encuentran registros, inserte un nuevo registro.
- Siempre inserte un registro, y si arroja un DUP_VAL_ON_INDEX excepciones (lo que indica que ya existía ese registro), simplemente ignórelo.
¿Cuál es el inconveniente de elegir la segunda opción?
ACTUALIZAR:
Supongo que la mejor manera de decirlo es: "¿La sobrecarga causada por la excepción es peor que la sobrecarga causada por la selección inicial?"
¿Prueba esto?
SELECT 1
FROM TABLE
WHERE OBJECTID = ''PRON_172.JPG'' AND
USERID=''JCURRAN''
Debería devolver 1, si hay uno allí, de lo contrario NULL.
En su caso, parece seguro ignorarlo, pero para el rendimiento, uno debe evitar excepciones en el camino común. Una pregunta para preguntar: "¿Cuán comunes serán las excepciones?" ¿Pocos lo suficiente como para ignorarlo? o tantos otros deberían ser usados?
No creo que haya un inconveniente en tu segunda opción. Creo que es un uso perfectamente válido de la excepción nombrada, además evita la sobrecarga de búsqueda.
Por lo general, el manejo de excepciones es más lento; sin embargo, si solo ocurre pocas veces, entonces evitaría la sobrecarga de la consulta.
Creo que depende principalmente de la frecuencia de la excepción, pero si el rendimiento es importante, sugeriría una evaluación comparativa con ambos enfoques.
En términos generales, tratar eventos comunes como una excepción es un mal olor; por esta razón, puedes ver también desde otro punto de vista.
Si es una excepción, debe tratarse como una excepción, y su enfoque es correcto.
Si se trata de un evento común, entonces debe intentar manejarlo explícitamente y luego verificar si el registro ya está insertado.
Normalmente solo insertaría y atraparía la excepción DUP_VAL_ON_INDEX, ya que es el más simple de codificar. Esto es más eficiente que verificar la existencia antes de insertar. No considero que esto sea un "mal olor" (¡horrible frase!) Porque la excepción que manejamos la plantea Oracle, no es como plantear sus propias excepciones como un mecanismo de control de flujo.
Gracias al comentario de Igor ahora he ejecutado dos benchamrks diferentes sobre esto: (1) donde todos los intentos de inserción excepto el primero son duplicados, (2) donde todos los insertos no son duplicados. La realidad se ubicará en algún lugar entre los dos casos.
Nota: pruebas realizadas en Oracle 10.2.0.3.0.
Caso 1: Mayormente duplicados
Parece que el enfoque más eficiente (por un factor significativo) es comprobar la existencia MIENTRAS insertando:
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;
/
Resultados (después de ejecutarse una vez para evitar el análisis de los gastos generales):
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
Caso 2: sin duplicados
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;
/
Resultados:
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
En este caso, DUP_VAL_ON_INDEX gana por una milla. Tenga en cuenta que "seleccionar antes de insertar" es el más lento en ambos casos.
Por lo tanto, parece que debe elegir la opción 1 o 3 según la probabilidad relativa de que las inserciones sean o no duplicadas.
En mi humilde opinión, es mejor ir con la Opción 2: Aparte de lo que ya se ha dicho, debe considerar la seguridad de subprocesos . Si opta por la opción 1 y Si varios hilos están ejecutando su bloque PL / SQL, entonces es posible que dos o más hilos seleccionen fuego al mismo tiempo y en ese momento no hay registro, esto terminará liderando todos los hilos para insertar y obtendrá un error de restricción único.