通过rowid切片对大表进行删除修改操作

对一个大表进行UPDATE,DELETE，如果在一个SESSION里面运行SQL，很容易引发undo不够，或者由于一些原因，导致回滚，这个是灾难我们可以对表按照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) bid2from (select fid,bid,blocks,chunks1,trunc((sum2 - blocks + 1 - 0.1) / chunks1) range1,trunc((sum2 - 0.1) / chunks1) range2from (select 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) sum2from dba_extentswhere segment_name = upper('&&segment_name')and owner = upper('&&owner'))where sum1 > &&rowid_ranges) a,(select rownum - 1 rnfrom dualconnect by level <= &&rowid_ranges) bwhere b.rn between a.range1 and a.range2) c,(select max(data_object_id) oidfrom dba_objectswhere 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';上面脚本不能用于分区表,而且有时候我们需要delete，update的数据其实只占据表总行数的30%，利用上面脚本要做很多无用功。可以利用下面脚本：select count(*) total_rows from scott.test 带上where条件;---求出要DELETE,UPDATE的总行数，然后运行下面SQL，输入总行数，ROWID分片数select 'where rowid >= ''' || rid || ''' and rowid < ''' ||lead(rid, 1, rid) over(order by rid) || ''';'from (select rn, ridfrom (select rownum rn, rowid rid, count(*) over() total_rowsfrom scott.test 带上where条件order by rowid)where rn in (select trunc(&&total_rows / level)from dualconnect by level <= &&levelunionselect 1 from dual));结果类似如下：                                                                                                                    'WHEREROWID>='''||RID||'''ANDROWID<'''||LEAD(RID,1,RID)OVER(ORDERBYRID----------------------------------------------------------------------where rowid >= 'AAASSdAAEAAAAILAAA' and rowid  < 'AAASSdAAEAAAAJuAAB';where rowid >= 'AAASSdAAEAAAAJuAAB' and rowid  < 'AAASSdAAEAAAAJ5AAm';where rowid >= 'AAASSdAAEAAAAJ5AAm' and rowid  < 'AAASSdAAEAAAAKGAA2';where rowid >= 'AAASSdAAEAAAAKGAA2' and rowid  < 'AAASSdAAEAAAAMRAAU';where rowid >= 'AAASSdAAEAAAAMRAAU' and rowid  < 'AAASSdAAEAAAAMoAAq';where rowid >= 'AAASSdAAEAAAAMoAAq' and rowid  < 'AAASSdAAEAAAANKAAo';where rowid >= 'AAASSdAAEAAAANKAAo' and rowid  < 'AAASSdAAEAAAAOCAAb';where rowid >= 'AAASSdAAEAAAAOCAAb' and rowid  < 'AAASSdAAEAAAAPbAAS';where rowid >= 'AAASSdAAEAAAAPbAAS' and rowid  < 'AAASSdAAEAAAASOAA6';where rowid >= 'AAASSdAAEAAAASOAA6' and rowid  < 'AAASSdAAEAAAAaiAAE';where rowid >= 'AAASSdAAEAAAAaiAAE' and rowid  < 'AAASSdAAEAAAAaiAAE';    ---最后一个ROWID 需要改一下，把<改写为<=然后自己带入条件，在多个SESSION运行SQL。