分段表rowid_通过rowid切片优化大表的update和delete，解决undo不够的问题！

SQL> select count(*) from test_b;

COUNT(*)

----------

1465120

SQL> 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) || '''' ||';'

2 from (select distinct b.rn,

3 first_value(a.fid) over(partition by b.rn order by a.fid, a.bid rows between unbounded preceding and unbounded following) fid1,

4 last_value(a.fid) over(partition by b.rn order by a.fid, a.bid rows between unbounded preceding and unbounded following) fid2,

5 first_value(decode(sign(range2 - range1),

6 1,

7 a.bid +

8 ((b.rn - a.range1) * a.chunks1),

9 a.bid)) over(partition by b.rn order by a.fid, a.bid rows between unbounded preceding and unbounded following) bid1,

10 last_value(decode(sign(range2 - range1),

11 1,

12 a.bid +

13 ((b.rn - a.range1 + 1) * a.chunks1) - 1,

14 (a.bid + a.blocks - 1))) over(partition by b.rn order by a.fid, a.bid rows between unbounded preceding and unbounded following) bid2

15 from (select fid,

16 bid,

17 blocks,

18 chunks1,

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

20 trunc((sum2 - 0.1) / chunks1) range2

21 from (select /*+ rule */

22 relative_fno fid,

23 block_id bid,

24 blocks,

25 sum(blocks) over() sum1,

26 trunc((sum(blocks) over()) / &&rowid_ranges) chunks1,

27 sum(blocks) over(order by relative_fno, block_id) sum2

28 from dba_extents

29 where segment_name = upper('&&segment_name')

30 and owner = upper('&&owner'))

31 where sum1 > &&rowid_ranges) a,

32 (select rownum - 1 rn

33 from dual

34 connect by level <= &&rowid_ranges) b

35 where b.rn between a.range1 and a.range2) c,

36 (select max(data_object_id) oid

37 from dba_objects

38 where object_name = upper('&&segment_name')

39 and owner = upper('&&owner')

40 and data_object_id is not null) d

41 /

Enter value for rowid_ranges: 10

old 26: trunc((sum(blocks) over()) / &&rowid_ranges) chunks1,

new 26: trunc((sum(blocks) over()) / 10) chunks1,

Enter value for segment_name: test_b

old 29: where segment_name = upper('&&segment_name')

new 29: where segment_name = upper('test_b')

Enter value for owner: scott

old 30: and owner = upper('&&owner'))

new 30: and owner = upper('scott'))

old 31: where sum1 > &&rowid_ranges) a,

new 31: where sum1 > 10) a,

old 34: connect by level <= &&rowid_ranges) b

new 34: connect by level <= 10) b

old 38: where object_name = upper('&&segment_name')

new 38: where object_name = upper('test_b')

old 39: and owner = upper('&&owner')

new 39: and owner = upper('scott')

'WHEREROWIDBETWEEN'''||SYS.DBMS_ROWID.ROWID_CREATE(1,D.OID,C.FID1,

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

where rowid between 'AAAXFqAAGAAADrIAAA' and 'AAAXFqAAGAAAEr/CcP';

where rowid between 'AAAXFqAAGAAAEsAAAA' and 'AAAXFqAAGAAAFT/CcP';

where rowid between 'AAAXFqAAGAAAFUAAAA' and 'AAAXFqAAGAAAF7/CcP';

where rowid between 'AAAXFqAAGAAAF8AAAA' and 'AAAXFqAAGAAAGj/CcP';

where rowid between 'AAAXFqAAGAAAGkAAAA' and 'AAAXFqAAGAAAHr/CcP';

where rowid between 'AAAXFqAAGAAAHsAAAA' and 'AAAXFqAAGAAAHz/CcP';

where rowid between 'AAAXFqAAGAAAH0AAAA' and 'AAAXFqAAGAAAI7/CcP';

where rowid between 'AAAXFqAAGAAAI8AAAA' and 'AAAXFqAAGAAAJD/CcP';

where rowid between 'AAAXFqAAGAAAJEAAAA' and 'AAAXFqAAGAAAKL/CcP';

where rowid between 'AAAXFqAAGAAAKMAAAA' and 'AAAXFqAAGAAAKT/CcP';

10 rows selected.