Oracle Database 12c, Columna Identity. ¿El fin de las secuencias?

Por Gerardo Tezza
Publicado en Abril 2014

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:

  • Implementación
  • Performance
  • Problemas en su implementación

Implementación:

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 ) ]

Donde

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áusula
By 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 .

Performance

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

Problemas en su implementación

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.

Conclusiones

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.