GENERATED

Las columnas de identidad

La base de datos 12c introduce la posibilidad de definir una cláusula de identidad contra una columna de tabla definido utilizando un tipo numérico. La sintaxis es mostrar a continuación.

GENERATED
[ ALWAYS | BY DEFAULT [ ON NULL ] ]
AS IDENTITY [ ( identity_options ) ]

Haciendo caso omiso de las identity_options , los cuales coinciden con los del CREAR SECUENCIA comunicado, esta sintaxis nos permite utilizar tres variaciones en la funcionalidad de identidad.

Antes de que podamos ver algunos ejemplos, es necesario asegurarse de que el usuario de prueba tiene el CREATE SEQUENCEprivilegio. Sin ella, los intentos de definir una columna de identidad producirá una "ORA-01031: privilegios insuficientes" error.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER=pdb1;

GRANT CREATE TABLE, CREATE SEQUENCE TO test;
CONN test/test@pdb1

El uso de ALWAYS fuerzas el uso de la identidad. Si una instrucción de inserción hace referencia a la columna de identidad, incluso para especificar un valor NULL, se produce un error.

DROP TABLE identity_test_tab PURGE;

CREATE TABLE identity_test_tab (
  id          NUMBER GENERATED ALWAYS AS IDENTITY,
  description VARCHAR2(30)
);

SQL> INSERT INTO identity_test_tab (description) VALUES ('Just DESCRIPTION');

1 row created.

SQL> INSERT INTO identity_test_tab (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION');
INSERT INTO identity_test_tab (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION')
                               *
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column


SQL> INSERT INTO identity_test_tab (id, description) VALUES (999, 'ID=999 and DESCRIPTION');
INSERT INTO identity_test_tab (id, description) VALUES (999, 'ID=999 and DESCRIPTION')
                               *
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column


SQL>

El uso BY DEFAULT le permite utilizar la identidad si la columna no se hace referencia en la instrucción de inserción, pero si se hace referencia a la columna, el valor especificado se utilizará en lugar de la identidad. El intento de especificar el valor NULL en este caso resulta en un error, ya que las columnas de identidad son siempre NOT NULL.

DROP TABLE identity_test_tab PURGE;

CREATE TABLE identity_test_tab (
  id          NUMBER GENERATED BY DEFAULT AS IDENTITY,
  description VARCHAR2(30)
);

SQL> INSERT INTO identity_test_tab (description) VALUES ('Just DESCRIPTION');

1 row created.

SQL> INSERT INTO identity_test_tab (id, description) VALUES (999, 'ID=999 and DESCRIPTION');

1 row created.

SQL> INSERT INTO identity_test_tab (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION');
INSERT INTO identity_test_tab (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION')
                                                        *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("TEST"."IDENTITY_TEST_TAB"."ID")

Usando BY DEFAULT ON NULL permite la identidad que se utilizará si se hace referencia a la columna de identidad, pero no se especifica un valor NULL.

DROP TABLE identity_test_tab PURGE;

CREATE TABLE identity_test_tab (
  id          NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
  description VARCHAR2(30)
);

SQL> INSERT INTO identity_test_tab (description) VALUES ('Just DESCRIPTION');

1 row created.

SQL> INSERT INTO identity_test_tab (id, description) VALUES (999, 'ID=999 and DESCRIPTION');

1 row created.

SQL> INSERT INTO identity_test_tab (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION');

1 row created.

SQL> SELECT * FROM identity_test_tab;

 ID DESCRIPTION
---------- ------------------------------
  1 Just DESCRIPTION
       999 ID=999 and DESCRIPTION
  2 ID=NULL and DESCRIPTION

Basado en el requisito para el CREATE SEQUENCEprivilegio, no es difícil deducir que una secuencia se utiliza para rellenar la columna de identidad.

COLUMN object_name FORMAT A20

SELECT object_name, object_type
FROM   user_objects;

OBJECT_NAME          OBJECT_TYPE
-------------------- -----------------------
ISEQ$$_92117         SEQUENCE
IDENTITY_TEST_TAB    TABLE

2 rows selected.

Las [DBA|ALL|USER]_TAB_IDENTITY_COLSvistas muestran información sobre las columnas de identidad.

SET LINESIZE 100
COLUMN table_name FORMAT A20
COLUMN column_name FORMAT A15
COLUMN generation_type FORMAT A10
COLUMN identity_options FORMAT A50

SELECT table_name, 
       column_name,
       generation_type,
       identity_options
FROM   all_tab_identity_cols
WHERE  owner = 'TEST'
ORDER BY 1, 2;

TABLE_NAME           COLUMN_NAME     GENERATION IDENTITY_OPTIONS
-------------------- --------------- ---------- --------------------------------------------------
IDENTITY_TEST_TAB    ID              ALWAYS     START WITH: 1, INCREMENT BY: 1, MAX_VALUE: 9999999
                                                999999999999999999999, MIN_VALUE: 1, CYCLE_FLAG: N
                                                , CACHE_SIZE: 20, ORDER_FLAG: N

SQL>

El enlace entre la mesa y la secuencia se almacena en la SYS.IDNSEQ$tabla.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER=pdb1;

COLUMN sequence_name FORMAT A30

SELECT a.name AS table_name,
       b.name AS sequence_name
FROM   sys.idnseq$ c
       JOIN obj$ a ON c.obj# = a.obj#
       JOIN obj$ b ON c.seqobj# = b.obj#;

TABLE_NAME      SEQUENCE_NAME
-------------------- ------------------------------
IDENTITY_TEST_TAB    ISEQ$$_92117

el uso de la secuencia es ahora visible en los planes de ejecución.

SET AUTOTRACE ON
SET LINESIZE 200

INSERT INTO identity_test_tab (description) VALUES ('Just DESCRIPTION');

Execution Plan
----------------------------------------------------------
Plan hash value: 993166116

----------------------------------------------------------------------------------------------
| Id  | Operation   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT  |       |    1 |  100 |    1   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL | IDENTITY_TEST_TAB |      |      |    |      |
|   2 |   SEQUENCE   | ISEQ$$_92117      |      |      |    |      |
----------------------------------------------------------------------------------------------

Restricciones

Hay una serie de restricciones menores asociados con las columnas de identidad. En lugar de repetir la documentación, se puede leer aquí .

Performance

Las siguientes tablas nos permitirán comparar el rendimiento de la columna de identidad contra la utilización directa de una secuencia y una solución basada en el gatillo.

-- Create a table with an old-style identity column populated using a trigger.
CREATE TABLE trigger_identity (
  id           NUMBER  NOT NULL,
  description  VARCHAR2(30)
);

CREATE SEQUENCE trigger_identity_seq;

CREATE OR REPLACE TRIGGER trigger_identity_bir 
BEFORE INSERT ON trigger_identity 
FOR EACH ROW
WHEN (new.id IS NULL)
BEGIN
  :new.id := trigger_identity_seq.NEXTVAL;
END;
/

-- Populate the column directly using a sequence.
CREATE TABLE sequence_identity (
  id           NUMBER  NOT NULL,
  description  VARCHAR2(30)
);

CREATE SEQUENCE sequence_identity_seq;

-- Create a table with a real identity column.
CREATE TABLE real_identity (
  id          NUMBER GENERATED ALWAYS AS IDENTITY,
  description VARCHAR2(30)
);

La siguiente secuencia de comandos compara el rendimiento de las inserciones de las tres tablas. La primera prueba utiliza el gatillo para rellenar la columna ID. El segundo ensayo hace referencia a una secuencia directamente, en lugar de depender de un gatillo. El tercero utiliza la nueva funcionalidad de la columna de identidad.

SET SERVEROUTPUT ON
DECLARE
  l_time    PLS_INTEGER;
  l_cpu     PLS_INTEGER;

  TYPE t_data IS TABLE OF trigger_identity.description%TYPE;
  l_data t_data;
BEGIN
  -- Popluate a collection with some dummy data.
  SELECT 'DUMMY DATA'
  BULK COLLECT INTO l_data
  FROM dual
  CONNECT BY level <= 10000;


  -- Trigger-based solution.
  EXECUTE IMMEDIATE 'TRUNCATE TABLE trigger_identity';

  l_time := DBMS_UTILITY.get_time;
  l_cpu  := DBMS_UTILITY.get_cpu_time;

  FORALL i IN l_data.first .. l_data.last
    INSERT INTO trigger_identity (description) VALUES (l_data(i));
  
  DBMS_OUTPUT.put_line('TRIGGER_IDENTITY : ' ||
                       'Time=' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs ' ||
                       'CPU Time=' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs ');


  -- Direct use of a sequence.
  EXECUTE IMMEDIATE 'TRUNCATE TABLE sequence_identity';

  l_time := DBMS_UTILITY.get_time;
  l_cpu  := DBMS_UTILITY.get_cpu_time;

  FORALL i IN l_data.first .. l_data.last
    INSERT INTO sequence_identity (id, description) VALUES (sequence_identity_seq.NEXTVAL, l_data(i));
  
  DBMS_OUTPUT.put_line('SEQUENCE_IDENTITY: ' ||
                       'Time=' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs ' ||
                       'CPU Time=' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs ');


  -- Using an identity column.
  EXECUTE IMMEDIATE 'TRUNCATE TABLE real_identity';

  l_time := DBMS_UTILITY.get_time;
  l_cpu  := DBMS_UTILITY.get_cpu_time;

  FORALL i IN l_data.first .. l_data.last
    INSERT INTO real_identity (description) VALUES (l_data(i));
  
  DBMS_OUTPUT.put_line('REAL_IDENTITY    : ' ||
                       'Time=' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs ' ||
                       'CPU Time=' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs ');
 
END;
/
TRIGGER_IDENTITY : Time=217 hsecs CPU Time=204 hsecs
SEQUENCE_IDENTITY: Time=26 hsecs CPU Time=22 hsecs
REAL_IDENTITY    : Time=28 hsecs CPU Time=26 hsecs

PL/SQL procedure successfully completed.

No es sorprendente que la prueba basada en activadores realiza mucho peor que los otros. El uso directo de una secuencia y la columna de identidad 12c dar resultados comparables, que son típicamente un orden de magnitud más rápido que el uso de un gatillo para rellenar la columna ID.