In the OTN forum, Jonathan Lewis recently asked for an efficient SQL solution to split a table into 12 ROWID ranges having the same (+-1) number of blocks. I’m posting here a slightly cleaned-up version of my answer so I can change it if necessary after the question gets archived.
If you stop at the ANSWER clause, you will see the number of blocks in each bucket.
If you stop at the ROWIDS clause, you will get the ROWID ranges which are the point of the exercise.
The final SELECT is for testing purposes: it compares the number of rows in the table to the sum of the rows in each bucket. Obviously the two numbers had better be equal or else there is a bug. If you find a bug, let me know – gently ;)
define d_owner = 'SYS' define d_table_name = 'SOURCE$' define d_num_buckets = 12 column obj# new_value d_object_id select obj# from sys.obj$ where name = '&d_table_name' and owner# = (select user# from sys.user$ where name = '&d_owner'); with extents as ( select block_id, file_id, sum(blocks) over() total_blocks, sum(blocks) over() / &d_num_buckets blocks_per_bucket, sum(blocks) over(order by file_id, block_id) - blocks cumul_prev_blocks, sum(blocks) over(order by file_id, block_id) cumul_current_blocks from dba_extents where (owner, segment_name) = (('&d_owner', '&d_table_name')) ) , extents_with_buckets as ( select width_bucket(cumul_prev_blocks, .5, total_blocks + .5, &d_num_buckets) prev_bucket, width_bucket(cumul_prev_blocks+1, .5, total_blocks + .5, &d_num_buckets) first_bucket, width_bucket(cumul_current_blocks, .5, total_blocks + .5, &d_num_buckets) last_bucket, e.* from extents e ) , selected_extents as ( select * from extents_with_buckets where cumul_current_blocks = round(last_bucket*blocks_per_bucket) or prev_bucket < last_bucket ) , expanded_extents as ( select first_bucket + level - 1 bucket, case level when 1 then cumul_prev_blocks else round((first_bucket + level - 2) * blocks_per_bucket) end start_blocks, case first_bucket + level - 1 when last_bucket then cumul_current_blocks - 1 else round((first_bucket + level - 1) * blocks_per_bucket) - 1 end end_blocks, e.* from selected_extents e connect by cumul_prev_blocks = prior cumul_prev_blocks and first_bucket + level -1 <= last_bucket and prior sys_guid() is not null ) , answer as ( select bucket, min(file_id) first_file_id, min(block_id + start_blocks - cumul_prev_blocks) keep (dense_rank first order by cumul_prev_blocks) first_block_id, max(file_id) last_file_id, max(block_id + end_blocks - cumul_prev_blocks) keep (dense_rank last order by cumul_prev_blocks) last_block_id, max(end_blocks) + 1 - min(start_blocks) blocks from expanded_extents group by bucket ) , rowids as ( select dbms_rowid.rowid_create( 1, &d_object_id, first_file_id, first_block_id, 0 ) rowid_start, dbms_rowid.rowid_create( 1, &d_object_id, last_file_id, last_block_id, 9999 ) rowid_end from answer order by bucket ) select 'select count(*) cnt from &d_owner..&d_table_name union all select sum(cnt) from (' txt from dual union all select 'select count(*) cnt from &d_owner..&d_table_name where rowid between chartorowid(''' || rowid_start || ''') and chartorowid(''' || rowid_end || ''')' || case when lead(rowid_start) over(order by rowid_start) is null then ');' else ' union all' end test_sql from rowids;