We have several posts on AskTom where it is described how to “carve up” a table into equi-sized chunks in order to (say) perform a task in parallel on that table. Here is an example of one.
Much of this has nowadays been obsoleted by the DBMS_PARALLEL_EXECUTE package, but in either instance, one key point remains: When you are generating rowids, there is no guarantee that the rowid you generate is either valid or will return a row from the table even if you used xxx_EXTENTS to build that rowid.
Let’s look at an example
SQL> create table t ( x int, y char(100));
Table created.
SQL>
SQL> insert into t
2 select rownum, rownum
3 from dual
4 connect by level <= 10;
10 rows created.
SQL>
SQL> commit;
Commit complete.
SQL> select object_id, data_object_id
2 from user_Objects
3 where object_name = 'T';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
173494 173494
1 row selected.
So here are the true rowids from the table.
SQL> select x, rowid from t;
X ROWID
---------- ------------------
1 AAAqW2AAGAAAADlAAA
2 AAAqW2AAGAAAADlAAB
3 AAAqW2AAGAAAADlAAC
4 AAAqW2AAGAAAADlAAD
5 AAAqW2AAGAAAADlAAE
6 AAAqW2AAGAAAADlAAF
7 AAAqW2AAGAAAADlAAG
8 AAAqW2AAGAAAADlAAH
9 AAAqW2AAGAAAADlAAI
10 AAAqW2AAGAAAADlAAJ
10 rows selected.
So let’s use DBA_EXTENTS to attempt to build a rowid to access a row from the table
SQL> SELECT Dbms_Rowid.Rowid_Create(1, 173494, Relative_Fno, Block_Id, 0)
2 FROM Dba_Extents
3 WHERE Segment_Name = 'T'
4 AND Owner = user
5 AND Extent_Id = 0;
DBMS_ROWID.ROWID_C
------------------
AAAqW2AAGAAAADgAAA
1 row selected.
SQL> select * from t where rowid = 'AAAqW2AAGAAAADgAAA';
select * from t where rowid = 'AAAqW2AAGAAAADgAAA'
*
ERROR at line 1:
ORA-01410: invalid ROWID
This is the critical point. Instinctively, we may feel that the rows will start at the very first byte in the very first block of the very first extent. But this is not the reality. In this case, we can trawl a little further to see in which block the first rows are found:
SQL> SELECT Dbms_Rowid.Rowid_Create(1, 173494, Relative_Fno, Block_Id+rownum, 0)
2 FROM Dba_Extents, ( select 1 from dual connect by level <= 8 )
3 WHERE Segment_Name = 'T'
4 AND Owner = user
5 AND Extent_Id = 0;
DBMS_ROWID.ROWID_C
------------------
AAAqW2AAGAAAADhAAA
AAAqW2AAGAAAADiAAA
AAAqW2AAGAAAADjAAA
AAAqW2AAGAAAADkAAA
AAAqW2AAGAAAADlAAA
AAAqW2AAGAAAADmAAA
AAAqW2AAGAAAADnAAA
AAAqW2AAGAAAADoAAA
8 rows selected.
SQL> select * from t where rowid = 'AAAqW2AAGAAAADhAAA';
select * from t where rowid = 'AAAqW2AAGAAAADhAAA'
*
ERROR at line 1:
ORA-01410: invalid ROWID
SQL> select * from t where rowid = 'AAAqW2AAGAAAADiAAA';
select * from t where rowid = 'AAAqW2AAGAAAADiAAA'
*
ERROR at line 1:
ORA-01410: invalid ROWID
SQL> select * from t where rowid = 'AAAqW2AAGAAAADjAAA';
no rows selected
SQL> select * from t where rowid = 'AAAqW2AAGAAAADkAAA';
no rows selected
SQL> select * from t where rowid = 'AAAqW2AAGAAAADlAAA';
X Y
---------- ----------------------------------------------------------------------------------------------------
1 1
1 row selected.
SQL> select * from t where rowid = 'AAAqW2AAGAAAADmAAA';
no rows selected
SQL> select * from t where rowid = 'AAAqW2AAGAAAADnAAA';
no rows selected
All sorts of factors can impact “where” a row will reside in a table, so whenever you are generating rowids, ensure your queries are not using equality predicates. Generated rowids provide ranges of data to search for. In our example above, we can use the single extent to determine upper and lower bounds for the rowids and use that.
SQL> select * from t
2 where rowid between
3 ( select cast(dbms_rowid.rowid_create(1, 173494, relative_fno, block_id, 0) as rowid)
4 from dba_extents
5 where segment_name = 'T'
6 and owner = user
7 and extent_id = 0
8 )
9 and
10 ( select cast(dbms_rowid.rowid_create(1, 173494, relative_fno, block_id + blocks - 1, 9999) as rowid)
11 from dba_extents
12 where segment_name = 'T'
13 and owner = user
14 and extent_id = 0
15 )
16 ;
X Y
---------- ---------------------------------------------------------------------------------
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
