« Oracle 11gr2 : Claúsula PREPROCESSOR para tablas externasOracle11gr2 : Nueva característica de los índices UNUSABLES (UNUSABLE INDEX) »

Oracle11gr2 : Claúsula IGNORE_ROW_ON_DUPKEY_INDEX , para evitar los errores ORA-0001



Si tenemos una tablaA la cual contiene datos y una tablaB con la misma estructura pero con otros datos , y queremos fusionarla, ¿Cómo lo hacemos sin que haya registros repetidos? , o mejor dicho, ¿Cómo lo hacemos para que no se gatille la Primary Key?



Pues bien , lo podríamos hacer con un Pl/Sql de la siguiente forma :

declare
cursor c1 is select ...
begin
open c1
loop
exit when c1 not found
fetch c1 into variable
end loop;
end;

O podría ser con un FOR ... LOOP , pero en el fondo , debemos generar código sí o sí :-/

Pues bien , Oracle11gr2 , nos soluciona ese inconveniente , ¿cómo?

Ha creado una gran claúsula llamada IGNORE_ROW_ON_DUPKEY_INDEX , que no es nada más que in hint :>> , acá viene su uso...

1.- Creamos la tabla que contiene los datos originales y que contiene la PK

SQL> create table tabla1 (col1 number not null ,
2 col2 number not null ,
3 col3 date ,
4 constraint tabla1_pk primary key (col1));

2.- Insertamos datos sencillos dentro de la tabla, sin violar la PK

SQL> insert into tabla1 values (1,1,sysdate);

1 row created.

SQL> insert into tabla1 values (2,1,sysdate);

1 row created.

SQL> insert into tabla1 values (3,1,sysdate);

1 row created.

3. - Insertamos un valor con el format INSERT VALUES y veremos como se gatilla la Primary Key

SQL> insert into tabla1 values (1,0,sysdate);
insert into tabla1 values (1,0,sysdate)
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.TABLA1_PK) violated

4.- Procedemos a insertar la misma fila, pero con el hint IGNORE_ROW_ON_DUPKEY_INDEX , y vemos el resultado , que no inserta la fila y no arroja error :)

SQL> insert /*+ ignore_row_on_dupkey_index (tabla1 (col1) ) */
2 into tabla1 values (1,0,sysdate);

0 rows created.

¿Qué sucede con el formato de inserción INSERT SELECT? , pues bien acá un ejemplo

5.- Vemos la tabla secundaria y los datos que contiene, los cuales no pueden ser insertados en su totalidad pues violan la PK

SQL> select * from valores_tmp;

CAMPO1 CAMPO2
---------- ----------
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8

6 .- Procedemos a insertar sin ocupar el nuevo HINT y aparece el típico error de violación de PK

SQL> insert into tabla1 (select campo1 , campo2 , sysdate from valores_tmp);
insert into tabla1 (select campo1 , campo2 , sysdate from valores_tmp)
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.TABLA1_PK) violated

7.- Y si consultamos los datos de nuestra tabla original, vemos que no han sido afectados

SQL> select * from tabla1;

COL1 COL2 COL3
---------- ---------- ---------
1 1 05-SEP-09
2 1 05-SEP-09
3 1 05-SEP-09

SQL>

8.- Ahora si insertamos los mismos datos, pero con la nueva claúsula , vemos como ocurre la magia, simplemente selecciona cuales insertar ... que bien :>>

SQL> insert /*+ ignore_row_on_dupkey_index (tabla1 (col1) ) */
2 into tabla1 (select campo1 , campo2 , sysdate from valores_tmp);

5 rows created.

SQL>

9.- Validamos nuevamente nuestra tabla original y están los datos correctos, de ambas tablas

SQL> select * from tabla1;

COL1 COL2 COL3
---------- ---------- ---------
1 1 05-SEP-09
2 1 05-SEP-09
3 1 05-SEP-09
4 4 05-SEP-09
5 5 05-SEP-09
6 6 05-SEP-09
7 7 05-SEP-09
8 8 05-SEP-09

8 rows selected.

SQL>

Espero les sirva

by Ligarius
06.09.09. 14:48:21. 500 words, 5885 views. Categories: Base de datos, Tuning / Performance, Oracle11gR2 ,