1-1 删除大表(非分区表)中的部分数据 / 如何执行大事务（update,delete)

 

大事务

对一个大表进行UPDATE,DELETE，如果在一个SESSION里面运行SQL，很容易引发undo不够，或者由于一些原因，导致回滚，这个是灾难

大批量删除的时候，要把索引禁掉，因为维护索引的时候是单块读

1.大事务产生会产生大量的undo
2.考虑删除表的数据，维护索引的成本

　　

 方法一： ROWID 分片

我们可以对表按照ROWID分片，然后开启多个进程并行的运行，这样既能提升处理速度，还能减少undo，还能防止死事物恢复太慢。

下面脚本(不支持分区表)要求输入3个参数
1.ROWID分片个数
2.表名字
3.OWNER

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
/

 

例如，输出10个ROWID切片，我们想要进行DELETE,UPDATE等等，只需要修改一下SQL，同时运行10个SQL即可。

'WHEREROWIDBETWEEN'''||SYS.DBMS_ROWID.ROWID_CREATE(1,D.OID,C.FID1,
------------------------------------------------------------------
where rowid between 'AAASSdAAEAAAAIIAAA' and 'AAASSdAAEAAAALqCcP';
where rowid between 'AAASSdAAEAAAALrAAA' and 'AAASSdAAEAAAANyCcP';
where rowid between 'AAASSdAAEAAAANzAAA' and 'AAASSdAAEAAAAPyCcP';
where rowid between 'AAASSdAAEAAAAPzAAA' and 'AAASSdAAEAAAARyCcP';
where rowid between 'AAASSdAAEAAAARzAAA' and 'AAASSdAAEAAAATyCcP';
where rowid between 'AAASSdAAEAAAATzAAA' and 'AAASSdAAEAAAAVyCcP';
where rowid between 'AAASSdAAEAAAAVzAAA' and 'AAASSdAAEAAAAXyCcP';
where rowid between 'AAASSdAAEAAAAXzAAA' and 'AAASSdAAEAAAAZyCcP';
where rowid between 'AAASSdAAEAAAAZzAAA' and 'AAASSdAAEAAAAbyCcP';
where rowid between 'AAASSdAAEAAAAbzAAA' and 'AAASSdAAEAAAAdlCcP';

10 rows selected.