Oracle blog

How to split a table into rowid ranges.

This script helps immensely to copy any table with "out of row" lob columns in it across dblink. This can also be used for CTAS over dblinks, any parallel insert/delete/update operations.

This is not a duplicate to what Oracle parallel query option does, it basically fills in the gaps of Oracle parallel query option.

For example:

Out of row lobs takes hell lot of time to be copied over to another table, Oracle parallel query doesn’t help here across dblinks.

You can’t get parallel queries on both source and target tables of CTAS operation across db links.

Good amount of limitations on Oracle parallel DML operations.

By using this script you can split any table into up to 255 rowid ranges.

-- This script spits desired number of rowid ranges to be used for any parallel operations.
-- Best to use it for copying a huge table with out of row lob columns in it or CTAS/copy the data over db links.
-- This can also be used to simulate parallel insert/update/delete operations.
-- Maximum number of rowid ranges you can get here is 255.
-- Doesn't work for partitioned tables, but with minor changes it can be adopted easily.

-- Doesn't display any output if the total table blocks are less than rowid ranges times 128.

-- It can split a table into more ranges than the number of extents

set verify off
undefine rowid_ranges
undefine seg_name
undefine owner
set head off
set pages 0
set trimspool on

select decode(rn3, &&rowid_ranges, 'rowid >= ''', 'rowid between ''')||
sys.dbms_rowid.rowid_create(1, id, file_id, new_bid, 0)||
decode(rn3, &&rowid_ranges, '''', ''' and ''')||
decode(rn3, &&rowid_ranges, null, sys.dbms_rowid.rowid_create(1, id, fid2, bid2-1, 99999))||
decode(rn3, &&rowid_ranges, null, '''') from (select id, file_id, new_bid, rn3, lead(file_id) over (order by rn3) fid2,
lead(new_bid) over (order by rn3) bid2
from
(select file_id, block_id, rn3,
round(block_id + (equi_blks * (rn5 - 1))) new_bid, id
from
(select file_id, block_id, equi_blks, rn3,
row_number() over (partition by rn1 order by rn3) rn5
from
(select file_id, block_id, equi_blks, rn1, rn3,
row_number() over (partition by rn3 order by rn1) rn4
from
(select file_id, block_id, sum2, equi_blks, rn1
from
(select file_id, block_id, sum2, rn1, equi_blks,
row_number() over (partition by rn1 order by sum2) rn2
from
(select file_id, block_id, sum1, sum2, (sum1/&&rowid_ranges) equi_blks,
trunc((sum2 * &&rowid_ranges)/(sum1 + 1))+1 rn1
from
(select relative_fno file_id, block_id, sum(blocks) over () sum1,
sum(blocks) over (order by relative_fno, block_id) sum2
from dba_extents
where
segment_name = upper('&&seg_name') and
owner = upper('&&owner') and
256 > &&rowid_ranges
)
where
sum1 > (128 * &&rowid_ranges)
)
)
where rn2 = 1
) a,
(select rownum rn3 from (select 1 from dual group by cube (1, 1, 1, 1, 1, 1, 1, 1))
where rownum <= &&rowid_ranges ) b where b.rn3 <= a.rn1 ) where rn4 = 1 ) c, (select max(data_object_id) id from dba_objects where object_name = upper('&seg_name') and owner = upper('&owner') and data_object_id is not null ) d ) ) /

-------------------------> End of the sql script
-------------------------> <-----------------------

--- Update on April 28th 2009, above script has a bug, please use the corrected script below:

select
'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 /*+ rule */
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
/

posted by Saibabu Devabhaktuni @ 8:20 PM