Aprenda PL/SQL

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
  

Mensagemem Qua, 12 Jun 2013 4:02 pm

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

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?
cleberz

Mensagemem Qui, 13 Jun 2013 10:53 pm

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.

Código: Selecionar todos
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
stcoutinho
Localização: Sao Paulo - SP

Mensagemem Sex, 14 Jun 2013 9:25 am

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
fbifabio
Localização: São Paulo - SP

Fábio Prado
www.fabioprado.net

Mensagemem Sex, 14 Jun 2013 3:09 pm

-- Cira uma tabela baseada nos registros que você deseja manter
CREATE TABLE myTable2 AS
(SELECT * FROM myTable
WHERE ID IN (o que deseja manter))
--
--A paga toda a tabela com TRUNCATE
TRUNCATE TABLE myTable;

--Após apagar a tabela, você cria um insert com base nos registros que preservou na sua tabela de BKP(myTable2)
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 .
ogj

Mensagemem Qua, 19 Jun 2013 10:26 am

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

ALTER TABLE <tabela> TRUNCATE SUBPARTITION;


Obrigado!
cleberz

Mensagemem Seg, 29 Jul 2013 7:09 pm

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
dr_gori
Localização: Porto Alegre - RS

Thomas F. G
https://www.patreon.com/glufke

Mensagemem Sex, 01 Dez 2017 3:10 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.

Código: Selecionar todos
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 EXIT WHEN RY_ROWID.COUNT = 0 .

Jayme Jeffman
jjeffman


  • Veja também
    Respostas
    ExibiÇões
    Última mensagem


        Voltar para PL/SQL

        Quem está online

        Usuários navegando neste fórum: Google [Bot] e 4 visitantes