BLOG

Colunas Identity

Escrito por Francisco Cavalin

25 jul, 2017 | 13:00

Em versões do Banco de Dados Oracle anterior a 12c, não havia uma equivalência direta a Colunas Identity ou com autonumeração. Este comportamento podia ser implementado usando uma combinação de SEQUENCES e TRIGGERS. No Oracle 12c, pode-se implementar isto com Colunas Identity ou definindo o uso de sequences como valores DEFAULT das colunas.

Para criar um Coluna Identity é necessário o privilégio CREATE SEQUENCE.
Para definir uma Coluna Identity use a seguinte sintaxe:
GENERATED [ ALWAYS | BY DEFAULT [ ON NULL ] ] AS IDENTITY [ ( identity_options ) ]

Usando ALWAYS força o uso de Identity, se o comando INSERT se referir a Colunas Identity, será retornado um erro, mesmo ser o valor especificado for NULL.

Usando BY DEFAULT permite o uso do Identity, se a coluna não for referenciada no comando INSERT, mas se a coluna for referenciada, o valor especificados será usado no lugar do Identity. O uso de valores NULL nesse tipo de coluna, gerará um erro, pois colunas Identity são sempre NOT NULL.

Usando BY DEFAULT ON NULL permite o uso do Identity, se a coluna não for referenciada no comando INSERT ou se ela for referenciada e o ser valor for NULL. Se a coluna for referenciada, o valor especificados será usado.

As visões [DBA|ALL|USER]_TAB_IDENTITY_COLS mostram as informações sobre colunas Identity.

Entre as restrições de Colunas Identity estão:
Apenas uma coluna Identity é permitida por tabela.
A coluna Identity deve ser de um tipo numérico, não podendo ser de um tipo definindo pelo usuário.
A coluna Identity não pode ter valor DEFAULT, sendo NOT NULL e NOT DEFERRABLE.
Um comando CREATE TABLE AS SELECT não herdará a coluna Identity.

Comparativamente, a performance da Coluna Identity é bem melhor que a construção com TRIGGERS e SEQUENCES, e quase igual a construção com SEQUENCE direta no comando INSERT.

——————————————————————————
— 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)
);

——————————————————————————
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.

Quer saber mais?

ENTRE EM CONTATO