Menu de navegação

[José Roberto Rodrigues dos SantosParticipante]

Tenho uma tabela gigantesca no Oracle e tenho que deletar , mais ou menos, 600.000.000 linhas e não consigo apenas por SQL Delete, trava toda hora e não segue adiante, o Srs teriam alguma dica mágica para ajudar-me neste problema? Já tentei deletar também usando a clausula rownum, para deletar de parte em parte e também não funciona, tipo exemplo abaixo:
DELETE from tabela where Data <= '08/04/16' AND rownum <= 450000; Por gentileza, teriam alguma dica?

[José Laurindo ChiappaModerador]

Não, dica mágica não tem não…. Bom, vc não diz mas IMAGINO que esses 600.000.000 de linhas representam uma fração SIGNIFICATIVA do total de linhas da tabela, tipo uns 60% ou mais, né ?? Bom, de qquer maneira :

a) não sabemos (vc absolutamente não nos diz) Quanto espaço uma linha ocupa nessa sua tabela e nem Qual a qtdade total de linhas para podermos estimar volumes em BYTES (número de linhas é uma métrica Por Demais Imprecisa) mas Suponho que estamos falando em movimentar/acessar/remover múltiplas centenas de Gigabytes, certo ?? Vou dar algumas opções e recomendações, mas de cara já te afirmo que num volume desses DEVERIAM ter sido usados os recursos de organização física em databases de grande porte, como PARTICIONAMENTO, tablespaces dedicadas (digamos, uma tablespace para cada Mês, digamos)e opções de COMPACTAÇÃO – com isso a tarefa seria um simples DROP PARTITION ou um DROP TABLESPACE, ambos se corretamente implementados deveriam levar SEGUNDOS pra se completar….

b) as questões principais quando se fala de performance num DELETE são que PRIMEIRO, um DELETE não só tem que ficar varrendo a tabela pra encontrar as linhas a apagar MAS também obrigatoriamente gera um UNDO equivalente aos dados a apagar, para permitir o ROLLBACK da operação, isso é EXIGÊNCIA das regras de um RDBMS não só do Oracle, e SEGUNDO, os Índices Obrigatoriamente TEM que ser atualizados online após o DELETE, o que é uma operação LONGA e CUSTOSA…
Como ALTERNATIVA, se for detectado que é a geração de UNDO que está interferindo mais na sua performance (algo muito Comum de acontecer), avalie a possibilidade de ao invés do DELETE, vc SALVAR os poucos dados que vc quer manter numa outra tabela qualquer que vc criaria, mandar um TRUNCATE nessa tabela gigante (efetivamente DESTRUINDO essa tabela, o que via de regra é muito rápido) e depois vc insere os dados que quer manter de volta na tabela-gigante… O senão dessa alternativa é que vc PERDE a chance de ROLLBACK (não existe rollback depois de um TRUNCATE) e além disso vc não pode TRUNCAR se há tabelas-filhas com constraints habilitadas, vc tem que desabilitar as constraints antes temporariamente que seja E depois as rehabilitar… https://asktom.oracle.com/pls/apex/f?p=100:11:1337911574066::NO::P11_QUESTION_ID:2345591157689 demonstra um pouco essa técnica…

c) CASO a salva de dados+o truncate da tabela toda+reinsert dos dados não possa ser habilitado por qquer motivo, aí a tua opção é tentar Acelerar o DELETE em si : pra isso, o procedimento é :

1. confirmar que NINGUÉM mais está usando a tabela gigante, a tabela pai dela e/ou as tabelas-filhas da gigante, para evitar qquer tipo de espera por lock e/ou consulta a dados em rollback

2. agendar a operação para uma dia/hora em que não haja quase ninguém mais usando o database (para que todos ou quase todos os recursos do database E do hardware possam ser dedicados ao procedimento de deleção)

3. desabilitar as constraints E os índices da tabela gigante, para que não seja feita a validação de constraints após o DELETE e que a atualização de índices seja feita por vc mais tarde, posteriormente

4. ter o banco E o sistema operacional/hardware configurados para as opções de I/O mais performáticas possíveis : por exemplo, no sistema operacional ter configurado I/O asínchrono e direto, no banco setar o parâmetro DB_FILE_MULTIBLOCK_READ_COUNT para o maior valor admissível pelo teu hardware, ter o parâmetro FILESYSTEMIO_OPTIONS setado para usar todas as opções possíveis no seu hardware (preferencialmente SETALL), etc

5. usar de PARALELISMO no DELETE, ie : ao invés de vc ter apenas uma sessão solitária lendo um pedacinho da tabela por vez para encontrar os dados a deleter, com PARALELISMO vc passa a ter MÙLTIPLAS sessões lendo ao mesmo tempo a tabela, cada uma das sessões lendo um pedacinho diferente da tabela ao mesmo tempo…
SE o teu hardware é capaz de fazer múltiplos I/Os ao mesmo tempo (via de regra hardware de servidor de PRODUÇÂO é plenamente capaz disso), essa opção pode significar uma melhoria IMENSA na performance da deleção….
A criação das sessões escravas que vão ler os diferentes pedacinhos da tabela, a distribuição dos pedacinhos pelas sessões e os controles de processamento da operação podem ser feitos AUTOMATICAMENTE pelo RDBMS Oracle (se vc estiver usando Enterprise Edition ** e ** tiver Licenciado a opção de Parallel SQL, vide https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:39946845137685 por exemplo), OU vc pode fazer o paralelismo de pobre, onde vc abre as múltiplas sessões, distribui os dados e controla o fluxo (https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4248554900346593542 é um exemplo)…

6. Opcionalmente vc pode além de processar em paralelo, também realizar a recuperação das linhas a deletar em BULK, ie, ao invés do programa PL/SQL receber uma linha por vez do banco ele recebe um Array com centenas de linhas a processar (deletar no seu caso) : veja https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:5026576100346976091 para refs…

7. Após o DELETE, a operação de habilitar as constraints e atualizar o índice TAMBÉM pode ser feita em paralelo, mas com a restrição de que tem que ser com a opção de Parallel SQL nativo, não há comando SQL que vc possa usar para atualizar apenas uma parte do índice, exceto se ele for particionado…

[]s

Chiappa

[José Roberto Rodrigues dos SantosParticipante]

Muito obrigado pela aula e peço perdão na forma amadora que fiz a pergunta. BD não é minha especialidade, sinceramente não passava por minha cabeça que teríamos tantas formas de manipular um delete em uma tabela, sempre usei o Be-a-bá dos deletes, selects e updates e nunca tinha tido tamanha dor de cabeça como esta…também esta é a primeira vez que me reparo com uma tabela assim tão gigante em termos de dados armazenados, de fato houve sim, uma falta de profissionalismo na hora de implementar esta base de dados e agora estamos pagando o pato…é o costumeiro e velho barato que saiu muito caro…
Grato demais e muito sucesso para vossa pessoa!!!

[José Laurindo ChiappaModerador]

okdoc, espero ter sido útil no sentido de indicar as possibilidades E que :

a. no futuro vc já saiba ao menos que existem as funcionalidades VLDB (Very Large DataBase), criadas para manipular adequadamente grandes volumes de dados (principalmente o Particionamento), para que Próximas implementações passem a usar elas : absolutamente Não se Entende VLDB, banco com BILHÕES DE LINHAS como deve ser teu caso sem as implementar…

E

b. se hoje neste momento as funcionalidades de VLDB não estão implementadas, é verificar com teu Analista a viabilidade de fazer o TRUNCATE, e se não for viável checar junto com seu DBA como estão as configs que citei E o que é possível/licenciado de usar nesse banco no sentido de Paralelizar o DELETE, preferencialmente numa janela de manutenção/horário em que ninguém esteja usando o banco E com índices e constraints desabilitados ….

[]s

Chiappa

[Autor]

Posts