Adicionando coluna com valor default em tabelas volumosas

Fala pessoal, como vão vocês? Espero que bem!

Nestes últimos meses venho trabalhando bastante com banco de dados Oracle, com isso acabei me deparando com alguns problemas relacionados a performance.

Um deles, foi ao adicionar uma nova coluna com valor default definido em tabelas volumosas com mais de 5.000.000 de registros.

Como tinha criado um script para cada uma das alterações, percebi que alguns scripts demoraram quase uma hora para finalizar o procedimento. Ficaria inviável coloca-los na aplicação.

Então pesquisando o motivo disso, encontrei algumas pessoas falando sobre o assunto. O comportamento do Oracle são divididos em dois, antes e depois da versão Oracle 11g.

Antes da versão 11g, o Oracle fazia um update para cada registro da tabela acionando à nova coluna o valor default definido no script. Já na versão 11g, este comportamento mudou e ficou mais inteligente. O Oracle passou à adicionar o valor default no metadado da tabela.

Mas fiquei um pouco frustado, pois já estava usando o Oracle 11 e mesmo assim o tempo para incluir um anova coluna estava muito grande. Resolvi então fazer uma consulta informal ao Guilherme Freddi, DBA da Synchro, empresa onde trabalho. Ele me perguntou se ao adicionar a coluna, eu estava também setando a coluna como NOT NULL. Eu disse não, então ele me pediu para refazer o teste.

Exemplo do script de alteração:

ALTER TABLE TABELA ADD EXISTE_ERRO VARCHAR(1 CHAR) DEFAULT 'N' NOT NULL;

Observação: A definição do NOT NULL deve ser feita no mesmo comando do ALTER TABLE, caso contrário você pagará o custo de atualização dos registros.

Para minha felicidade, o script executou instantaneamente. Abaixo deixo as métricas do antes e depois da adição da constraint NOT NULL no final do comando ALTER TABLE.

Adicionando coluna sem a constraint NOT NULL. O tempo de execução é mais de 2 minutos (127.934 segundos)
Adicionando coluna com a constraint NOT NULL o tempo de execução cai para menos de 1 segundo (0.447 segundo).

No link abaixo, o autor faz uma ótima explicação sobre o tema, dando exemplos inclusive de como as informações são gravadas no metadados da tabela.

Fonte: fast=true for adding columns in 11G

um comentário

  1. […] Adicionando coluna com valor default em tabelas volumosas […]

    Curtir

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google

Você está comentando utilizando sua conta Google. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s

%d blogueiros gostam disto: