Skip to Main Content
  • Questions
  • On the use of DBMS_ROWID.ROWID_ROW_NUMBER function

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Miles.

Asked: February 01, 2003 - 3:00 pm UTC

Last updated: January 02, 2013 - 12:32 pm UTC

Version: 8.1.7

Viewed 10K+ times! This question is

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 Tom 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.

Rating

  (28 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

On the use of DBMS_ROWID.ROWID_ROW_NUMBER function

Miles, February 03, 2003 - 3:06 pm UTC

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?




Tom Kyte
February 03, 2003 - 3:51 pm UTC

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

Rob, February 03, 2003 - 8:54 pm UTC

Tom:

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

Tom Kyte
February 04, 2003 - 7:25 am UTC

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

Reader

A reader, February 03, 2003 - 10:51 pm UTC

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

Tom Kyte
February 04, 2003 - 7:44 am UTC

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


Miles, February 08, 2003 - 2:09 pm UTC

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



Great Start but...

Skip, September 03, 2003 - 5:35 pm UTC

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

Tom Kyte
September 04, 2003 - 8:42 am UTC

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

2) see
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:10498431232211 <code>

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

Forced single thread

Skip, September 04, 2003 - 11:54 am UTC

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.

Tom Kyte
September 05, 2003 - 1:26 pm UTC

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.

Robert, October 27, 2003 - 3:20 pm UTC


Most efficent method

Robert, October 27, 2003 - 5:55 pm UTC

Tom,

I ran the following query on a rowid range for a table which contains approx. 1/10 of the table.
The 'date_inserted' field in the second query is not indexed.

1. Can you explain the behavior why Oracle chose to scan the Primary Key index on the 'count(*)' in the first query instead of doing table access by rowid range like the 'count(date_inserted)' did? 
(as you can see, the 'count(*)' query read approximatly 5 times as many blocks as the 'count(date_inserted)' did)

2. How can we use this rowid range to do parallel full table scans by rowid range so we can take advantage of the multi block read count of full table scans, while running multiple queries on the same table?

16:19:26 SQL> set autotrace on
16:19:33 SQL> select count(*) from accounts
16:19:37   2   where rowid between chartorowid('AAAC5rACwAABn0LAAA')
16:19:37   3                   and chartorowid('AAAC5rACwAABq8KEJA')
16:19:38   4  
16:19:38 SQL> /

 COUNT(*)
---------
   361519

 real: 78342

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5056 Card=1 Bytes=7)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FAST FULL SCAN) OF 'PK_ACCOUNTS' (UNIQUE) (Cost=5
          056 Card=34449 Bytes=241143)





Statistics
----------------------------------------------------------
          0  recursive calls
          4  db block gets
      69124  consistent gets
      66643  physical reads
          0  redo size
        172  bytes sent via SQL*Net to client
        293  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

16:20:57 SQL> 
16:39:54 SQL> 
16:39:56 SQL> edit
Wrote file afiedt.buf

  1  select count(date_inserted) from accounts
  2   where rowid between chartorowid('AAAC5rACwAABn0LAAA')
  3*                  and chartorowid('AAAC5rACwAABq8KEJA')
16:40:05 SQL> /

COUNT(DATE_INSERTED)
--------------------
              361519

 real: 21050

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=25217 Card=1 Bytes=1
          4)

   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (BY ROWID RANGE) OF 'ACCOUNTS' (Cost=25217
          Card=34449 Bytes=482286)





Statistics
----------------------------------------------------------
          0  recursive calls
          4  db block gets
      10480  consistent gets
      10480  physical reads
          0  redo size
        211  bytes sent via SQL*Net to client
        305  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

16:40:28 SQL> 

Thanks,

Robert.

 

Tom Kyte
October 27, 2003 - 6:51 pm UTC

how up to date are the stats on that index?

are they accurate?

More info

Robert, October 27, 2003 - 6:54 pm UTC

Tom,

The table was analyzed one day ago, with 'analyze table...estimate statistics'... it would not have changed significantly since then.

Thanks,

Robert.

Tom Kyte
October 27, 2003 - 7:03 pm UTC

ok, are the INDEX stats accurate -- was the INDEX analyzed a day ago as well?

see how big that index is -- the index is 7 times the size of 1/10 of the table???

the index is about 70k blocks
1/10th of the table is 10k blocks

meaning the index is 70% the size of the table in real life?

that is leading me to think the optimizer is thinking the index is smaller then it really is.

'analyze table...'

Robert, October 28, 2003 - 9:53 am UTC

Tom,

Doesn't 'analyze table...estimate statistics' automatically analyze the index stats as well?

Thanks,

Robert.

Tom Kyte
October 28, 2003 - 10:27 am UTC

it'll get the table, the indexes and histograms:

ops$tkyte@ORA920PC> create table t as select object_id, object_name from all_objects;
 
Table created.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> create index t_idx on t(object_id);
 
Index created.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> analyze table t
  2  estimate statistics
  3  /
 
Table analyzed.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> select table_name, num_rows from user_tables where table_name = 'T';
 
TABLE_NAME                       NUM_ROWS
------------------------------ ----------
T                                   32778
 
ops$tkyte@ORA920PC> select index_name, num_rows from user_indexes where table_name = 'T';
 
INDEX_NAME                       NUM_ROWS
------------------------------ ----------
T_IDX                               30207
 
ops$tkyte@ORA920PC> select count(*) from user_tab_histograms where table_name = 'T';
 
  COUNT(*)
----------
         4
 
ops$tkyte@ORA920PC>
 

...but back to the original question, please...

Robert, October 28, 2003 - 11:00 am UTC

Tom,

Hence the fact that the 'analyze table...estimate statistics' also calculates index stats, answers your concern that the index stats were out of date.

Assuming I am giving you accurate information (which I believe I am). And according to dba_tables and dba_indexes which shows this table and indexes to have been analyzed 2 days ago...

1. Why are the queries above generating those explain plans?
...and (perhaps more importantly)...
2. How can we use rowid ranges to run multiple programs simultaniously (i.e. parallel), each with a different rowid range...all accessing the table as a full table scan, thereby getting the speed of the multi block read count in parallel.

Thanks,

Robert.

Tom Kyte
October 28, 2003 - 2:09 pm UTC

1) the optimizer thinks the index is much smaller then it is. Can you peek at the stats and see if they are even anywhere CLOSE to reality.

2) this would be an appropriate time to consider using HINTS as you have a highly specialized piece of code here. You can hint this query.

More info......

Robert, October 28, 2003 - 2:57 pm UTC

Tom,

The stats appear to be accurate...

select count(*) from accounts;

COUNT(*)
---------
13811676

select num_rows
from dba_tables
where table_name = 'ACCOUNTS';

NUM_ROWS
---------
13779291

select distinct_keys
from dba_indexes
where table_name = 'ACCOUNTS'
and index_name = 'PK_ACCOUNTS';

DISTINCT_KEYS
-------------
13230309


Can you please give an example of using hints in order to achieve a full table scan on several jobs running in parallel, each running a different rowid range.
Each job would process a different rowid range on a big table and each job would do a full table scan in order to use multi block read count.
I have your new book "Effective Oracle by Design" and in Chapter 8 (I believe) you mention doing this.. but I can't find the example(s).

Thanks,

Robert

Tom Kyte
October 28, 2003 - 9:05 pm UTC


what are the index stats on this index? are they ACCURATE.

does it make sense to you that 70k blocks are in the index but only 100k blocks are in the table???


LEAF_BLOCKS NUMBER
DISTINCT_KEYS NUMBER
AVG_LEAF_BLOCKS_PER_KEY NUMBER
AVG_DATA_BLOCKS_PER_KEY NUMBER
CLUSTERING_FACTOR NUMBER
STATUS VARCHAR2(8)
NUM_ROWS NUMBER
SAMPLE_SIZE NUMBER
LAST_ANALYZED DATE


Purpose of Relative_fno

Sri, January 20, 2005 - 9:22 am UTC

Dear Tom,
what is the purpose of the column relative_fno
in dba_data_files?is rfile# is same as this?
if i can use dbms_rowid to get the relative_fno
what is the purpose of having a column in dba_data_files
thanx in advance

Tom Kyte
January 20, 2005 - 10:41 am UTC

it is used to support lots and lots of files, in most databases -- relative and "real" will be the same.

it lets us have a file number relative to a tablespace instead of the entire database, raising the over all number of possible files.

about rowid of views

Harry, January 24, 2005 - 5:24 am UTC

Greeting Tom,

I respect you. Here is my question.

Rowid for a row from a table is the physical address of a row.
How does Oracle construct the rowid for a view?

SQL> select d.rowid,e.ROWID from dept d, emp e where e.deptno=d.deptno
  2  and e.empno=7369;

ROWID              ROWID
------------------ ------------------
AAAHW5AABAAAMUSAAB AAAJ1pAABAAAP9KAAA


SQL> create or replace  view v as select d.dname, e.ename, e.empno from dept d, emp e where e.deptno=d.deptno;

View created

SQL> select rowid, dname, ename from v where empno=7369;

ROWID              DNAME          ENAME
------------------ -------------- ----------
AAAKNAAABAAAMaKAAX RESEARCH       SMITH

SQL> 

 

Tom Kyte
January 24, 2005 - 8:38 am UTC

the rowid comes from the base table that is "key preserved", from the table that is "updatable"

ops$tkyte@ORA9IR2> create table emp as select * from scott.emp;
 
Table created.
 
ops$tkyte@ORA9IR2> create table dept as select * from scott.dept;
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace  view v as select d.dname, e.ename, e.empno from dept d,
  2  emp e where e.deptno=d.deptno;
 
View created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select rowid from v;
select rowid from v
                  *
ERROR at line 1:
ORA-01445: cannot select ROWID from a join view without a key-preserved table
 
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter table dept add constraint deptno_pk primary key(deptno);
 
Table altered.
 
<b>emp is now key preserved -- it understands that the EMP rows will appear AT MOST ONCE in the view, so rowid comes from there</b>

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select rowid from v;
 
ROWID
------------------
AAAJOQAAIAAAABSAAA
AAAJOQAAIAAAABSAAB
AAAJOQAAIAAAABSAAC
AAAJOQAAIAAAABSAAD
AAAJOQAAIAAAABSAAE
AAAJOQAAIAAAABSAAF
AAAJOQAAIAAAABSAAG
AAAJOQAAIAAAABSAAH
AAAJOQAAIAAAABSAAI
AAAJOQAAIAAAABSAAJ
AAAJOQAAIAAAABSAAK
AAAJOQAAIAAAABSAAL
AAAJOQAAIAAAABSAAM
AAAJOQAAIAAAABSAAN
 
14 rows selected.
 

Harry, January 31, 2005 - 10:11 am UTC

Thanks for your quick reply. But in my new schema that is true, in my old scott schema I can not get it.

sys@PROD> @conn a/a
a@PROD> select rowid from v where rowid in (select rowid from emp);

ROWID
------------------
AAAKVXAABAAAQ+6AAA
AAAKVXAABAAAQ+6AAB
AAAKVXAABAAAQ+6AAC
AAAKVXAABAAAQ+6AAD
AAAKVXAABAAAQ+6AAE
AAAKVXAABAAAQ+6AAF
AAAKVXAABAAAQ+6AAG
AAAKVXAABAAAQ+6AAH
AAAKVXAABAAAQ+6AAI
AAAKVXAABAAAQ+6AAJ
AAAKVXAABAAAQ+6AAK
AAAKVXAABAAAQ+6AAL
AAAKVXAABAAAQ+6AAM
AAAKVXAABAAAQ+6AAN

14 rows selected.


But in my old schema, I can not get it.

scott@PROD> desc emp;
Name Null? Type
----------------------------------------------------- -------- ----------------
--------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
ID NUMBER(38)

scott@PROD> desc dept
Name Null? Type
----------------------------------------------------- -------- ----------------
--------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)

scott@PROD> desc v
Name Null? Type
----------------------------------------------------- -------- ----------------
--------------------
DNAME VARCHAR2(14)
ENAME VARCHAR2(10)
EMPNO NOT NULL NUMBER(4)

scott@PROD> select text from user_views where view_name='V';

TEXT
-------------------------------------------------------------------------------

select d.dname, e.ename, e.empno from dept d, emp e where e.deptno=d.deptno

scott@PROD> select rowid from emp;

ROWID
------------------
AAAJ1pAABAAAP9KAAA
AAAJ1pAABAAAP9KAAB
AAAJ1pAABAAAP9KAAC
AAAJ1pAABAAAP9KAAD
AAAJ1pAABAAAP9KAAE
AAAJ1pAABAAAP9KAAF
AAAJ1pAABAAAP9KAAG
AAAJ1pAABAAAP9KAAH
AAAJ1pAABAAAP9KAAI
AAAJ1pAABAAAP9KAAJ
AAAJ1pAABAAAP9KAAK
AAAJ1pAABAAAP9KAAL
AAAJ1pAABAAAP9KAAM
AAAJ1pAABAAAP9KAAN

14 rows selected.

scott@PROD> select rowid from dept;

ROWID
------------------
AAAHW5AABAAAMUSAAA
AAAHW5AABAAAMUSAAB
AAAHW5AABAAAMUSAAC
AAAHW5AABAAAMUSAAD


scott@PROD> select rowid from v;

ROWID
------------------
AAAKNAAABAAAMaKAAX
AAAKNAAABAAAMaKAAY
AAAKNAAABAAAMaKAAZ
AAAKNAAABAAAMaKAAa
AAAKNAAABAAAMaKAAb
AAAKNAAABAAAMaKAAc
AAAKNAAABAAAMaKAAd
AAAKNAAABAAAMaKAAe
AAAKNAAABAAAMaKAAf
AAAKNAAABAAAMaKAAg
AAAKNAAABAAAMaKAAh
AAAKNAAABAAAMaKAAi
AAAKNAAABAAAMaKAAj
AAAKNAAABAAAMaKAAk

14 rows selected.

scott@PROD> select rowid from v where rowid in (select rowid from emp);

no rows selected

Tom Kyte
January 31, 2005 - 10:16 am UTC

umm, get "what"

look at your keys there -- do you see how the primary keys dictate "what is the key preserved table"

Harry, February 05, 2005 - 9:38 pm UTC

Sorry, in my schema, there is a MATERIALIZED VIEW EMP_MLV created from emp table. When I query the view v, oracle rewrite the query to get the rowid from the MATERIALIZED VIEW EMP_MLV.

SQL> select rowid from v where rowid in (select rowid from emp_mlv);

ROWID
------------------
AAAKNAAABAAAMaKAAX
AAAKNAAABAAAMaKAAY
AAAKNAAABAAAMaKAAZ
AAAKNAAABAAAMaKAAa
AAAKNAAABAAAMaKAAb
AAAKNAAABAAAMaKAAc
AAAKNAAABAAAMaKAAd
AAAKNAAABAAAMaKAAe
AAAKNAAABAAAMaKAAf
AAAKNAAABAAAMaKAAg
AAAKNAAABAAAMaKAAh
AAAKNAAABAAAMaKAAi
AAAKNAAABAAAMaKAAj
AAAKNAAABAAAMaKAAk

14 rows selected
 

Only one chunk!

Neil, November 16, 2005 - 7:19 am UTC

Tom - I am getting the following output from split:

@split original 4

GRP MIN_RID MAX_RID
---------- ------------------ ------------------
3 AAB/rsAA9AAAoARAAA AAB/rsAA9AAAwAQCcQ

1 row selected.

When I make a CTAS copy of this table and split that up, it works. It also works on most of my other tables.
This table has 1396200 rows.
The dba_extents view shows this:

select count(blocks), count(block_id) from dba_extents where segment_name = 'ORIGINAL';

COUNT(BLOCKS) COUNT(BLOCK_ID)
------------- ---------------
1 1

1 row selected.

select count(blocks), count(block_id) from dba_extents where segment_name = 'COPY';

COUNT(BLOCKS) COUNT(BLOCK_ID)
------------- ---------------
567 567

1 row selected.

The original table has one extent - but why is it only showing one block?
This appears to be the reason behind the single chunk returned by split (it contains all the rows, but that kind of defeats the object!)

Is the data in the view corrupt or am I missing something?




Tom Kyte
November 16, 2005 - 5:10 pm UTC

split can only work on extent boundaries.

the maximum number of chunks is the number of extents. It works on extents itself.

Re: Only one chunk!

Neil, November 16, 2005 - 10:11 am UTC

Tom,
The table is in a large tablespace and is inheriting its storage parameters from there. The copy went into the default tablespace which is considerably smaller. In the large tablespace, the table occupies only one extent (it is expected to grow), but in the smaller one it occupies lots.
Is there a workaround for this, apart from recreating the table in a smaller tablespace? The version is 8.1.7.

Tom Kyte
November 16, 2005 - 5:59 pm UTC

define "large tablespace" and such? do you mean a locally managed tablespace with huge extents?

Re: Only one chunk!

Neil, November 17, 2005 - 4:18 am UTC

Yes - The tablespace is locally managed and the initital and next extent sizes are 134,217,728 ...

Tom Kyte
November 17, 2005 - 8:15 am UTC

I didn't get this:


and the initital and next extent sizes
are 134,217,728 ...


is that bytes? then this is just a small 128mb table (or less)? Not really a candidate for parallelism??

Only one chunk!

Neil, November 17, 2005 - 9:34 am UTC

That figure is from dba_tablespaces and presumably it's in bytes. The table contains 1.3 million rows, and is not especially large at the moment, but it will grow. I guess I'm still asking if there's any way to split a table in this fashion if it only occupies one extent. Massive extents could in theory contain massive tables after all.

Tom Kyte
November 18, 2005 - 7:17 am UTC

yes, you could. No I do not have the query. You can use the same technique, dbms_rowid to create rowid ranges, you would break the block ranges up

for example, you discover the block range is (5,145) ... (5,245) -- file 5, blocks 145..245

You could create rowid ranges from

5,145 - 5,195
5,196 - 5,245


to split into two. and then use dbms_rowid like I did to create the actual rowid - to cover row 0 from 5,145 to row 10,000 on 5,195 and so on.

multiple ranges per extent

Scott, November 30, 2005 - 11:11 am UTC

Thanks Tom. This put me on the right track for fixing an issue I am facing.

The query below will break up the table into chunks of a given number of blocks. Just change the split_size column in the mblocks subquery to control the chunk size. You should make it a factor of two for most even splits, but any number works.

It is not an issue for my purposes but small segments each get their own chunk. It shouldn't be too hard for someone to consolidate these if they needed.


WITH blocks_0 AS (
SELECT relative_fno,
block_id min_block,
lead(block_id,1) over (ORDER BY block_id) max_block,
lead(blocks, 1) over (ORDER BY block_id) blocks
FROM dba_extents
WHERE segment_name = 'FIRST_REDEMPTIONS'
AND owner = 'SVC' ORDER BY block_id
) ,
blocks AS (
SELECT *
FROM blocks_0
WHERE max_block IS NOT NULL
),
oid AS (
SELECT data_object_id
FROM all_objects
WHERE object_name = 'FIRST_REDEMPTIONS'
AND owner='SVC'
),
mblocks AS
(
SELECT MAX(blocks) max_blocks, 64 split_size FROM blocks
),
splits AS (
SELECT ROWNUM * split_size bl FROM mblocks CONNECT BY LEVEL <= max_blocks/split_size
UNION SELECT NULL FROM dual
)
SELECT dbms_rowid.rowid_create( 1, data_object_id, relative_fno, min_block, 0 ) low,
dbms_rowid.rowid_create( 1, data_object_id, relative_fno, max_block+blocks-1, 10000 ) high,
blocks, NVL(bl, blocks)
FROM blocks, oid, splits
WHERE (bl < blocks OR bl IS NULL)
ORDER BY 1,4


scratch my last post

A reader, November 30, 2005 - 1:19 pm UTC

I don't know what I was thinking with my last post. If you can delete it please do.

This query is tested:

WITH blocks AS (
SELECT relative_fno,
block_id min_block,
blocks
FROM dba_extents
WHERE segment_name = 'FIRST_REDEMPTIONS'
AND owner = 'SVC' ORDER BY block_id
) ,
oid AS (
SELECT data_object_id
FROM all_objects
WHERE object_name = 'FIRST_REDEMPTIONS'
AND owner='SVC'
),
mblocks AS
(
SELECT MAX(blocks) max_blocks, 64 split_size FROM blocks
),
splits AS (
SELECT ROWNUM * split_size bl FROM mblocks CONNECT BY LEVEL <= max_blocks/split_size
UNION SELECT NULL FROM dual
),
split_segments AS (
SELECT data_object_id, relative_fno, min_block, blocks, NVL(bl, blocks) chunk_size,
min_block+GREATEST(0,NVL(bl,blocks)-split_size) STARTb,
min_block+NVL(bl,blocks)-1 ENDb
FROM blocks, oid, splits, mblocks
WHERE (bl < blocks OR bl IS NULL)
),
rids AS (
SELECT dbms_rowid.rowid_create( 1, data_object_id, relative_fno, startb, 0 ) low,
dbms_rowid.rowid_create( 1, data_object_id, relative_fno, endb , 10000 ) high
,s.*
FROM split_segments s
)
SELECT * FROM rids
ORDER BY relative_fno, min_block,startb


DBMS_ROWID

Aru, July 19, 2006 - 10:23 pm UTC

Hi Tom,

I am trying to read about dbms_rowid package from oracle docs but got stuck right at the begineing with not understanding the following:-

  1  SELECT dbms_rowid.rowid_block_number(rowid)
  2     FROM scott.emp
  3*    WHERE ename = 'KING'
SQL> /

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
                                  32

This would mean that the particular row with ename as King recides in the block with block_no 32. Am I right in thinking that?
But when I do:-


SQL> select segment_name, segment_type from dba_extents where block_id = 32;

no rows selected.


SQL> select block_id from dba_extents where segment_name='EMP' and owner='SCOTT';
  BLOCK_ID
----------
        25

This would mean that the block_id and the rowid_block_number are not the same. 
Please help explain...

Regards,
Aru.


 

Tom Kyte
July 22, 2006 - 4:30 pm UTC

ops$tkyte%ORA10GR2> select dbms_rowid.rowid_block_number( rowid) from  scott.emp where ename = 'KING';

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
                                6256

ops$tkyte%ORA10GR2> select block_id, block_id+blocks-1 from dba_extents
  2  where segment_name = 'EMP' and owner = 'SCOTT';

  BLOCK_ID BLOCK_ID+BLOCKS-1
---------- -----------------
      6249              6256



the single extent of my scott.emp table covers the block range above.

6256 is one of the blocks in that range.

block_id in dba_extents is the FIRST BLOCK of that extent.  You have to add in the number of blocks to see what the start AND END blocks are. 

A+++++++++++++++++++++

Aru, July 23, 2006 - 5:18 pm UTC

Thanks,
Regards,
ARU.

Rowid_block_number <> block_id

Hector Gabriel Ulloa ligarius, February 14, 2007 - 12:57 pm UTC

Hi people

--Tablespace
create tablespace orderby
datafile
'/<path>/orderby01.dbf' size 1M,
'/<path>/orderby02.dbf' size 1M
extent management local uniform size 128K
segment space management manual;

--Inserting data
insert into t1
SELECT ROWNUM,
DBMS_RANDOM.STRING ('U', 64),
DBMS_RANDOM.STRING ('U', 64),
DBMS_RANDOM.STRING ('U', 64),
DBMS_RANDOM.STRING ('U', 64),
DBMS_RANDOM.STRING ('U', 64)
FROM all_objects
WHERE ROWNUM < 21;

--Retrieve data of rowid_block_number
SQL> select
2 dbms_rowid.rowid_to_absolute_fno(rowid, 'SYSTEM', 'T1') ABS_FNO,
3 dbms_rowid.rowid_relative_fno(rowid) REL_FNO,
4 dbms_rowid.rowid_block_number(rowid) BLOCKNO, -- Last block used
5 dbms_rowid.rowid_row_number(rowid) ROWNO,
6 a
7 from t1 where a = 1;

ABS_FNO REL_FNO BLOCKNO ROWNO A
---------- ---------- ---------- ---------- ----------
25 25 10 0 1

--Consulting data of blocks
SQL> select block_id ,
block_id + blocks - 1
from dba_extents
where segment_name = 'T1';


BLOCK_ID BLOCK_ID+BLOCKS-1
---------- -----------------
9 24

The dba_extents.block_id = 9 not same with
dbms_rowid.rowid_row_number(rowid) = 10

Why?

Thank you very much....

Regards
Ligarius
htp://ligarius.wordpress.com

Tom Kyte
February 14, 2007 - 2:29 pm UTC

why do you say "last block used", I would presume that to be no the last block used.

I'm not really sure what you are looking for - but remember, the first bit of a segment is used by Oracle to manage the segment itself.

Findout rowids for extent grater than some number

A reader, July 26, 2007 - 9:25 am UTC

Tom,

Thanks for your help to Oracle professionals.

My question is:

Is there a way through which I can find out ROWIDs of records for a given table where blocks were allocated from a extent i.e. where extent number(extent_id) is 50 or whatever extent_id I provide?

In short, how can I find ROWID for records providing just extent_id in my sql query?

Can you please show an example if you have any?

Cheers,
Tom Kyte
July 27, 2007 - 8:49 am UTC

ops$tkyte%ORA10GR2> create table t as select * from all_objects;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select e.extent_id, e.block_id, e.block_id+blocks-1,
  2         dbms_rowid.rowid_create( 1, o.data_object_id, e.file_id, e.block_id, 0 ) min_rowid,
  3         dbms_rowid.rowid_create( 1, o.data_object_id, e.file_id, e.block_id+e.blocks-1, 10000 ) max_rowid
  4    from dba_extents e,
  5         user_objects o
  6   where o.object_name = 'T'
  7     and e.segment_name = 'T'
  8     and e.owner = user
  9     and e.segment_type = 'TABLE'
 10   order by e.extent_id
 11  /

 EXTENT_ID   BLOCK_ID E.BLOCK_ID+BLOCKS-1 MIN_ROWID          MAX_ROWID
---------- ---------- ------------------- ------------------ ------------------
         0       4129                4136 AAAh+UAAEAAABAhAAA AAAh+UAAEAAABAoCcQ
         1       4137                4144 AAAh+UAAEAAABApAAA AAAh+UAAEAAABAwCcQ
         2       4145                4152 AAAh+UAAEAAABAxAAA AAAh+UAAEAAABA4CcQ
         3       4153                4160 AAAh+UAAEAAABA5AAA AAAh+UAAEAAABBACcQ
         4       4161                4168 AAAh+UAAEAAABBBAAA AAAh+UAAEAAABBICcQ
         5       4169                4176 AAAh+UAAEAAABBJAAA AAAh+UAAEAAABBQCcQ
         6       4177                4184 AAAh+UAAEAAABBRAAA AAAh+UAAEAAABBYCcQ
         7       4185                4192 AAAh+UAAEAAABBZAAA AAAh+UAAEAAABBgCcQ
         8       4193                4200 AAAh+UAAEAAABBhAAA AAAh+UAAEAAABBoCcQ
         9       4201                4208 AAAh+UAAEAAABBpAAA AAAh+UAAEAAABBwCcQ
        10       4209                4216 AAAh+UAAEAAABBxAAA AAAh+UAAEAAABB4CcQ
        11       4217                4224 AAAh+UAAEAAABB5AAA AAAh+UAAEAAABCACcQ
        12       4225                4232 AAAh+UAAEAAABCBAAA AAAh+UAAEAAABCICcQ
        13       4233                4240 AAAh+UAAEAAABCJAAA AAAh+UAAEAAABCQCcQ
        14       4241                4248 AAAh+UAAEAAABCRAAA AAAh+UAAEAAABCYCcQ
        15       4249                4256 AAAh+UAAEAAABCZAAA AAAh+UAAEAAABCgCcQ
        16       4361                4488 AAAh+UAAEAAABEJAAA AAAh+UAAEAAABGICcQ
        17       4489                4616 AAAh+UAAEAAABGJAAA AAAh+UAAEAAABIICcQ
        18       4617                4744 AAAh+UAAEAAABIJAAA AAAh+UAAEAAABKICcQ
        19       4745                4872 AAAh+UAAEAAABKJAAA AAAh+UAAEAAABMICcQ
        20       4873                5000 AAAh+UAAEAAABMJAAA AAAh+UAAEAAABOICcQ

21 rows selected.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select count(*) from t;

  COUNT(*)
----------
     49916

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> with data as
  2  (
  3  select e.extent_id, e.block_id, e.block_id+blocks-1,
  4         dbms_rowid.rowid_create( 1, o.data_object_id, e.file_id, e.block_id, 0 ) min_rowid,
  5         dbms_rowid.rowid_create( 1, o.data_object_id, e.file_id, e.block_id+e.blocks-1, 10000 ) max_rowid
  6    from dba_extents e,
  7         user_objects o
  8   where o.object_name = 'T'
  9     and e.segment_name = 'T'
 10     and e.owner = user
 11     and e.segment_type = 'TABLE'
 12  )
 13  select extent_id, count(*) cnt
 14    from data, t
 15   where t.rowid between data.min_rowid and data.max_rowid
 16   group by rollup (extent_id)
 17  /

 EXTENT_ID        CNT
---------- ----------
         0        437
         1        654
         2        566
         3        647
         4        592
         5        677
         6        563
         7        646
         8        545
         9        635
        10        564
        11        620
        12        543
        13        625
        14        541
        15        575
        16       8870
        17       8854
        18       8857
        19       9147
        20       4758
                49916

22 rows selected.


Findout rowids for extent grater than some number

A reader, July 29, 2007 - 11:25 pm UTC

Thanks a lot Tom. Exactly what I was looking for!

Cheers,

Map dbms_rowid.rowid_block_number(rowid) to Dba_extents

Snehasish Das, December 21, 2012 - 1:49 am UTC

Hi Tom,

How do we map dbms_rowid.rowid_block_number(rowid) to the DBA_EXTENTS view. The DBA_EXTENTS view has block_id but that is not the same as the block_number returned by
dbms_rowid.rowid_block_number(rowid).

What I practically wanted to see was what data is stored in the extents (extend_id). Like

select T.*,dbms_rowid.rowid_block_number(rowid) from TABL T where dbms_rowid.rowid_block_number(rowid) = XXXX

Regards,
Snehasish Das
Tom Kyte
January 02, 2013 - 12:32 pm UTC

dba_extents has the starting block number and number of blocks - so when you join to it, you need to use >= and <

something like this:

ops$tkyte%ORA11GR2> create table t
  2  as
  3  select * from all_objects;

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> with data
  2  as
  3  (
  4  select dbms_rowid.rowid_block_number(rowid) bno,
  5         dbms_rowid.ROWID_RELATIVE_FNO(rowid) rfno,
  6             object_id
  7    from t
  8   where mod(object_id,100) = 0
  9  )
 10  select d.object_id, d.bno, de.extent_id
 11    from data d, dba_extents de
 12   where d.bno >= de.block_id
 13     and d.bno < de.block_id+de.blocks
 14     and de.relative_fno = d.rfno
 15     and de.owner = user
 16     and de.segment_name = 'T'
 17  /

 OBJECT_ID        BNO  EXTENT_ID
---------- ---------- ----------
       100     110524          0
       300     110526          0
       400     110527          0
       500     110528          1
       600     110529          1
       700     110530          1
       800     110531          1
       900     110532          1
      1000     110533          1
      1100     110534          1
      1200     110537          2
      1300     110538          2
      1400     110539          2
      1500     110540          2
      1600     110541          2
      1700     110543          2
      1800     110544          3
      1900     110545          3
      2000     110547          3
      2100     110548          3
...

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.