主页 / dba / 问题 / 343525
Accepted
Pantea
Asked: 2024-11-11 21:10:37 +0800 CST

查询删除包含 4 亿条记录的大表中 eff_date 较低的记录

  • 772

我有一张具有以下结构的表格：

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

该表的示例数据：

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'));

这只是示例数据，原始表中的实际数据量接近 4 亿条记录。我需要做的是，对于每组activity_type, tracking_code，我需要保留具有最高“eff_date”的记录并删除其余记录。因此，对于，activity_type=A and tracking_code = 1我需要保留具有的记录eff_date = 1/2/2024并删除另一个记录。我现在有以下查询：

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
                  )

但是这似乎很慢。您能提出更好的解决方案吗？原始表按 eff_date 进行分区，并在其余两列上建立索引。另一点是，单个组中每条记录的 eff_date 之间可能相差一年以上。

提前致谢

query-performance

1 个回答

  1. Best Answer

    由于您无论如何都打算删除至少一半的表，因此将要保留的行插入新表、删除旧表，然后重命名新表可能更简单、更快捷。然后，您可以将查询简化为单个操作，而无需GROUP BY任何连接，如下所示：

    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
);

    由于您已经有索引(activity_type, tracking_code)，因此上述查询应该是SARGable并且高性能的。

    • 7

相关问题