Friday, March 10, 2006

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
/


10 Comments:

At February 27, 2008 at 11:50:00 PM PST, Anonymous Anonymous said...

Your solution seems to return invalid rowids. I am just wondering if there is anything you forgot to include. I have tried to split a 600 million row table based on your publised solution. It constantly gives me Invalid Rowids. I checked the table for some ROWIDs and I confirmed that some of the rowids match with your solution, but most of them DON'T.

Any ideas ?

thanks

Nagesh

 
At October 6, 2008 at 8:33:00 PM PDT, Blogger Unknown said...

I found similar code that works here: http://pages.videotron.com/orautils/pages/split_table_by_extent_range.htm

I tested it on 5million rows and it added up, where the code on this page didn't.

 
At April 28, 2009 at 12:30:00 AM PDT, Blogger Saibabu Devabhaktuni said...

Nagesh/Kelly,

Thanks for your note. Apologies for late reply, here is the corrected script.

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
/

 
At November 3, 2009 at 11:32:00 PM PST, Anonymous Anonymous said...

This comment has been removed by a blog administrator.

 
At December 20, 2009 at 1:10:00 AM PST, Anonymous Anonymous said...

Can anyone recommend the robust Script Deployment system for a small IT service company like mine? Does anyone use Kaseya.com or GFI.com? How do they compare to these guys I found recently: N-able N-central desktop management
? What is your best take in cost vs performance among those three? I need a good advice please... Thanks in advance!

 
At February 20, 2010 at 10:35:00 AM PST, Anonymous Anonymous said...

Good brief and this mail helped me alot in my college assignement. Gratefulness you on your information.

 
At March 13, 2010 at 9:08:00 PM PST, Anonymous Anonymous said...

Good dispatch and this fill someone in on helped me alot in my college assignement. Thanks you for your information.

 
At March 19, 2010 at 12:25:00 PM PDT, Blogger Unknown said...

Thank you for the code.

Btw, DBMS_PARALLEL_EXECUTE.create_chunks_by_rowid in 11gR2 do the same, doesn't it ?

http://www.oracle-base.com/articles/11g/dbms_parallel_execute_11gR2.php

 
At March 21, 2010 at 6:58:00 AM PDT, Anonymous Anonymous said...

Hello. And Bye.

 
At October 11, 2011 at 9:46:00 AM PDT, Anonymous Anonymous said...

Hello. And bye?

Thats random ;)

 

Post a Comment

<< Home