Identity banco Oracle 12C

Introdução

O objetivo desse post é apresentar o recurso Identity do banco Oracle.

Esse recurso foi implementado a partir da release 12C entretanto já existia nos bancos Mysql, SQL Server e Postgres.

Com bancos Oracle sempre foi padrão a utilização de sequences ao invés de recursos auto-incremento.

Tipos de Identity no Oracle

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

Tipos suportados

ALWAYS: O banco de dados sempre gerará um valor para essa coluna. Inserir um valor manualmente nessa coluna causará um erro.
BY DEFAULT: O banco de dados gerará um valor para essa coluna, caso o valor não seja informado. Se o valor for informado, este será aplicado a coluna. Caso tente inserir um valor NULL nessa coluna causará um erro.
BY DEFAULT ON NULL: Suporta a sintaxe informando valor, omitindo ou passando NULL.

Para as configurações BY DEFAULT ou BY DEFAULT ON NULL, você deve ter atenção ao valor inserido manualmente porque o incremento não leva em consideração valores inseridos manualmente e estourará erro de constraint unique. Por isso, o recomendado é criar ranges distantes para valores inseridos manualmente.

Recursos suportados como identity options

START WITH: Valor inicial do contador. Valor padrão = 1.
INCREMENT BY: Valor interno que o contador será incrementado. Valor padrão é = 1.
CACHE: Define um número de registros que será usado para fazer cache. Recomendado para volumes altos de inserção.

Algumas informações complementares

Apenas uma coluna IDENTITY pode ser usada por tabela. Se sua tabela precisa de duas colunas, as N próximas devem usar sequences.
Somente novas colunas podem ter atributo IDENTITY. Não é possível alterar uma coluna existente para suportar esse recurso. Para essa necessidade, sequences são recomendadas.
Em caso de TRUCANTE Table aplicado, o campo retorna para valor definido pela option START WITH.
Não há opção de uso de NEXTVAL, como na sequence.
É possível usar CURRVAL.
O tipo de dado deve ser numérico.
Não pode ser definido valor default para esse tipo de coluna.
Para esse tipo de coluna, implicitamente já é NOT NULL.
CREATE TABLE AS SELECT não gerará para a tabela filha o comportamento de identity

Script para teste do recurso

DROP TABLE accounts;
CREATE TABLE accounts (
    id NUMBER GENERATED ALWAYS AS IDENTITY,
    text NUMBER NOT null,
    PRIMARY KEY(id)
);

INSERT INTO ACCOUNTS(id,text) VALUES (1,1);
--SQL Error [32795] [99999]: ORA-32795: cannot insert into a generated always identity COLUMN

COMMIT;

INSERT INTO ACCOUNTS(text) VALUES (2);
--Updated Rows	1
COMMIT;


INSERT INTO ACCOUNTS(id,text) VALUES (NULL,2);
--SQL Error [32795] [99999]: ORA-32795: cannot insert into a generated always identity COLUMN
COMMIT;

ALTER TABLE ACCOUNTS 
MODIFY ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY;

INSERT INTO ACCOUNTS(id,text) VALUES (100,3);
--Updated Rows	1
COMMIT;

INSERT INTO ACCOUNTS(text) VALUES (10);
--Updated Rows	1
COMMIT;

INSERT INTO ACCOUNTS(id,text) VALUES (NULL,5);
--Updated Rows	1
COMMIT;

SELECT * FROM ACCOUNTS;

ALTER TABLE ACCOUNTS 
MODIFY ID NUMBER GENERATED BY DEFAULT AS IDENTITY;

INSERT INTO ACCOUNTS(id,text) VALUES (101,6);
--Updated Rows	1
COMMIT;

INSERT INTO ACCOUNTS(text) VALUES (8);
--Updated Rows	1
COMMIT;

INSERT INTO ACCOUNTS(id,text) VALUES (NULL,9);
--SQL Error [1400] [23000]: ORA-01400: cannot insert NULL into ("PROCESSUM"."ACCOUNTS"."ID")
COMMIT;

Performance

No site, foi feita uma comparação entre a estratégia de usar Sequence, Trigger e Identity. Nos testes feitos a sequence teve uma performance mais rápida. Porém, tudo isso, na minha visão, é bem relativo, uma vez que depende de variáveis como concorrência, estratégia/necessidade de cache, dentre outras coisas.

Resultado benchmark:

SEQUENCE_IDENTITY: Time=26 hsecs CPU Time=22 hsecs
REAL_IDENTITY : Time=28 hsecs CPU Time=26 hsecs
TRIGGER_IDENTITY : Time=217 hsecs CPU Time=204 hsecs

Conclusão

Acredito que a Oracle criou o recurso por uma demanda de mercado. Frameworks de persistência demandam por esse tipo de flexibilidade e o esforço de configuração de sequences em alguns contextos demandava mais tempo. É aquela velha máxima, o produto quem demanda é o cliente e o mercado. A Oracle com isso, fornece mais opção para seu produto, agregando valor.

Para quem não tem banco Oracle configurado, existe esse artigo de como instalar um banco Oracle 12C no docker.

Antonio Lazaro (Popete)