No último post, começamos a falar sobre colunas Identity, uma nova funcionalidade do Oracle 12c, no post de hoje estaremos continuando e concluindo sobre este tema.
4 – Criação de uma tabela com a cláusula By Default com restrição Not Null.
Esta definição de uma coluna, permite a utilização de valores externos e gera
valores quando se insere um valor null a uma coluna criada com a cláusula By Default Not Null;
SQL> Create Table teste.Identity_Default_Not_Null_1
(Col_1 Number Generated By Default On Null As Identity,
Col_2 Varchar2(50)); 2 3
Table created.
SQL>
SQL> Insert Into teste.Identity_Default_Not_Null_1
Values (Null,’Prova’); 2
1 row created.
SQL>
SQL> Select * From teste.Identity_Default_Not_Null_1;
COL_1 COL_2
———- ————————————————–
1 Prova
SQL>
Como podemos ver neste caso,um erro não é gerado pelo insert como no ultimo exemplo do post anterior. Esta cláusula gera valores para a coluna pare que esta não contenha valores nulos, sem levar em consideração que os valores de entrada sejam consecutivos.
5 – Opções sobre o uso da cláusula Identity
O exemplo abaixo mostra como criar uma Tabela com a cláusula Identity com um valor inicial de 100 e com um incremento de 10.
SQL> Create Table teste.Identity_Start_Increment
( Col_1 Number Generated Always As Identity (Start With 100
Increment By 10), Col_2 Varchar2(50));
2 3
Table created.
SQL>
Realizamos un Insert de 10 registros
SQL> Insert Into teste.Identity_Start_Increment (Col_2)
Select Level||’ Numero’
From Dual
Connect by level<=10; 2 3 4
10 rows created.
SQL>
Vemos o resultado da cláusula Insert
SQL> Select * From teste.Identity_Start_Increment;
COL_1 COL_2
———- ————————————————–
100 1 Numero
110 2 Numero
120 3 Numero
130 4 Numero
140 5 Numero
150 6 Numero
160 7 Numero
170 8 Numero
180 9 Numero
190 10 Numero
10 rows selected.
SQL>
Como mostrado o valor da coluna col_1 começa em 100 e vai aumentando em 10.
Verificando as colunas IDENTITY
SQL> Select Table_Name, Column_Name,Data_Default From Dba_Tab_Columns
Where Table_Name=’IDENTITY_DEFAULT_NOT_NULL_1′;
TABLE_NAME COLUMN_NAME DATA_DEFAULT
—————————— ——————– ——————————
IDENTITY_DEFAULT_NOT_NULL_1 COL_2
IDENTITY_DEFAULT_NOT_NULL_1 COL_1 “TESTE”.”ISEQ$$_92301″.nextval
SQL>
SQL> Select Last_Number, Increment_By, Cache_Size, Max_Value
From dba_Sequences
Where Sequence_Name=’ISEQ$$_92301′; 2 3
LAST_NUMBER INCREMENT_BY CACHE_SIZE MAX_VALUE
———– ———— ———- ———-
21 1 20 1.0000E+28
SQL>
6 – deletando tabelas com coluna Identity
6.1 – deletando uma tabela com uma coluna Identity a sequencia não é deletada.
drop table teste.IDENTITY_DEFAULT_NOT_NULL_1;
Table dropped.
SQL> Select Last_Number, Increment_By, Cache_Size, Max_Value
From dba_Sequences
Where Sequence_Name=’ISEQ$$_92301′; 2 3
LAST_NUMBER INCREMENT_BY CACHE_SIZE MAX_VALUE
———– ———— ———- ———-
21 1 20 1.0000E+28
SQL>
6.2 – Restaurando a tabela e dropando a mesma com a opção purge
SQL> FLASHBACK TABLE teste.IDENTITY_DEFAULT_NOT_NULL_1 to before drop;
Flashback complete.
SQL> SQL> desc teste.IDENTITY_DEFAULT_NOT_NULL_1
Name Null? Type
—————————————– ——– —————————-
COL_1 NOT NULL NUMBER
COL_2 VARCHAR2(50)
SQL>
SQL> drop table teste.IDENTITY_DEFAULT_NOT_NULL_1 purge;
Table dropped.
SQL> Select Last_Number, Increment_By, Cache_Size, Max_Value
From dba_Sequences
Where Sequence_Name=’ISEQ$$_92301′; 2 3
no rows selected
SQL>
Colocando a cláusula purge a sequencia também é deletada.
7 – Comparando tempo de resposta entre insert com colunas Identity e uso de sequences
Agora iremos mostrar o tempo de resposta da cláusula Identity. Vamos inserir 100.000 registros numa tabela, vamos tomar o tempo cada um:
7.1 – carregando uma tabela com campo de Identity Allways.
SQL> Create Table teste.Identity
( Col_1 Number Generated Always As Identity, Col_2 Varchar2(50)); 2
Table created.
SQL> Set Timing On
Insert Into teste.Identity (Col_2)
Select ‘Numero de registro ‘|| Level
From Dual
Connect By Level<=100000;SQL> 2 3 4
100000 rows created.
Elapsed: 00:00:01.96
SQL>
7.2 – uma nova carga em uma tabela com a coluna definida com By Default
SQL> Insert Into teste.Identity_Default (Col_2)
Select ‘Numero de registro ‘|| Level
From Dual
Connect By Level<=100000; 2 3 4
100000 rows created.
Elapsed: 00:00:01.76
SQL>
7.3 – Comparando o tempo de resposta durante a inserção de uma tabela com a opção allways e outra BY DEFAULT ON NULL
Primeiro carregar uma tabela com campo de Identity Allways.
SQL> Insert Into teste.Identity (Col_2)
Select ‘Numero de registro ‘|| Level
From Dual
Connect By Level<=100000; 2 3 4
100000 rows created.
Elapsed: 00:00:01.69
SQL>
7.4 – Carregar uma tabela com uma coluna definida por By Default On Null
SQL> Create Table teste.Identity_Default_On_Null
2 (Col_1 Number Generated By Default On Null As Identity, Col_2 Varchar2(50));
Table created.
SQL>
SQL> Insert Into teste.Identity_Default_On_Null (Col_2)
Select ‘Numero de registro ‘|| Level
From Dual
Connect By Level<=100000; 2 3 4
100000 rows created.
Elapsed: 00:00:02.01
SQL>
Por ultimo utilizaremos o velho método, já utilizado antes da versão 12c
7.5 – Criando uma tabela sem colunas Identitity e uma sequence para avaliamos o tempo.
SQL> Create Table teste.Tabela_com_sequence (Col_1 Number,
Col_2 Varchar2(50 Char)); 2
Table created.
SQL>
agora vamos criar uma sequencia
SQL> Create Sequence teste.Seq_teste
2 Start With 1 Increment By 1;
Sequence created.
SQL>
Agora vamos inserir registros nessa tabela
SQL> Insert Into teste.Tabela_com_sequence
Select teste.Seq_teste.nextval, ‘Numero de registro ‘|| Level
From Dual
Connect By Level<=100000;
2 3 4
100000 rows created.
Elapsed: 00:00:01.83
SQL>
8 – Alguns pontos devem ser observados:
8.1 – Dado uma coluna definida como Identity está vinculada a uma sequencia,no caso de uma instrução insert for abortada, os números da sequência serão perdidos.
8.2 – Caso uma tabela seja exportada com todos os seus valores, devemos observar que a coluna de identity esteja em by default, porque de outra forma teremos um erro.
Conclusão
A inclusão de colunas de Identity é um avanço importante, já que a mesma encapsula a instrução nextval de uma sequência.
Por outro lado, a inclusão de opções, By Default e By Default On Null, permitem que os valores a serem inseridos possam ser internos ou externos.
A coisa mais importante neste post é que demonstramos isso sem quase perder desempenho.
bibliografia
ORACLE-BASE
ASK-TOM ORACLE Documentation
ORACLE BLOG 