Melhorar performance no comando DELETE

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Responder
cleberz
Rank: Analista Pleno
Rank: Analista Pleno
Mensagens: 107
Registrado em: Ter, 15 Nov 2011 8:42 am

Pessoal estou deletando uma tabela com 12 milhoes de registro com o seguinte comando:

Selecionar tudo

DELETE FROM FLXSTG.CFTB_CONTRACT_CHARGES
WHERE STG_BLK_CD = 'UE' AND DT_INS_STG < to_date('05/06/13','dd/mm/YY') -- 12.627163
Os dois campos são indices. Acontece que esta demorando muuuuuuuuuuuuuito...horas e horas.

alguém tem uma ideia o que eu posso fazer para melhorar a performance?
Avatar do usuário
stcoutinho
Moderador
Moderador
Mensagens: 850
Registrado em: Qua, 11 Mai 2011 5:15 pm
Localização: são Paulo - SP

Cleberz,

Como você mencionou, são 12 milhões de registros que você está tentando executar com um único comando DELETE. Não creio que a limpeza seja instântanea, especialmente se você estiver em modo ARCHIVE.

Talvez você pudesse usar COLLECTIONS para tentar agilizar a limpeza desta tabela. COLLECTIONS funcionam como cursores e podem agilizar o processamento de um volume massivo de dados

Abaixo, segue uma sugestão (rascunho) de como poderia elaborar uma rotina com COLLECTIONS.

Mas você precisaria pesquisar um pouco sobre este tema no manual de SQL ou de PL/SQL do ORACLE.

Livros em português da ORACLE PRESS sobre programação PL/SQL apresentam tópicos completos sobre esta funcionalidade.

Selecionar tudo

DECLARE
  -- Cursor para identificar o rowid (endereco unico) a ser limpo na tabela
  -- Rowid seria um endereco unico que localiza o registro na instancia dados
  CURSOR CV_LIMPEZA IS
      SELECT ROWID FROM <TABELA> WHERE <CONDICOES>;
  -- Estou criando uma especie de array aqui
  TP_ROWID    IS TABLE OF CHAR(18); 
  RY_ROWID    TP_ROWID;
BEGIN
  -- Abrindo cursor
  OPEN CV_LIMPEZA;
  -- Loop de limpeza principal
  LOOP
     -- Carregando no array 50000 registros (enderecos rowid)
     FETCH  CV_LIMPEZA BULK COLLECT INTO RY_ROWID LIMIT 50000;
     -- Comando FORALL para limpeza massiva dos registros
     FORALL I IN 1..RY_ROWID.COUNT 
         DELETE FROM <TABELA> WHERE ROWID = RY_ROWID(I);
     COMMIT;
    -- Se chegou ao final do cursor, sai do LOOP 
    EXIT WHEN CV_LIMPEZA%NOTFOUND;
  END LOOP;
  -- Fechando o cursor
  CLOSE CV_LIMPEZA;
END;  
Abraços,

Sergio Coutinho
Avatar do usuário
fbifabio
Moderador
Moderador
Mensagens: 199
Registrado em: Ter, 22 Fev 2011 1:51 pm
Localização: São Paulo - SP
Contato:
Fábio Prado
www.fabioprado.net

cleberz,

A collection não vai melhorar este problema. SQL puro é sempre mais rápido do que SQL dentro de bloco PL/SQL.

O que eu vejo de cara que daria para fazer no seu caso seria indexar as colunas da cláusula WHERE, criando preferencialmente um índice composto ou executar parallel dml.

[]s
ogj
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 5
Registrado em: Qui, 06 Jun 2013 2:45 pm

-- Cira uma tabela baseada nos registros que você deseja manter

Selecionar tudo

CREATE TABLE myTable2 AS
(SELECT * FROM  myTable
  WHERE ID IN (o que deseja manter))
--
--A paga toda a tabela com TRUNCATE

Selecionar tudo

TRUNCATE TABLE myTable;
--Após apagar a tabela, você cria um insert com base nos registros que preservou na sua tabela de BKP(myTable2)

Selecionar tudo

  INSERT INTO myTable
     (campos preservados)
  SELECT (campos preservados)
    FROM myTable2

Sou iniciante em PL/SQL, mais acredito que isso seria possivel.

Se não tenta indo deletando em partes .
cleberz
Rank: Analista Pleno
Rank: Analista Pleno
Mensagens: 107
Registrado em: Ter, 15 Nov 2011 8:42 am

valeu galera, eu particionei a tabela e estou deletando por partição com o seguinte comando:

Selecionar tudo

ALTER TABLE <tabela> TRUNCATE SUBPARTITION;

Obrigado!
Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5024
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Senhores

Sei que este é um caso já resolvido. O cleberz usou TRUNCATE SUBPARTITION.
Mas vamos analisar todas opções:

O Fábio citou acima usar um comando DELETE direto.
Sabemos que o GURU Tom Kyte já disse que é sempre melhor usar um comando SQL direto do que usar PLSQL. (ou seja, teoricamente, um DELETE é ***sempre*** melhor que usar BULK).
MAAAAS... Neste caso são 12 milhões de linhas. Creio que dependendo do tamanho das colunas, um comando DELETE não suportaria apagar tudo isso. (ia faltar UNDO, etc)

Não sabemos quantas linhas tem na tabela ao todo.
Então na minha opinião, sobraria usar o FORALL que o Sergio sugeriu, OU usar o TRUNCATE.

1. Caso praticamente todas linhas precisam ser apagadas, ai a idéia do TRUNCATE é boa. (salvar as linhas que se quer MANTER numa outra tabela, TRUNCATE a original, e depois insere as linhas de volta).

2. Mas caso a tabela tenha 200 milhões de linhas, ai creio que o FORALL/BULK seja melhor.

Como vemos, cada caso é um caso... No tuning, nunca existe uma resposta pronta e rápida. Como já disse o PORTILHO, "No tuning, o que interessa é o mais rápido. Não importa como é feito". Ai o jeito é tentar várias coisas e MEDIR o tempo. :-o
jjeffman
Rank: Estagiário Júnior
Rank: Estagiário Júnior
Mensagens: 1
Registrado em: Sex, 01 Dez 2017 2:55 pm

stcoutinho escreveu:Cleberz,

Como você mencionou, são 12 milhões de registros que você está tentando executar com um único comando DELETE. Não creio que a limpeza seja instântanea, especialmente se você estiver em modo ARCHIVE.

Talvez você pudesse usar COLLECTIONS para tentar agilizar a limpeza desta tabela. COLLECTIONS funcionam como cursores e podem agilizar o processamento de um volume massivo de dados

Abaixo, segue uma sugestão (rascunho) de como poderia elaborar uma rotina com COLLECTIONS.

Selecionar tudo

DECLARE
  -- Cursor para identificar o rowid (endereco unico) a ser limpo na tabela
  -- Rowid seria um endereco unico que localiza o registro na instancia dados
  CURSOR CV_LIMPEZA IS
      SELECT ROWID FROM <TABELA> WHERE <CONDICOES>;
  -- Estou criando uma especie de array aqui
  TP_ROWID    IS TABLE OF CHAR(18); 
  RY_ROWID    TP_ROWID;
BEGIN
  -- Abrindo cursor
  OPEN CV_LIMPEZA;
  -- Loop de limpeza principal
  LOOP
     -- Carregando no array 50000 registros (enderecos rowid)
     FETCH  CV_LIMPEZA BULK COLLECT INTO RY_ROWID LIMIT 50000;
     -- Comando FORALL para limpeza massiva dos registros
     FORALL I IN 1..RY_ROWID.COUNT 
         DELETE FROM <TABELA> WHERE ROWID = RY_ROWID(I);
     COMMIT;
    -- Se chegou ao final do cursor, sai do LOOP 
    EXIT WHEN CV_LIMPEZA%NOTFOUND;
  END LOOP;
  -- Fechando o cursor
  CLOSE CV_LIMPEZA;
END;  
Abraços,

Sergio Coutinho
Olá Sergio,

Como eu tive um problema semelhante, embora minha questão não fosse a performance, mas sim o estouro da tablespace temp/undo em clientes onde eu não tinha direitos de DBA, usei este script como base para exclusão de registros, só que verifiquei que sempre sobravam registros que atendiam a condição do cursor CV_LIMPEZA.

Mesmo em versão mais recentes do Oracle (10g, 11g) o valor CV_LIMPEZA%NOTFOUND em BULK colections resulta verdadeiro sempre que o número de linhas retornado for inferior ao limite utilizado, assim a solução é substituí-lo por

Selecionar tudo

EXIT WHEN RY_ROWID.COUNT = 0 .
Jayme Jeffman
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 11 visitantes