Coluna NOT NULL ENABLE NOVALIDATE

Fala pessoal, como estão?

Devido a necessidades de negócio, podemos nos deparar com o cenário de ter em uma tabela X um campo que foi definido como NULO e que a partir de agora não pode mais permitir NULOs, ou seja, NOT NULL, porém, a tabela X já está populada. Em algumas situações, podemos ter tabelas que podem conter até milhões de registros.

Até ai sem novidades, de maneira simples podemos criar um script que percorre os dados de toda a tabela, e que preencha a coluna vazia com algum valor default por exemplo, e após a confirmação desta transação podemos então realizar um ALTER TABLE que modifica a coluna para NOT NULL.

Se pensarmos em performance quando esse cenário abrange alguns milhões de registros em produção, essa talvez não seja a melhor opção, visto que esta estratégia irá gerar: tempo de processamento, utilização de CPU, geração de dados de UNDO e Redo Logs e etc.

Neste artigo quero falar sobre outra possibilidade que podemos utilizar, para assim reduzir tais impactos gerados pela solução anterior, trazendo benefícios como:

• Redução de tempo de processamento;
• Redução da geração de UNDO/Redo log de processamento no banco;
• Redução de utilização de CPU e outros recursos de servidor e instância.

Vamos lá. O que primeiramente precisamos saber é que quando criamos um campo em uma tabela com a opção: NOT NULL, o Oracle cria uma constraint que por padrão é executada a cada instrução DML. Ou seja, quando executamos um insert por exemplo, a constraint automaticamente “valida” a informação inserida retornando falha ou não, restringindo assim quando necessário e conforme a regra estabelecida.

Sabendo disso, o que podemos fazer é: alterar a coluna para NOT NULL, desabilitando a validação dos dados antigos que já estão NULOs e gravados no banco e permitir que o Oracle valide as novas informações na tabela a partir do momento da modificação da coluna. Vamos a um exemplo:

Primeiro criamos uma tabela que tem dois campos que não permitem nulos e apenas a coluna: ID do lançamento permitirá valores vazios:

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0
 
SQL> create table tb_saldo (dt_lancamento date not null,
2                         saldo number(10,2) not null,
3                         id_lancamento number(9) );

Table created   

Agora, inserimos um milhão de registros para teste, deixando apenas a coluna ID nula:

SQL> insert into tb_saldo (dt_lancamento, saldo)
2    select to_date('01/01/2016', 'dd/mm/yyyy') + rownum datas,
3           round(dbms_random.value(low => 1, high => 10000), 2) valores
4      from dual
5    connect by level <= 1000000;  

1000000 rows inserted 

Executed in 4,992 seconds  

SQL> commit;

Commit complete

SQL> select * from tb_saldo where rownum <= 10; 

DT_LANCAMENTO        SALDO ID_LANCAMENTO
------------- ------------ ------------- 
26/04/2019         2663,34  
27/04/2019         3415,29  
28/04/2019         6532,49  
29/04/2019         6331,55  
30/04/2019         9967,23  
01/05/2019         9574,83  
02/05/2019         6929,65  
03/05/2019         6620,44  
04/05/2019         2221,89  
05/05/2019         5832,51
   
10 rows selected  

Neste momento, com a tabela populada, modificamos a coluna ID para não permitir mais nulos, porém desconsideramos a validação dos dados que já estão na tabela com a opção: ENABLE NOVALIDATE. A alteração na tabela é praticamente instantânea:

SQL> alter table tb_saldo modify id_lancamento NOT NULL ENABLE NOVALIDATE;

Table altered

Executed in 0,034 seconds

Os dados continuam na tabela, veja:

SQL>   select * from tb_saldo where rownum <= 10;  

DT_LANCAMENTO        SALDO ID_LANCAMENTO
------------- ------------ ------------- 
26/04/2019         2663,34  
27/04/2019         3415,29  
28/04/2019         6532,49  
29/04/2019         6331,55  
30/04/2019         9967,23  
01/05/2019         9574,83  
02/05/2019         6929,65  
03/05/2019         6620,44  
04/05/2019         2221,89  
05/05/2019         5832,51   

10 rows selected 

Se tentarmos agora inserir um registro com ID nulo, a constraint validará a informação e não permitirá a transação. Desta maneira os dados antigos podem continuar nulos, mas os novos registros a partir de nosso ALTER TABLE não são permitidos nulos:

SQL> insert into tb_saldo (dt_lancamento, saldo, id_lancamento) values ( trunc(sysdate), 200, null );

insert into tb_saldo (dt_lancamento, saldo, id_lancamento) values ( trunc(sysdate), 200, null )

ORA-01400: não é possível inserir NULL em ("PRODUCAO"."TB_SALDO"."ID_LANCAMENTO")

Veja as constraints que foram criadas:

SQL> select a.constraint_name, a.search_condition, a.validated  from all_constraints a where a.table_name = 'TB_SALDO';

CONSTRAINT_NAME                SEARCH_CONDITION                                                                 VALIDATED
------------------------------ -------------------------------------------------------------------------------- -------------
SYS_C00355382                  "DT_LANCAMENTO" IS NOT NULL                                                      VALIDATED
SYS_C00355383                  "SALDO" IS NOT NULL                                                              VALIDATED
SYS_C00355384                  "ID_LANCAMENTO" IS NOT NULL                                                      NOT VALIDATED

Assim, evitamos ter que percorrer toda a tabela para preencher tal coluna vazia e depois modificar o campo para NOT NULL, ganhando desempenho e otimização dos recursos. É apenas uma possibilidade, pois, depende de cada empresa com sua regra de negócio.

Abraço,

Ronaldo