一、 大表 delete 背景需求
某用户的数据库存在一个交易账单明细表，存放近 3 年的交易账单明细信息，现有数据量约 120 G ，共计 1700w 行数据记录，由于用户业务要求及提高查询效率的关系，仅需要保留近一个月的订单明细信息即可，其余信息将不再提供给业务用户进行查询，现希望对此大表进行裁剪，按表内提供的时间字段进行筛选删除，并不干扰正常业务运行。
二、 大表 delete 效率分析
经过对数据库的查询发现 1600 w + 的数据都需要被删除，改表在开药等产生交易流水的过程中都会不断的产生数据，如果采用 create table XXX 账单交易明细 _temp as select * from XXX 账单交易明细 where create_date > sysdate-30 的方式捞出需求数据再进行 truncate table 操作存在两点问题， 1 是存在业务中断， 2 是很难保证在 create table 后无新数据产生。所以排除这种做法。
为避免业务中断和数据的丢失，采用删除数据的方式进行处理。基本的执行 sql 为 delete from XXX 账单交易明细 where create _date < sysdate-30 。但是对于这样的一个 sql 执行依然存在着一些问题。
通过上述的环境可知 sql 删除的数据量过大，众所周知， oracle 在进行大事务处理的时候回产生大量的 undo 和 redo 数据，这中间产生的数据量可能已经超过了表本身的数据量，对于一个大的事务执行来说，中途无法断执行，一旦执行被中断，又会消耗大量的 io 去进行数据的回写，这样对于整体的性能消耗非常严重，也可能对现有业务冲击较大。占用正常业务量使用的 io 情况。这种情况其实就意味着如果将这么一个大表删除当成是一个大的事务来处理是非常糟糕的一个事情，因为中途如果产生业务的卡顿问题，中途想停止作业也是不可能的，因为中途停止造成的数据 rollback 可能是对业务系统更加大的冲击。综上所述对于这个删除作业来说应该将以此大的事务切割成小的事务来进行完成。那么一个表删除切割删除可以根据根据 id %100 这种方式来进行删除，比如说 d elete from XXX 账单交易明细 where create _date < sysdate-30 and mod(id,100) = 1; 进行事务的切割删除，然后可以同时跑多个进程删除 mod(id,100) = 2 ， 3 等等后面的数据进行加速删除任务。但是这种切片其实存在一个问题就是事务槽的争用。当执行多个进程并行任务的时候，可能多个进程会去同时争抢一个块上的事务槽，因为这种取模的切割方法无法很好的分配每个进程只扫描固定的一些区域，事实上，很多情况下相当于每个进程都要去扫描这个表里面的全部内容，这种事务的争抢和重复扫描的情况在并行多进程处理这种事务的时候有可能比单一进程处理快不了多少。因此我们可以考虑通过 dba _extends 这个视图查询这个表数据所在的物理块上进行事务的切割。查询分隔的 sql 如下：
select 'where rowid between ''' ||sys.dbms_rowid.rowid_create(1, d.oid, c.fid1, c.bid1, 0) ||''' and ''' ||sys.dbms_rowid.rowid_create(1, d.oid, c.fid2, c.bid2, 9999) || '''' ||';'
from (select distinct b.rn, first_value(a.fid) over(partition by b.rn order by a.fid, a.bid rows between unbounded preceding and unbounded following) fid1,
last_value(a.fid) over(partition by b.rn order by a.fid, a.bid rows between unbounded preceding and unbounded following) fid2,
first_value(decode(sign(range2 - range1),
1,
a.bid +
((b.rn - a.range1) * a.chunks1),
a.bid)) over(partition by b.rn order by a.fid, a.bid rows between unbounded preceding and unbounded following) bid1,
last_value(decode(sign(range2 - range1),
1,
a.bid +
((b.rn - a.range1 + 1) * a.chunks1) - 1,
(a.bid + a.blocks - 1))) over(partition by b.rn order by a.fid, a.bid rows between unbounded preceding and unbounded following) bid2
from (select fid,
bid,
blocks,
chunks1,
trunc((sum2 - blocks + 1 - 0.1) / chunks1) range1,
trunc((sum2 - 0.1) / chunks1) range2
from (select /*+ rule */
relative_fno fid,
block_id bid,
blocks,
sum(blocks) over() sum1,
trunc((sum(blocks) over()) / &&rowid_ranges) chunks1,
sum(blocks) over(order by relative_fno, block_id) sum2
from dba_extents
where segment_name = upper('&&segment_name')
and owner = upper('&&owner'))
where sum1 > &&rowid_ranges) a,
(select rownum - 1 rn
from dual
connect by level <= &&rowid_ranges) b
where b.rn between a.range1 and a.range2) c,
(select max(data_object_id) oid
from dba_objects
where object_name = upper('&&segment_name')
and owner = upper('&&owner')
and data_object_id is not null) d;
通过上述的 sql 执行时输入用户名、表名、切割片数来对一个大表进行裁剪，执行后能得出一个 rowid 的范围值如下图：
通过对删除的 sql 中加上 rowid 的范围删选，能够清晰的指定出每一个删除任务的进程扫描物理文件的范围，且单一物理块上作业的删除进程有且仅有一个，从而减少每个进程所需要扫描的数据量和减少事务槽争用的情况。通过上述方法顺利对 1700 W 行的大表进行了数据清理工作，同时也没有反馈作业过程中有业务卡顿的情况。
三、 r ow id 切片删除限制
虽然基于 rowid 切片的技术可以实现将大的批量删除动作切割成多个小事务，并减少不同并发之间的事务槽争用问题，但并不能说明该技术没有缺陷或者说适用于所有主流场景，下列将针对该技术的某些限制，做一下简要说明。
1、 rowid 切片不连续问题，其实通过查看这些 sql 不难发现这种 rowid 切片的逻辑是通过查找表所在的 r file # 和 block # 来构造出一个 rowid ，从而指定这个 rowid 的范围，但是中间的逻辑并非是实实在在的 rowid ，而是一个虚构的，这个虚构的过程中有些语句会在成以下的切片情况：
rowid 范围
切片 1 |---------------------------------------------|
切片 2 |------------------------------------------------|
中间的 rowid 其实是有交集的。之前就碰到的一个类似案例，是将一个大表迁移到一个分区表上面去，当时使用这种 rowid 的切片语句来进行加速运行，导致的问题就是表中有重复的数据，因为这个 rowid 的不连续问题，导致了有部分块是被重复进行拷贝了，在新的表上面又没有主键索引，导致业务上线运行了一段时间才发现这样的问题。
2 、 rowid 切片的分区不支持需要切片的表其本身是分区表，其实通过上面的 sql 很容易就发现，这个 segment _name 就是需要切片的表名，但是如果这个表本身就是分区表，他的段的名字就不能是表名了。