Melhorar performance no comando DELETE

[cleberz]

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?

[stcoutinho]

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

[fbifabio]

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]

-- 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]

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

Selecionar tudo

ALTER TABLE <tabela> TRUNCATE SUBPARTITION;

Obrigado!

[dr_gori]

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.

[jjeffman]

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