文章转自：
http://blog.sina.com.cn/s/blog_747f4c1d0102w5bf.html

  对一个大表进行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) bid2

from (select fid,

bid,

blocks,

chunks1,

trunc((sum2 - blocks + 1 - 0.1) / chunks1) range1,

trunc((sum2 - 0.1) / chunks1) range2

from (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) 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';

上面脚本不能用于分区表,而且有时候我们需要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, rid

from (select rownum rn, rowid rid, count(*) over() total_rows

from scott.test 带上where条件

order by rowid)

where rn in (select trunc(&&total_rows / level)

from dual

connect by level <= &&level

union

select 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。

ITPub博客

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

最新文章