通过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) 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。