Generating rowids

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
  2    Dbms_Rowid.Rowid_Create(1, 173494, Relative_Fno, Block_Id, 0) lo_val,
  3    Dbms_Rowid.Rowid_Create(1, 173494, Relative_Fno, Block_Id + blocks - 1, 9999) hi_val
  4  FROM Dba_Extents
  5  WHERE Segment_Name = 'T'
  6  AND Owner = user
  7  AND Extent_Id = 0;

LO_VAL             HI_VAL
------------------ ------------------
AAAqW2AAGAAAADgAAA AAAqW2AAGAAAADnCcP

1 row selected.

SQL> select * from t where rowid between 'AAAqW2AAGAAAADgAAA' and 'AAAqW2AAGAAAADnCcP';

         X Y
---------- ---------------------------------------------------------------------------------
         1 1
         2 2
         3 3
         4 4
         5 5
         6 6
         7 7
         8 8
         9 9
        10 10

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s