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

Question

我管理一个应用程序，它有一个非常大的(将近1TB的数据，在一个表中有超过5亿行) Oracle数据库后端。数据库实际上不做任何事情(没有SProcs，没有触发器或任何东西)--它只是一个数据存储。

每个月我们都必须清除两张主表中的记录。清除的标准是不同的，是行龄和几个状态字段的组合。我们通常每月清除1,000万至5,000万行(通过进口，每周增加约3-5百万行)。

目前，我们必须分批删除大约50,000行(即。删除50000，comit，删除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天的维护窗口。

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

Answer 1

带有'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;

Answer 2

这方面的经典解决方案是隔断您的表，例如按月或按周。如果您以前没有遇到过这些表，那么在选择时，分区表就像几个具有隐式UNION的结构相同的表，并且在根据分区条件插入分区表时，它会自动将一行存储在适当的分区中。您提到了索引--每个分区也有自己的分区索引。在Oracle中，删除分区是一项非常廉价的操作(在加载方面它类似于TRUNCATE，因为这就是真正要做的--截断或删除这些不可见的子表之一)。这将是一个很大的处理“事后”，但没有意义的哭溢出牛奶-这样做的好处远远超过成本。每个月，您都会拆分顶部的分区，为下一个月的数据创建一个新的分区(您可以使用DBMS_JOB轻松地实现自动化)。

对于分区，您还可以利用并行查询和分区消除，这将使用户非常高兴.

Answer 3

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

这对分区的有用性有影响。假设你的名字上有索引。一个标准的Btree索引，全部在一个段中，可能需要执行四次跳转才能从根块到叶块，第五次读取才能得到行。如果该索引被划分为50个段，并且没有分区键作为查询的一部分，那么这50个段中的每一个都需要检查。每个片段都会更小，所以您可能只需要执行2次跳转，但是您可能仍然会执行100次读取，而不是前5次。

如果它们是位图索引，则方程是不同的。您可能不是使用索引来标识单个行，而是使用它们的集合。因此，它使用的不是使用5个IOs返回单个记录的查询，而是使用10000个IOs。因此，索引的额外分区中的额外开销并不重要。