主页 / dba / 问题 / 710
Accepted
Coding Gorilla
Asked: 2011-01-20 09:30:55 +0800 CST

在 Oracle 中删除非常大的记录集的最佳方法

  • 772

我管理一个应用程序，它有一个非常大的（近 1TB 的数据，一个表中有超过 5 亿行）Oracle 数据库后端。数据库并没有真正做任何事情（没有 SProcs，没有触发器或任何东西），它只是一个数据存储。

每个月我们都需要从两个主表中清除记录。清除的标准各不相同，是行龄和几个状态字段的组合。我们通常最终每月清除 10 到 5000 万行（我们每周通过导入增加大约 3-5 百万行）。

目前我们必须分批删除大约 50,000 行（即删除 50000、提交、删除 50000、提交、重复）。尝试一次全部删除整个批次会使数据库在大约一个小时内无响应（取决于行数）。像这样批量删除行在系统上非常粗糙，我们通常必须在一周内“在时间允许的情况下”执行此操作；允许脚本连续运行会导致用户无法接受的性能下降。

我相信这种批量删除也会降低索引性能，并有其他影响最终导致数据库性能下降。一张表就有34个索引，索引数据量其实比数据本身还大。

这是我们的一位 IT 人员用来执行此清除的脚本：

BEGIN
LOOP

delete FROM tbl_raw 
  where dist_event_date < to_date('[date]','mm/dd/yyyy') and rownum < 50000;

  exit when SQL%rowcount < 49999;

  commit;

END LOOP;

commit;

END;

这个数据库必须达到 99.99999%，而且我们每年只有 2 天的维护窗口。

我正在寻找一种更好的方法来删除这些记录，但我还没有找到任何方法。有什么建议么？

oracle oracle-11g

6 个回答

  1. Best Answer

    带有“A”和“B”的逻辑可能“隐藏”在可以进行分区的虚拟列后面：

    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;
    • 18

  2. 对此的经典解决方案是对表进行分区，例如按月或按周。如果您以前没有遇到过，分区表就像几个结构相同的表，UNION在选择时带有隐式，Oracle 会根据分区条件在插入时自动将行存储在适当的分区中。您提到了索引-每个分区也都有自己的分区索引。在 Oracle 中删除分区是一项非常便宜的操作（类似于TRUNCATE就负载而言，因为那是您真正在做的事情-截断或删除这些不可见的子表之一）。“事后”进行分区将是大量的处理，但没有必要为溢出的牛奶哭泣——这样做的好处远远超过了成本。每个月，您都会拆分顶部分区，为下个月的数据创建一个新分区（您可以使用 轻松自动化DBMS_JOB）。

    通过分区，您还可以利用并行查询和分区消除，这应该会让您的用户非常高兴......

    • 14

  3. 需要考虑的一个方面是删除性能中有多少来自索引，有多少来自原始表。从表中删除的每条记录都需要从每个 btree 索引中删除相同的行。如果您有 30 多个 btree 索引，我怀疑您的大部分时间都花在了索引维护上。

    这对分区的有用性有影响。假设您有名称索引。一个标准的 Btree 索引，全部在一个段中，可能必须执行四次跳转才能从根块到达叶块，并进行第五次读取才能获得行。如果该索引被划分为 50 个段，并且您没有将分区键作为查询的一部分，则需要检查这 50 个段中的每一个。每个片段会更小，因此您可能只需要进行 2 次跳转，但您最终可能仍会进行 100 次读取，而不是之前的 5 次。

    如果它们是位图索引，则方程式不同。您可能没有使用索引来标识单个行，而是使用它们的集合。因此，不是使用 5 个 IO 来返回单个记录的查询，而是使用 10,000 个 IO。因此，索引的额外分区中的额外开销无关紧要。

    • 4

  4. 每月以 50,000 批删除 5000 万条记录只需 1000 次迭代。如果您每 30 分钟删除 1 次，它应该满足您的要求。运行您发布的查询但删除循环以便它只执行一次的计划任务不应该对用户造成明显的降级。我们在我们的制造工厂中进行的记录量大致相同，几乎 24/7 全天候运行，它满足了我们的需求。实际上，我们每 10 分钟将其扩展为多一点 10,000 条记录，在我们的 Oracle unix 服务器上运行大约需要 1 或 2 秒。

    • 2

  5. 如果磁盘空间不是很宝贵，您可以创建表的“工作”副本，例如my_table_new，使用 CTAS（将表创建为选择），其标准将省略要删除的记录。您可以并行执行创建语句，并使用附加提示使其快速，然后构建所有索引。然后，一旦完成（并经过测试），将现有表my_table_old重命名为并将“工作”表重命名为my_table. 一旦您drop my_table_old purge对摆脱旧桌子的一切感到满意。如果有一堆外键约束，看看dbms_redefinition PL/SQL 包。使用适当的选项时，它将克隆您的索引、约束等。这是AskTom的 Tom Kyte 建议的总结名声。第一次运行后，您可以自动执行所有操作，并且创建表应该更快，并且可以在系统启动时完成，并且应用程序停机时间将被限制在不到一分钟的时间内重命名表。使用 CTAS 将比执行多次批量删除要快得多。如果您没有获得分区许可，这种方法可能特别有用。

    示例 CTAS，保留过去 365 天的数据行和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;
    • 1

  6. 删除分区时，全局索引不可用，需要重建，全局索引的重建将是一个大问题，如果你在线进行，它会很慢，否则你需要停机。无论哪种情况，都不能满足要求。

    “我们通常最终每月清除 10 到 5000 万行”

    我建议使用 PL/SQL 批量删除，我认为几个小时就可以了。

    • 0

相关问题