分段表rowid_表按ROWID切片

ata_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.

上面脚本不能用于分区表,而且有时候我们需要delete，update的数据其实只占据表总行数的30%，利用上面脚本要做很多无用功

可以利用下面脚本

select count(*) total_rows from scott.test 带上where条件; ---求出要DELETE,UPDATE的总行数，然后运行下面SQL，输入总行数，ROWID分片数

select 'where rowid &gt= ''' || rid || ''' and rowid &lt ''' || 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 &lt= &&level

union

select 1 from dual));

结果类似如下：

'WHEREROWID&gt='''||RID||'''ANDROWID&lt'''||LEAD(RID,1,RID)OVER(ORDERBYRID

----------------------------------------------------------------------

where rowid &gt= 'AAASSdAAEAAAAILAAA' and rowid &lt 'AAASSdAAEAAAAJuAAB';

where rowid &gt= 'AAASSdAAEAAAAJuAAB' and rowid &lt 'AAASSdAAEAAAAJ5AAm';

where rowid &gt= 'AAASSdAAEAAAAJ5AAm' and rowid &lt 'AAASSdAAEAAAAKGAA2';

where rowid &gt= 'AAASSdAAEAAAAKGAA2' and rowid &lt 'AAASSdAAEAAAAMRAAU';

where rowid &gt= 'AAASSdAAEAAAAMRAAU' and rowid &lt 'AAASSdAAEAAAAMoAAq';

where rowid &gt= 'AAASSdAAEAAAAMoAAq' and rowid &lt 'AAASSdAAEAAAANKAAo';

where rowid &gt= 'AAASSdAAEAAAANKAAo' and rowid &lt 'AAASSdAAEAAAAOCAAb';

where rowid &gt= 'AAASSdAAEAAAAOCAAb' and rowid &lt 'AAASSdAAEAAAAPbAAS';

where rowid &gt= 'AAASSdAAEAAAAPbAAS' and rowid &lt 'AAASSdAAEAAAASOAA6';

where rowid &gt= 'AAASSdAAEAAAASOAA6' and rowid &lt 'AAASSdAAEAAAAaiAAE';

where rowid &gt= 'AAASSdAAEAAAAaiAAE' and rowid &lt 'AAASSdAAEAAAAaiAAE'; ----最后一个ROWID 需要改一下，把&lt改写为&lt=

11 rows selected.

然后自己带入条件，在多个SESSION运