Miles -- Thanks for the question regarding "On the use of DBMS_ROWID.ROWID_ROW_NUMBER function", version 8.1.7

Submitted on 1-Feb-2003 15:00 Central time zone

Tom's latest followup | Bookmark | Bottom

Last updated 27-Jul-2007 8:49

You Asked

Hi Tom,
Great book!, buying your book is one of the best investment I ever made.
I want your opinion of this, we have a large batch process that takes hours to execute 
and which fetches (full table scan) on a joined of two huge table.  We only have a small 
window to run this large batch process and so we are thinking of breaking this batch 
process into several batch process and executing each in parallel by using 
DBMS_ROWID.ROWID_ROW_NUMBER during data retrieval as follows:

SELECT a.*, b.*
  FROM large_table1 a,
       large_table2 b
 Where a.key = b.key
   And Mod(Dbms_Rowid.Rowid_Row_Number(a.rowid, Max_Process) = threadNo_in;
Where:
Max_Process is the number of batch process to be executed in parallel
(e.g. 4  -- start for 4 batch job)
threadNo_in is the thread / batch process for this batch job, say 0 - indicating that 
this session's batch job is 0. 

Using the Mod function on the Dbms_Rowid.rowid_row_number we intend to group the records 
of the large_table1 into say thread 0, 1, 2, 3, and start a separate session for each 
thread, effectively executing each process in parallel.  

My question is, is this a safe implementation of the DBMS_ROWID.ROWID_ROW_NUMBER 
function?  Can you see any potential problem with this?  Maybe you can suggest something 
else.

and we said...

I would not do that, you want to break the data up by EXTENTS or FILES.  Having them each 
do "every other row" would have the entire table "hot" and you wouldn't be able to use 
the rowids to actually access the data.

you would generate N rowid pairs that "covered the table"

and then query:

SELECT a.*, b.*
  FROM ( select * from large_table1 where rowid between :x and :y ) a,
       large_table2 b
 Where a.key = b.key


so you would use rowid ranges.  Here is an example of how to generate by file -- you can 
use extents, whatever suits you best:

ops$tkyte@ORA920> create tablespace test
  2  datafile size 1088k
  3  autoextend off
  4  /

Tablespace created.

ops$tkyte@ORA920> alter tablespace test add datafile size 1088k autoextend off;

Tablespace altered.

ops$tkyte@ORA920> alter tablespace test add datafile size 1088k autoextend off;

Tablespace altered.

ops$tkyte@ORA920>
ops$tkyte@ORA920> create table t ( x int, y char(255) ) tablespace test;


Table created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> begin
  2          for i in 1 .. 100000000
  3          loop
  4                  insert into t values ( i, i );
  5                  commit;
  6          end loop;
  7  end;
  8  /
begin
*
ERROR at line 1:
ORA-01653: unable to extend table OPS$TKYTE.T by 128 in tablespace TEST
ORA-06512: at line 4


ops$tkyte@ORA920> select dbms_rowid.rowid_relative_fno(rowid), count(*)
  2    from t
  3   group by dbms_rowid.rowid_relative_fno(rowid)
  4  /

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)   COUNT(*)
------------------------------------ ----------
                                  15       3456
                                  16       3429
                                  17       3456


Ok, so you want to process the rows in there file by file -- in separate 
threads/processes.  You would feed them rowid ranges:

ops$tkyte@ORA920> select 'select count(*) from t where rowid between 
chartorowid(''' ||
  2         dbms_rowid.rowid_create( 1, user_objects.data_object_id,
  3                                  dba_data_files.RELATIVE_FNO, 1, 0 )
  4         || ''') and chartorowid(''' ||
  5         dbms_rowid.rowid_create( 1, user_objects.data_object_id,
  6                   dba_data_files.RELATIVE_FNO, dba_data_files.blocks+1, 1 )
  7                                  || ''');'
  8  from user_objects, dba_data_files
  9  where user_objects.object_name = 'T'
 10  and dba_data_files.tablespace_name = 'TEST'
 11  /

'SELECTCOUNT(*)FROMTWHEREROWIDBETWEENCHARTOROWID('''||DBMS_ROWID.ROWID_CREATE(1,U
SER_OBJECTS.DATA_OBJECT_ID,DBA_DAT
---------------------------------------------------------------------------------
----------------------------------
select count(*) from t where rowid between chartorowid('AAAIWLAAPAAAAABAAB') and 
chartorowid('AAAIWLAAPAAAACJAAB');
select count(*) from t where rowid between chartorowid('AAAIWLAAQAAAAABAAB') and 
chartorowid('AAAIWLAAQAAAACJAAB');
select count(*) from t where rowid between chartorowid('AAAIWLAARAAAAABAAB') and 
chartorowid('AAAIWLAARAAAACJAAB');


then they would only scan their relevant files, never hitting anyone elses.  of 
course you can take this down to the extent level if you desire, just set up any 
covering -- yet non-overlapping -- rowid range.

Reviews

On the use of DBMS_ROWID.ROWID_ROW_NUMBER function February 3, 2003 - 3pm Central time zone

Bookmark | Bottom | Top

Reviewer: Miles from CA, USA

Thanks for tip, Tom.  
Still in connection to this topic, but what if there's only one relative file number for the table? 
 Does this mean I need to break the data by EXTENTs Level?  Can you please give me an example of 
breaking data by extent level?

Followup February 3, 2003 - 3pm Central time zone:

Ok, it was fun to write - so here goes:

this is for a table in a SINGLE file, breaks it into 4 "more or less" equi ranges:


select relative_fno, min_block start_block, max_block+blocks-1 end_block,   
       sum_blocks,
       dbms_rowid.rowid_create( 1, data_object_id, relative_fno, min_block, 0 ) 
       min_rid,
       dbms_rowid.rowid_create( 1, data_object_id, relative_fno, 
                                   max_block+blocks-1, 10000 ) max_rid
  from (
select relative_fno,
       block_id,
       min(block_id) over (partition by grp) min_block,
       max(block_id) over (partition by grp) max_block,
       blocks,
       sum(blocks) over (partition by grp) sum_blocks
  from (
select relative_fno,
       block_id,
       blocks,
       trunc( (sum(blocks) over (order by block_id)-0.01) /
              (sum(blocks) over ()/4) ) grp
  from dba_extents
 where segment_name = 'BIG_TABLE'
   and owner = user order by block_id
       )
       ), (select data_object_id
             from user_objects
            where object_name = 'BIG_TABLE' )
 where block_id = max_block
/


So, how does it work?  we start from the middle:

big_table@ORA920> select relative_fno,
  2         block_id,
  3             blocks,
  4             sum(blocks) over (order by block_id) cum_blocks,
  5             sum(blocks) over () tot_blocks,
  6             trunc( (sum(blocks) over (order by block_id)-0.01) /
  7                    (sum(blocks) over ()/4) ) grp
  8    from dba_extents
  9   where segment_name = 'BIG_TABLE'
 10     and owner = user order by block_id
 11  /

RELATIVE_FNO   BLOCK_ID     BLOCKS CUM_BLOCKS TOT_BLOCKS        GRP
------------ ---------- ---------- ---------- ---------- ----------
          11          9          8          8      13312          0
          11         17          8         16      13312          0
          11         25          8         24      13312          0
          11         33          8         32      13312          0
....
          11      10249       1024      11264      13312          3
          11      11273       1024      12288      13312          3
          11      12297       1024      13312      13312          3

84 rows selected.


We kept a running total of the blocks (cum_blocks) and had the tot number of blocks in the table 
(tot_blocks).  Now, If I take the tot_blocks and divide by 4 -- i get the number of blocks I would 
like optimally in each group.  We take that number and divide it into the running total number of 
blocks and assign a group id GRP of 0,1,2,3 to each group.  Next we:

big_table@ORA920> select relative_fno,
  2         block_id,
  3         min(block_id) over (partition by grp) min_block,
  4         max(block_id) over (partition by grp) max_block,
  5             blocks,
  6             sum(blocks) over (partition by grp) sum_blocks
  7    from (
  8  select relative_fno,
  9         block_id,
 10             blocks,
 11             trunc( (sum(blocks) over (order by block_id)-0.01) /
 12                    (sum(blocks) over ()/4) ) grp
 13    from dba_extents
 14   where segment_name = 'BIG_TABLE'
 15     and owner = user order by block_id
 16         )
 17  /

RELATIVE_FNO   BLOCK_ID  MIN_BLOCK  MAX_BLOCK     BLOCKS SUM_BLOCKS
------------ ---------- ---------- ---------- ---------- ----------
          11          9          9       3209          8       3328
          11         17          9       3209          8       3328
          11         25          9       3209          8       3328
          11         33          9       3209          8       3328
....
          11       8073       6665       8201        128       2560
          11       8201       6665       8201       1024       2560
          11       9225       9225      12297       1024       4096
          11      10249       9225      12297       1024       4096
          11      11273       9225      12297       1024       4096
          11      12297       9225      12297       1024       4096

84 rows selected.

Basically find the minimum block and maximum block per group.  Here I compute sum_blocks as well -- 
just to see how well the data was distributed in the groups.  It'll probably never be perfect (lmts 
with uniform extents and an evenly divisible number of extents would do that)...

Lastly we:

big_table@ORA920> select relative_fno, min_block start_block, max_block+blocks-1 end_block, 
sum_blocks,
  2         dbms_rowid.rowid_create( 1, data_object_id, relative_fno, min_block, 0 ) min_rid,
  3         dbms_rowid.rowid_create( 1, data_object_id, relative_fno, max_block+blocks-1, 10000 ) 
max_rid
  4    from (
  5  select relative_fno,
  6         block_id,
  7         min(block_id) over (partition by grp) min_block,
  8         max(block_id) over (partition by grp) max_block,
  9             blocks,
 10             sum(blocks) over (partition by grp) sum_blocks
 11    from (
 12  select relative_fno,
 13         block_id,
 14             blocks,
 15             trunc( (sum(blocks) over (order by block_id)-0.01) /
 16                    (sum(blocks) over ()/4) ) grp
 17    from dba_extents
 18   where segment_name = 'BIG_TABLE'
 19     and owner = user order by block_id
 20         )
 21             ), (select data_object_id
 22                   from user_objects
 23                          where object_name = 'BIG_TABLE' )
 24   where block_id = max_block
 25  /

RELATIVE_FNO START_BLOCK  END_BLOCK SUM_BLOCKS MIN_RID            MAX_RID
------------ ----------- ---------- ---------- ------------------ ------------------
          11           9       3336       3328 AAAHerAALAAAAAJAAA AAAHerAALAAAA0ICcQ
          11        3337       6664       3328 AAAHerAALAAAA0JAAA AAAHerAALAAABoICcQ
          11        6665       9224       2560 AAAHerAALAAABoJAAA AAAHerAALAAACQICcQ
          11        9225      13320       4096 AAAHerAALAAACQJAAA AAAHerAALAAADQICcQ


that is -- we keep only the row where the block_id = MAX_BLOCK id, that keeps one row PER GROUP.  
From this - we now have the STARTING block, the ENDING BLOCK and are ready to generate the rowids 
for each range.

There you go ;)

Is this how PQ does it February 3, 2003 - 8pm Central time zone

Bookmark | Bottom | Top

Reviewer: Rob from Pittsburgh, PA

Tom:

Is your approach the same approach taken by Parallel Query when it parallelizes a scan of a 
non-partitioned table.

Followup February 4, 2003 - 7am Central time zone:

similar but they take a slightly more sophisticated approach.  But it is fundementally based on 
rowid ranges.

Reader February 3, 2003 - 10pm Central time zone

Bookmark | Bottom | Top

Reviewer: A reader

RELATIVE_FNO   BLOCK_ID  MIN_BLOCK  MAX_BLOCK     BLOCKS SUM_BLOCKS
------------ ---------- ---------- ---------- ---------- ----------
          11          9          9       3209          8       3328

For the same file 11, min_block is 9 and max_block is 3209
The total blocks = 3209 - 9 + 1 = 3201. Could you explain 
how do you get total blocks = 3328

Thanks

Followup February 4, 2003 - 7am Central time zone:

You did not have the entire report.  The last extent has a starting block of 3209, but an ENDING 
block of 3209+128-1 or 3336.  Now, from that we take away 8 blocks (since min_block is 9) and we 
get 3328...

Here is the bigger picture with GRP included:

RELATIVE_FNO   BLOCK_ID  MIN_BLOCK  MAX_BLOCK     BLOCKS SUM_BLOCKS        GRP
------------ ---------- ---------- ---------- ---------- ---------- ----------
          11          9          9       3209          8       3328          0
          11         17          9       3209          8       3328          0
          11         25          9       3209          8       3328          0
          11         33          9       3209          8       3328          0
          11         41          9       3209          8       3328          0
....

          11       3081          9       3209        128       3328          0
          11       3209          9       3209        128       3328          0
          11       3337       3337       6537        128       3328          1
          11       3465       3337       6537        128       3328          1

February 8, 2003 - 2pm Central time zone

Bookmark | Bottom | Top

Reviewer: Miles from Hayward, CA

Excellent!  This is just what we wanted to do.  Thanks! Tom.

Great Start but... September 3, 2003 - 5pm Central time zone

Bookmark | Bottom | Top

Reviewer: Skip from Conway, AR USA

I am really interested in this article because I need to do essentially the same thing (split a 
table for multiple processes).  However, I have a couple of questions.  (ENV 9.2.0.3EE db)

1) Why do counts of rowid ranges force you into single thread?
2) How can I split a table into 4 equal parts?  When I use your technique above I got 25 parts, my 
system can only handle 4 processes running at once.

Thanks

Followup September 4, 2003 - 8am Central time zone:

1) what do you mean "force you into a single thread???"

2) see

http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:10498431232211

it handles rowids across files and can give you "4"

Forced single thread September 4, 2003 - 11am Central time zone

Bookmark | Bottom | Top

Reviewer: Skip from Conway, AR USA

What I mean by forced single thread is that no matter what parallel degree the table is or if I use 
a parallel hint the query is always run with one process showing in v$session.

Followup September 5, 2003 - 1pm Central time zone:

big_table@ORA920> @split BIG_TABLE 4
 
       GRP MIN_RID            MAX_RID
---------- ------------------ ------------------
         0 AAAJirAALAAAAAJAAA AAAJirAALAAADAICcQ
         1 AAAJirAALAAADAJAAA AAAJirAALAAAGQICcQ
         2 AAAJirAALAAAGQJAAA AAAJirAALAAAJQICcQ
         3 AAAJirAALAAAJQJAAA AAAJirAALAAAMgICcQ
 
Elapsed: 00:00:01.88
big_table@ORA920> select /*+ full(big_table) parallel(big_table,8) */
  2  count(*)
  3  from big_table
  4  where rowid between 'AAAJirAALAAAAAJAAA' and 'AAAJirAALAAADAICcQ';
 
  COUNT(*)
----------
    856998
 
Elapsed: 00:00:18.11
big_table@ORA920> set autotrace traceonly explain
big_table@ORA920> /
Elapsed: 00:00:00.01
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=608 Card=1 Bytes=7)
   1    0   SORT (AGGREGATE)
   2    1     SORT* (AGGREGATE)                                                                     
           :Q23000
   3    2       TABLE ACCESS* (FULL) OF 'BIG_TABLE' (Cost=608 Card=8750 Bytes=61250)                
           :Q23000
 
 
   2 PARALLEL_TO_SERIAL            SELECT /*+ PIV_SSF */ SYS_OP_MSR(COUNT(*)) F
                                   ROM (SELECT /*+ NO_EXPAND ROWID(A2) */ A2.RO
                                   WID C0 FROM "BIG_TABLE" PX_GRANULE(0, BLOCK_
                                   RANGE, DYNAMIC)  A2 WHERE A2.ROWID>='AAAJirA
                                   ALAAAAAJAAA' AND A2.ROWID<='AAAJirAALAAADAIC
                                   cQ') A1
 
   3 PARALLEL_COMBINED_WITH_PARENT


that ran in parallel. not all access paths are parallelizable.

October 27, 2003 - 3pm Central time zone

Bookmark | Bottom | Top