Olá pessoal, hoje trago um conteúdo bastante fundamental e simples, mas que por muitas vezes é esquecido.
Vez ou outra preciso discutir com desenvolvedores sobre a geração de valores sequenciais para os registros de uma tabela. Essa é uma discussão um tanto quanto cansativa, pois os desenvolvedores muitas das vezes optam por gerar esse número sequencial na aplicação, mas na maioria dos casos isso é bem menos performático do que fazê-lo no banco de dados, o qual já possui mecanismos e arquitetura pronta para tal, porém é necessário estar ciente das limitações do uso de sequences no Oracle Database, por exemplo.
Ao optar pelo uso de sequences precisamos assimilar que o Oracle Database não garante valores sequenciais mesmo se utilizadas para uma só tabela e ainda que usando as cláusulas ORDER, INCREMENT BY 1 e NOCACHE. Você eventualmente observará números faltantes no uso dessa sequence. Isso se deve ao fato de que transações que falham ou sofrem rollback fizeram o uso e carregaram um valor da sequence. A transação que utiliza a sequence sofre o rollback, mas não o valor solicitado à sequence. Vejamos um exemplo.
Primeiramente vamos conectar no nosso banco de dados. Aqui estou utilizando meu Autonomous Transaction Processing Database TESTDB:
[Frankys-MacBook-Pro:~ franky$] /Users/franky/Downloads/sqlcl/sqlcl/bin/sql /nolog SQLcl: Release 19.4 Production on Mon Feb 8 19:51:21 2020 Copyright (c) 1982, 2020, Oracle. All rights reserved. SQL> set cloudconfig /Users/franky/Downloads/Wallet_TESTDB.zip Operation is successfully completed. Operation is successfully completed. Using temp directory:/var/folders/1h/mwvhtfsn48gc06mh8yp9lgfh183jkar/T/oracle_cloud_config706624827909182374 SQL> connect admin@testdb_high Password? (**********?) ***************** Connected. SQL> select banner_full from v$version; BANNER_FULL -------------------------------------------------------------------------------- Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.4.0.0.0
Vamos então criar uma tabela e uma sequence para usar de exemplo:
SQL> CREATE TABLE t_tst01 ( 2 c1 NUMBER(5), 3 PRIMARY KEY ( c1 ) 4 ); Table T_TST01 created. SQL> CREATE SEQUENCE s_tst01 INCREMENT BY 1 MINVALUE 1 MAXVALUE 10 NOCYCLE ORDER NOCACHE; Sequence S_TST01 created.
Ao tentar extrair o valor atual da sequence o erro ORA-08002 é retornado, pois a sequence ainda não foi inicializada:
SQL> SELECT 2 s_tst01.CURRVAL 3 FROM 4 dual; Error starting at line : 1 in command - SELECT s_tst01.CURRVAL FROM dual Error report - ORA-08002: sequence S_TST01.CURRVAL is not yet defined in this session
Insiro um registro utilizando a sequence e este já a inicializa, em seguida faço rollback:
SQL> INSERT INTO t_tst01 ( c1 ) VALUES ( s_tst01.NEXTVAL ); 1 row inserted. SQL> SELECT 2 * 3 FROM 4 t_tst01; C1 ---------- 1 SQL> ROLLBACK; Rollback complete.
Insiro novamente um registro utilizando a sequence e em seguida consulto o valor inserido. Vejam que mesmo após o rollback o valor da sequence foi incrementado e assim ficamos sem o valor 1 na tabela:
SQL> INSERT INTO t_tst01 ( c1 ) VALUES ( s_tst01.NEXTVAL ); 1 row inserted. SQL> SELECT 2 * 3 FROM 4 t_tst01; C1 ---------- 2 SQL> COMMIT; Commit complete.
Ao consultar o valor atual vemos que ainda se mantém em 2 e ao consultar o próximo valor vemos o 3:
SQL> SELECT 2 s_tst01.CURRVAL 3 FROM 4 dual; CURRVAL ---------- 2 SQL> SELECT 2 s_tst01.NEXTVAL 3 FROM 4 dual; NEXTVAL ---------- 3
Quando inserimos utilizando novamente um novo valor constatamos que o valor 4 foi inserido e armazenado na tabela:
SQL> INSERT INTO t_tst01 ( c1 ) VALUES ( s_tst01.NEXTVAL ); 1 row inserted. SQL> SELECT 2 * 3 FROM 4 t_tst01; C1 ---------- 2 4 SQL> COMMIT; Commit complete.
Ao analisar os exemplos podemos concluir que toda vez que a função NEXTVAL é chamada a sequence é incrementada e mesmo que ocorra uma operação de rollback esta não influencia em nada no incremento da sequence.
Sendo assim é mais fácil preparar a lógica na aplicação para não estar relacionada a valores sequenciais, mesmo porque se um registro precisar ser removido este também deixará um vão na sequência.
A partir da versão 12cR2 é possível já configurar uma coluna para ser sequencial por padrão: GENERATED BY DEFAULT AS IDENTITY. Mas isso é assunto para outro post.
Por hoje é só. Espero que tenham gostado.
Abraços e até o próximo post,
Franky