我管理的应用程序具有非常大的(几乎1TB的数据,在一个表中有超过5亿行)Oracle数据库后端.数据库并没有真正做任何事情(没有SProcs,没有触发器或任何东西)它只是一个数据存储.
每个月我们都需要从两个主表中清除记录.清除的标准各不相同,是行年龄和几个状态字段的组合.我们通常最终每月清除1000到5000万行(我们通过导入每周增加约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天的维护时间.
带有“A”和“B”的逻辑可能会隐藏在可以进行分区的虚拟列后面：
原文链接：https://www.f2er.com/oracle/205381.htmlalter 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 '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,data) values
('A',date '2000-06-16',date '2010-01-01','A and 2000-06-16 is older than 3 yrs,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;
