Início / dba / Perguntas / 343525
Accepted
Pantea
Asked: 2024-11-11 21:10:37 +0800 CST

Consulta para excluir registros com eff_date menor em uma tabela grande com 400 milhões de registros

  • 772

Tenho uma tabela com a seguinte estrutura:

create table TEST_TAB
(
  activity_type CHAR(1),
  tracking_code NUMBER,
  eff_date      DATE
)

Dados de amostra para esta tabela:

insert into TEST_TAB (activity_type, tracking_code, eff_date)
values ('A', 1, to_date('01-11-2020', 'dd-mm-yyyy'));
insert into TEST_TAB (activity_type, tracking_code, eff_date)
values ('A', 1, to_date('02-01-2024', 'dd-mm-yyyy'));
insert into TEST_TAB (activity_type, tracking_code, eff_date)
values ('B', 2, to_date('01-08-2023', 'dd-mm-yyyy'));
insert into TEST_TAB (activity_type, tracking_code, eff_date)
values ('B', 2, to_date('02-08-2023', 'dd-mm-yyyy'));
insert into TEST_TAB (activity_type, tracking_code, eff_date)
values ('B', 2, to_date('03-08-2023', 'dd-mm-yyyy'));

Este é apenas um dado de amostra e a quantidade de dados reais na tabela original é de quase 400 milhões de registros. O que preciso fazer é que, para cada grupo de activity_type, tracking_code, preciso manter o registro que tem o maior "eff_date" e excluir o restante. Então, para o , activity_type=A and tracking_code = 1preciso manter um com eff_date = 1/2/2024e excluir o outro. O que tenho por enquanto é a consulta abaixo:

delete from test_tab
 where rowid in (select rid
                   from (select rowid as rid,
                                row_number() over(partition by activity_type, tracking_code order by eff_date desc) as row_num
                           from test_tab
                           )
                  where row_num > 1
                  )

No entanto, isso parece muito lento. Você poderia sugerir alguma solução melhor? A tabela original é particionada em eff_date e tem índice nas duas colunas restantes. Outro ponto é que pode haver mais de um intervalo de um ano entre eff_dates de cada registro em um único grupo.

desde já, obrigado

query-performance

1 respostas

  1. Best Answer

    Já que você planeja deletar pelo menos metade da tabela de qualquer forma, pode ser mais simples e rápido apenas inserir as linhas que você quer manter em uma nova tabela, remover a tabela antiga e então renomear a nova tabela depois. Então você pode simplificar a consulta para apenas uma única GROUP BYoperação sem nenhuma junção como esta:

    CREATE TABLE TEST_TAB2
AS
(
    SELECT
        activity_type,
        tracking_code,
        MAX(eff_date) AS eff_date
    FROM TEST_TAB
    GROUP BY activity_type, tracking_code
);

    Como você já tem um índice (activity_type, tracking_code), a consulta acima deve ser SARGável e ter bom desempenho.

    • 7

relate perguntas