Viendo los new feature de la versión 12c, encontré que ahora podemos definir una columna con el tipo de dato Identity, tal como lo tiene desde hace tiempo DB2. Entonces me puse a investigar, tomando información de distintos blogs y documentos , Lo que vamos a ver a lo largo del artículo, es como implementar columnas Identity y su impacto para poder establecer su mejor forma de utilizarlas. Vamos a trabajar sobre 3 ejes:
Que significa una columna Identity?
Es incorporar a una columna la propiedad de ser autonumérica.
Ahora veremos cómo crear columnas con las distintas alternativas que tenemos al usar la cláusula IDENTITY Sintaxis
COLUMN_NAME GENERATED [ ALWAYS | BY DEFAULT [ ON NULL ] ]AS
IDENTITY [ ( identity_options ) ]
ALWAYS Indica que no será necesario introducir un valor para esta columna en una sentencia insert. Por el contrario indicar un valor aunque sea Null, da un error ORA-32795. BY DEFAULT Le permite utilizar Identity, si la columna no se hace referencia en la una sentencia insert, pero si se hace referencia a la columna, el valor especificado se utiliza en lugar de la identidad. El intento de especificar el valor NULL en este caso se traduce en un error, ya que las columnas de identidad son siempre NOT NULL.
BY DEFAULT Si una sentencia insert se ingresa un valor NULL se generará [ ON NULL ] un nuevo valor, si en cambio si ingresa un valor para esta columna, se insertará el valor de la sentencia
Ejemplo 1) Creación de tabla con clausula ALWAYS:
CREATE TABLE TEST(COL_1 NUMBER GENERATED ALWAYS
AS IDENTITY,
COL_2 VARCHAR2(50));
Esta tabla se crea con la columna COL_1 como Always As Identity. Esto indica que siempre se generará un valor para cada registro insertado.
Insert Into Test(col_2) Values ('Prueba' );
1 filas insertadas.
Esta inserción se lleva a cabo, en cambio la que veremos a continuación da un error;
Insert Into Test(col_1,col_2) Values (999,'Prueba' );
Error SQL: ORA-32795: no se puede insertar una columna de identidad siempre generada.
La cláusula Always As Identity crea una restricción de NOT NULL y no permite que se ingresen valores externos. 2) Creación de tabla con clausula BY DEFAULT. Esta definición de la columna con la cláusula By Default, permite utilizar un valor externo en la inserción y en caso de no existir un valor en un Insert para esta columna se utilizará el valor generado por el Identity. Esta cláusula no permite valores Null.
Create Table Identity_Default (Col_1 Number Generated By Default As Identity,
Col_2 Varchar2(50));
Insert Into Identity_Default values (999,'Prueba');
1 filas insertadas.
Select * From Identity_Default;
Insert Into Identity_Default(col_2) values ('Prueba_2');
1 filas insertadas.
Select * From Identity_Default;
Insert Into Identity_Default Values (Null,'Prueba');
Error SQL: ORA-01400: no se puede realizar una inserción NULL
Como vemos la primer sentencia Insert incluye un valor para la columna que tiene definido un Identity. Como vemos tomo el valor insertado y ante la ausencia tomo el valor generado por el Identity. En la tercer sentencia Insert se ingresa un valor Null para la columna con Identity, esto produce un error dado a que el tipo Identity no permite Null. El uso de la cláusula By Default en una columna Primary Key se puede utilizar, teniendo cuenta que no se inserte valores por una sentencia Insert, dado a que no puede haber valores repetidos, uno ingresado en la sentencia y otro producido por el Identity.
3) Creación de una tabla con la cláusula By Default con restricción Not Null. Esta definición en una columna, permite el uso de valores externos y generará valores para cuando se inserte un valor Null a una columna creada con la cláusulaBy Default Not Null;
Create Table Identity_Default_Not_Null
(Col_1 Number Generated By Default On Null As Identity,
Col_2 Varchar2(50));
Tomaremos como ejemplo una sentencia similar a la última sentencia del ejemplo anterior.
Insert Into Identity_Default_Not_Null
Values (Null,'Prueba');
Select * From Identity_Default_Not_Null;
Como vemos en este caso no genera error como en vimos en el caso del ejemplo 2. Esta cláusula se genera para que la columna no tenga valores nulos sin tener en cuenta si los valores ingresados son consecutivos. Opciones en el uso de la cláusula Identity Al utilizar cualquiera de las clausulas Identity siempre se va a iniciar con el valor 1 y su incremento es de 1? No podemos parametrizar tanto el valor inicial como el incremento entre valores. En el siguiente ejemplo se ve cómo crear una tabla con la cláusula Identity con un valor inicial de 100 y un incremento de 10.
Create Table Identity_Start_Increment
( Col_1 Number Generated Always As Identity (Start With 100
Increment By 10),
Col_2 Varchar2(50));
Realizamos un Insert de 10 registros
Insert Into Identity_Start_Increment (Col_2)
Select Level||' Nro de Fila'
From Dual
Connect by level<=10;
Veamos el resultado de la cláusula Insert
Select *
From Identity_Start_Increment;
Como vemos en COL_1 el valor de la columna se inicia en 100 e incrementa en 10. Esto nos lleva a ver como procesa Oracle cuando ejecutamos una sentencia de creación de tablas que incluya columnas con Identity.
Create Table Identity_Start_Increment
( Col_1 Number Generated Always As Identity
(Start With 100 Increment By 10),
Col_2 Varchar2(50));
Select Table_Name, Column_Name,Data_Default
From User_Tab_Columns
Where Table_Name='IDENTITY_START_INCREMENT';
Tomando el nombre de la secuencia informada en el campo DATA_DEFAULT
ISEQ$$_92132 consultamos la tabla user_sequences;
Select Last_Number, Increment_By, Cache_Size, Max_Value
From User_Sequences
Where Sequence_Name='ISEQ$$_92128';
Como vemos Oracle creó una secuencia con un nombre de sistema con los valores establecidos en la cláusula de creación de la tabla con la opción PURGE, elimino también la secuencia.
Drop Table Identity_Start_Increment PURGE;
Select Last_Number, Increment_By, Cache_Size, Max_Value
From User_Sequences
Where Sequence_Name='ISEQ$$_92128';
table IDENTITY_START_INCREMENT borrado.
no se ha seleccionado ninguna fila
Si se omite la cláusula PURGE la secuencia no se borra .
Esta parte del artículo, va a mostrar cómo responde en el tiempo cada clausula Identity. Para ello vamos a ingresar 100000 registros en tablas con dos campos, un numérico Identity y un varchar2 de 50 caracteres y vamos a tomar el tiempo de cada uno: Primero cargaremos una tabla con el campo Identity Always.
Set Timing On
Insert Into Identity_Always (Col_2)
Select 'Nro de registro '|| Level
From Dual
Connect By Level<=100000;
Resultado
100.000 filas insertadas.
Elapsed: 00:00:01.341
La segunda carga la haremos sobre una columna definida como By Default
Insert Into Identity_Always_By_Default (Col_2)
Select 'Nro de registro '|| Level
From Dual
Connect By Level<=100000;
Resultado
100.000 filas insertadas.
Elapsed: 00:00:00.766
Dado a que el soporta el ingreso de valores cargados desde la sentencia en la columna, veremos la diferencia de tiempos empleada en una carga donde el valor por default no se utiliza, o sea que la secuencia no es la encargada de generar valores.
Insert Into Identity_Always_By_Default
Select Level, 'Nro de registro '|| Level
From Dual
Connect By Level<=100000;
Resultado
100.000 filas insertadas.
Elapsed: 00:00:00.130
Por último vamos a cargar una tabla con una columna definida By Default On Null
Insert Into Identity_Default_On_Null (Col_2)
Select 'Nro de registro '|| Level
From Dual
Connect By Level<=100000;
Resultado
100.000 filas insertadas.
Elapsed: 00:00:00.777
Por último recurrimos a la vieja escuela, antes de Oracle 12c y creamos una tabla sin ningún tipo de Identity
Create Sequence Load_Seq
Start With 1
Increment By 1;
Create Table Table_Seq (Col_1 Number,
Col_2 Varchar2(50 Char));
Insert Into Table_Seq
Select Load_Seq.Nextval, 'Nro de registro '|| Level
From Dual
Connect By Level<=100000;
Resultado
100.000 filas insertadas.
Elapsed: 00:00:00.766
Voy a resaltar dos de los problemas que mayor impacto tendrían.
1) Dado a que una columna definida como identity está vinculada a una secuencia, esta tiene puede observar las mismas situaciones que si la secuencia se define por fuera, por lo cual si un insert abortara, los números de la secuencia se perderían.
2)En el caso de hacer un export de una tabla, se exportaría con todos sus valores, la querer importar esa tabla, deberíamos tener en cuenta que la columna identity este en by default porque en caso contrario daría error.
La inclusión de las columnas Identity, es un importante avance, ya que encapsula en una sentencia de insert la obtención del nextval de la secuencia.
Por otro lado la inclusión de las opciones By Default y By Default On Null, permiten que los valores puedan ser internos o externos.
Lo más importante es que esto lo hacemos casi sin que perdamos performance.
http://www.oracle-base.com/articles/12c/identity-columns-in-oracle-12cr1.php
http://www.oracle.com/technetwork/issue-archive/2013/13-sep/o53asktom-1999186.html
Gerardo Daniel Tezza. Director de Techlearning y Cofundador del AROUG Oracle User Group de Argentina.