Melhor maneira de excluir um conjunto de registros muito grande no Oracle

A lógica com 'A' e 'B' pode estar "escondida" atrás de uma coluna virtual na qual você pode fazer o particionamento:

alter session set nls_date_format = 'yyyy-mm-dd';
drop   table tq84_partitioned_table;

create table tq84_partitioned_table (
  status varchar2(1)          not null check (status in ('A', 'B')),
  date_a          date        not null,
  date_b          date        not null,
  date_too_old    date as
                       (  case status
                                 when 'A' then add_months(date_a, -7*12)
                                 when 'B' then            date_b
                                 end
                        ) virtual,
  data            varchar2(100) 
)
partition   by range  (date_too_old) 
( 
  partition p_before_2000_10 values less than (date '2000-10-01'),
  partition p_before_2000_11 values less than (date '2000-11-01'),
  partition p_before_2000_12 values less than (date '2000-12-01'),
  --
  partition p_before_2001_01 values less than (date '2001-01-01'),
  partition p_before_2001_02 values less than (date '2001-02-01'),
  partition p_before_2001_03 values less than (date '2001-03-01'),
  partition p_before_2001_04 values less than (date '2001-04-01'),
  partition p_before_2001_05 values less than (date '2001-05-01'),
  partition p_before_2001_06 values less than (date '2001-06-01'),
  -- and so on and so forth..
  partition p_ values less than (maxvalue)
);

insert into tq84_partitioned_table (status, date_a, date_b, data) values 
('B', date '2008-04-14', date '2000-05-17', 
 'B and 2000-05-17 is older than 10 yrs, must be deleted');


insert into tq84_partitioned_table (status, date_a, date_b, data) values 
('B', date '1999-09-19', date '2004-02-12', 
 'B and 2004-02-12 is younger than 10 yrs, must be kept');


insert into tq84_partitioned_table (status, date_a, date_b, data) values 
('A', date '2000-06-16', date '2010-01-01', 
 'A and 2000-06-16 is older than 3 yrs, must be deleted');


insert into tq84_partitioned_table (status, date_a, date_b, data) values 
('A', date '2009-06-09', date '1999-08-28', 
 'A and 2009-06-09 is younger than 3 yrs, must be kept');

select * from tq84_partitioned_table order by date_too_old;

-- drop partitions older than 10 or 3 years, respectively:

alter table tq84_partitioned_table drop partition p_before_2000_10;
alter table tq84_partitioned_table drop partition p_before_2000_11;
alter table tq84_partitioned_table drop partition p2000_12;

select * from tq84_partitioned_table order by date_too_old;

A solução clássica para isso é particionar suas tabelas, por exemplo, por mês ou por semana. Se você não os encontrou antes, uma tabela particionada é como várias tabelas estruturadas de forma idêntica com um implícito UNIONao selecionar, e o Oracle armazenará automaticamente uma linha na partição apropriada ao inseri-la com base nos critérios de particionamento. Você menciona índices - bem, cada partição também recebe seus próprios índices particionados. É uma operação muito barata no Oracle descartar uma partição (é análogo a umTRUNCATEem termos de carga porque é isso que você está realmente fazendo - truncando ou descartando uma dessas subtabelas invisíveis). Será uma quantidade significativa de processamento para particionar "após o fato", mas não faz sentido chorar sobre o leite derramado - as vantagens de fazer até agora superam os custos. Todo mês você dividiria a partição superior para criar uma nova partição para os dados do próximo mês (você pode automatizar isso facilmente com um DBMS_JOB).

E com partições você também pode explorar consultas paralelas e eliminação de partições , o que deve deixar seus usuários muito felizes...

Um aspecto a ser considerado é quanto do desempenho de exclusão resulta dos índices e quanto da tabela bruta. Cada registro excluído da tabela requer a mesma exclusão da linha de cada índice btree. Se você tiver mais de 30 índices btree, suspeito que a maior parte do seu tempo seja gasto na manutenção do índice.

Isso tem um impacto na utilidade do particionamento. Digamos que você tenha um índice no nome. Um índice Btree padrão, tudo em um segmento, pode ter que fazer quatro saltos para ir do bloco raiz para o bloco folha e uma quinta leitura para obter a linha. Se esse índice for particionado em 50 segmentos e você não tiver a chave de partição como parte da consulta, cada um desses 50 segmentos precisará ser verificado. Cada segmento será menor, então você pode ter que fazer apenas 2 saltos, mas ainda pode acabar fazendo 100 leituras em vez das 5 anteriores.

Se forem índices de bitmap, as equações são diferentes. Você provavelmente não está usando índices para identificar linhas individuais, mas sim conjuntos delas. Portanto, em vez de uma consulta usando 5 E/S para retornar um único registro, ela estava usando 10.000 E/S. Como tal, a sobrecarga extra em partições extras para o índice não importa.

a exclusão de 50 milhões de registros por mês em lotes de 50.000 é de apenas 1.000 iterações. se você fizer 1 exclusão a cada 30 minutos, deve atender às suas necessidades. uma tarefa agendada para executar a consulta que você postou, mas remova o loop para que ele seja executado apenas uma vez, não deve causar uma degradação perceptível aos usuários. Fazemos praticamente o mesmo volume de registros em nossa fábrica que funciona praticamente 24 horas por dia, 7 dias por semana e atende às nossas necessidades. Na verdade, espalhamos um pouco mais de 10.000 registros a cada 10 minutos, que são executados em cerca de 1 ou 2 segundos em nossos servidores Oracle unix.

Se o espaço em disco não for um prêmio, você poderá criar uma cópia "de trabalho" da tabela, digamos my_table_new, usando CTAS (Criar tabela como seleção) com critérios que omitiriam os registros a serem descartados. Você pode fazer a instrução create em paralelo e com a dica de acréscimo para torná-la rápida e, em seguida, criar todos os seus índices. Então, uma vez terminado (e testado), renomeie a tabela existente para my_table_olde renomeie a tabela "trabalho" para my_table. Uma vez que você está confortável com tudo drop my_table_old purgepara se livrar da mesa velha. Se houver um monte de restrições de chave estrangeira, dê uma olhada no dbms_redefinition pacote PL/SQL . Ele clonará seus índices, restrições, etc. ao usar as opções apropriadas. Este é um resumo de uma sugestão de Tom Kyte do AskTomfama. Após a primeira execução, você pode automatizar tudo, e a criação da tabela deve ser muito mais rápida, podendo ser feita enquanto o sistema estiver ativo, e o tempo de inatividade do aplicativo seria limitado a menos de um minuto para fazer a renomeação das tabelas. Usar CTAS será muito mais rápido do que fazer várias exclusões em lote. Essa abordagem pode ser particularmente útil se você não tiver o particionamento licenciado.

CTAS de amostra, mantendo linhas com dados dos últimos 365 dias e flag_inactive = 'N':

create /*+ append */ table my_table_new 
   tablespace data as
   select /*+ parallel */ * from my_table 
       where some_date >= sysdate -365 
       and flag_inactive = 'N';

-- test out my_table_new. then if all is well:

alter table my_table rename to my_table_old;
alter table my_table_new rename to my_table;
-- test some more
drop table my_table_old purge;

ao descartar uma partição, você deixa índices globais inutilizáveis, que precisam reconstruir, a reconstrução de índices globais seria um grande problema, pois se você fizer isso online, será bastante lento, caso contrário, você precisará de tempo de inatividade. em ambos os casos, não pode atender ao requisito.

"Normalmente acabamos limpando entre 10 e 50 milhões de linhas por mês"

eu recomendaria usar a exclusão de lote PL/SQL, várias horas está ok, eu acho.