Tempo de leitura: menos de 1 minuto
Nesse último mês, andei trabalhando bastante com SQL, melhor dizendo PL/SQL. Basicamente minha tarefa era migração de dados, especificamente, alteração da PK de uma tabela que é referenciada por várias outras tabelas. No entanto, precisava fazer isso com a melhor performance possível.
Como não sou um DBA, faço apenas o “feijão com arroz”, estudei as formas de update que o Oracle disponibiliza.
Duas delas me chamaram bastante atenção, pois são as mais usadas pelos DBAs, são elas Create Table as Select e Merge Update.
Claro, essas são as mais indicadas, pois não uso particionamento de tabelas.
Create Table as Select
A primeira alternativa, Create Table as Select, consiste em criar uma cópia da tabela onde será feito o update. Essa estratégia possibilita realizar a alteração na projeção da query.
Vantagens:
- Rápido, o tempo é pouco maior que o retorno da query;
- Em uma única instrução é possível alterar os dados de quantas colunas forem necessárias, inclusive adicionar ou excluir colunas;
- Comando simples de ler e entender. A complexidade fica na query que retornará os dados;
- Não utiliza a tablespace de undo (tablespace usada no rollback).
Desvantagens:
- Duplicidade de dados (tabela);
- Pode estourar a tablespace de dados, portanto é necessário certificar-se que existe espaço suficiente para criação da tabela;
- Procedimentos manuais que devem ser executados pós cópia:
- Recriar as constraints e index na nova tabela, pois essa estratégia só leva os dados;
- Recriar constraints (FKs) de tabelas que referenciam a tabela “copiada”;
- “Dropar” a tabela original, se necessário;
- Em alguns bancos, onde o recurso de lixeira está habilitado, deve-se expurgar a tabela dropada, caso contrário o espaço não é liberado.
Exemplo de uso:
Abaixo segue script para criar as tabelas e incluir os registros. Dado que existam as tabelas :
- NF com as colunas ID (é a PK da tabela) e NUMERO (Observação: 1000 registros);
[code language=”sql”]
CREATE TABLE NF (
ID NUMBER(9) NOT NULL,
NUMERO NUMBER(9) NOT NULL,
CONSTRAINT PK_NF PRIMARY KEY(ID) USING INDEX TABLESPACE &&table_space_name_index
) TABLESPACE &&table_space_name;DECLARE
C NUMBER;
BEGIN
FOR C IN 1.. 1000 LOOP
INSERT INTO NF VALUES (C, C+1000);
END LOOP;
COMMIT;
END;
/
[/code] - NF_ITEM com as colunas ID (é a PK da tabela), DESCRICAO, ID_NF (Observação: 1 milhão de registros).
[code language=”sql”]
CREATE TABLE NF_ITEM (
ID NUMBER(9) NOT NULL,
DESCRICAO VARCHAR2(100 CHAR),
ID_NF NUMBER(9) NOT NULL,
CONSTRAINT PK_NF_ITEM PRIMARY KEY(ID) USING INDEX TABLESPACE &&table_space_name_index
) TABLESPACE &&table_space_name;DECLARE
C NUMBER;
BEGIN
FOR C IN 1.. 1000000 LOOP
INSERT INTO NF_ITEM VALUES (C, ‘DESCRIÇÃO ‘ || C, FLOOR((C/1000))+1);
END LOOP;
COMMIT;
END;
/
[/code]
Problema:
Preciso alterar todos os registros de NF_ITEM fazendo com que o ID seja composto pelo NF.NUMERO + “|#|” + NF_ITEM.ID.
A query necessária para fazer essa migração usando Create Table as Select é a seguinte:
[code language=”sql”]
CREATE TABLE NF_ITEM_COPY TABLESPACE &&table_space_name AS (
SELECT
I.ID AS ID_OLD, –remoneio o campo ID para ID_OLD
NF.NUMERO || ‘|#|’ || I.ID AS ID, — crio uma nova coluna para ID, concatenando os campos necessários
I.DESCRICAO,
I.ID_NF
FROM NF_ITEM I
JOIN NF ON NF.ID = I.ID_NF
);
[/code]
No exemplo acima, criamos uma nova tabela chamada “NF_ITEM_COPY”. Além disso, alteramos o nome da coluna ID para ID_OLD, e criamos uma nova coluna ID concatenando os campos NF.NUMERO e NF_ITEM.ID, separando-os por “|#|”. O tempo de execução desse script foi:
Merge Update
A segunda alternativa, Merge Update, consiste em executar um super update em lote. Essa estratégia não possibilita realizar a alteração na projeção como a anterior.
Vantagens:
- Não estoura tablespace de dados;
- Não há necessidade de recriar constrains;
- Não há necessidade de recriar todos os índices;
Desvantagens:
- Queries mais complexas;
- Mais lento que o “Create Table as select“;
- Não possui paginação de dados, sendo assim, pode estourar a tablespace UNDO;
- Não é possível criar ou excluir colunas da tabela que sofrerá o update.
Exemplo de uso:
Partindo do princípio que iremos fazer a mesma alteração do exemplo anterior, nosso código ficará assim:
[code language=”sql”]
MERGE INTO NF_ITEM I
USING NF ON (NF.ID = I.ID_NF)
WHEN MATCHED THEN
UPDATE SET ID = NF.NUMERO || ‘|#|’ || I.ID;
[/code]
No exemplo acima, alteramos os valor do campo ID da mesma forma como fizemos com o Create Table as select, só que desta vez não criamos uma nova tabela. O tempo de execução deste script foi bem maior que o anterior, no entato, é mais rápido do que fazer update registro a registro:
Valeu pessoal, abaixo deixo a fonte, na qual é muito boa a comparação que o autor faz:
