Friday, November 4, 2011

Spliting a very large table into pieces using rowid nonverlapping ranges

Below you will find a script that spliits a table into pieces in order to effectively process the data.
Just replace [OWNER] with the table owner , [TABLE_NAME] with your table and [CHUNK] with the number of pieces you want.


select grp,
       dbms_rowid.rowid_create( 1, data_object_id, lo_fno, lo_block, 0 ) min_rid,
       dbms_rowid.rowid_create( 1, data_object_id, hi_fno, hi_block, 10000 ) max_rid
  from (
select distinct grp,
       first_value(relative_fno)
        over (partition by grp order by relative_fno, block_id
              rows between unbounded preceding and unbounded following) lo_fno,
       first_value(block_id    )
       over (partition by grp order by relative_fno, block_id
            rows between unbounded preceding and unbounded following) lo_block,
       last_value(relative_fno)
        over (partition by grp order by relative_fno, block_id
        rows between unbounded preceding and unbounded following) hi_fno,
       last_value(block_id+blocks-1)
        over (partition by grp order by relative_fno, block_id
         rows between unbounded preceding and unbounded following) hi_block,
       sum(blocks) over (partition by grp) sum_blocks
  from (
select relative_fno,
       block_id,
       blocks,
       trunc( (sum(blocks) over (order by relative_fno, block_id)-0.01) /
              (sum(blocks) over ()/[CHUNK]) ) grp
  from dba_extents
 where segment_name = upper('[TABLE_NAME]')
   and owner = '[OWNER]'
   order by block_id
       )
       ),
       (select data_object_id from dba_objects where owner='[OWNER]' and object_name = upper('[TABLE_NAME]') )
       order by grp;


References
Tom Kyte's "Efective Oracle by Design"

No comments:

Post a Comment