Rowids

I have, in the past, used the dbms_rowid package to create rowids from block addresses (typically faking the first and last rowids that could appear in an extent) but I’ve just been sent a piece of information by Valentin Nikotin that’s going to make me go back and check whether what I’ve done with the package will always give me the correct results. Here’s a little demonstration code that highlights the issue:

rem
rem     Script:         rowid_trap.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Dec 2013
rem
rem     From an email by Valentin Nikotin
rem

create table t1
as
select
        rownum                  id,
        dbms_rowid.rowid_create(
                rowid_type      => 1,
                object_number   => 250000,
                relative_fno    => 5,
                block_number    => rownum + 2330000,
                row_number      => 1
        )                       basic_rowid,
        cast(
                dbms_rowid.rowid_create(
                        rowid_type      => 1,
                        object_number   => 250000,
                        relative_fno    => 5,
                        block_number    => rownum + 2330000,
                        row_number      => 1
                )
                as rowid
        )                       cast_rowid
from
        dual
connect by
        level <= 50
;


spool rowid_trap

select count(*) from t1 where basic_rowid = cast_rowid;
select count(*) from t1 where basic_rowid != cast_rowid;

select * from t1 order by basic_rowid;
select * from t1 order by cast_rowid;

spool off

As you can see I create a table with 50 rows, generating the nominal rowids that might appear as the first rowid for each of several consecutive blocks in a file. (The choice of rownum + 2330000 is based on the example that Valentin sent me using a real physical object and references to dba_extents).

As you might expect, when I compare the rowid value returned from dbms_rowid.rowid_create() with the equivalent value cast to rowid I get a perfect match across the table.

If you look at the output from sorting the rows, though, the rows appear in a different order. Check the table definition and you’ll see that dbms_rowid.create_rowid() seems to have returned a varchar2(), holding the “readable” representation of a rowid (and if you check stdbody.sql from $ORACLE_HOME/rdbms/admin, you’ll find that the pl/sql definition for rowid is as a subtype of varchar2()). Unfortunately the varchar2() representation of a rowid doesn’t follow the same sorting rules as the internal representation of a rowid.

The upshot of this is that if you’ve been using dbms_rowid.create_rowid() to generate a list of rowid ranges that you want to use to break a big object into little pieces for “manual parallelism” then you may have managed to generate a list of ranges that didn’t cover the whole object. Consider, for example, the first 20 rows I get from sorting by the character version of the rowid:

        ID BASIC_ROWID        CAST_ROWID
---------- ------------------ ------------------
        46 AAA9CQAAFAAI42+AAB AAA9CQAAFAAI42+AAB
        47 AAA9CQAAFAAI42/AAB AAA9CQAAFAAI42/AAB
        36 AAA9CQAAFAAI420AAB AAA9CQAAFAAI420AAB
        37 AAA9CQAAFAAI421AAB AAA9CQAAFAAI421AAB
        38 AAA9CQAAFAAI422AAB AAA9CQAAFAAI422AAB
        39 AAA9CQAAFAAI423AAB AAA9CQAAFAAI423AAB
        40 AAA9CQAAFAAI424AAB AAA9CQAAFAAI424AAB
        41 AAA9CQAAFAAI425AAB AAA9CQAAFAAI425AAB
        42 AAA9CQAAFAAI426AAB AAA9CQAAFAAI426AAB
        43 AAA9CQAAFAAI427AAB AAA9CQAAFAAI427AAB
        44 AAA9CQAAFAAI428AAB AAA9CQAAFAAI428AAB
        45 AAA9CQAAFAAI429AAB AAA9CQAAFAAI429AAB
         1 AAA9CQAAFAAI42RAAB AAA9CQAAFAAI42RAAB
         2 AAA9CQAAFAAI42SAAB AAA9CQAAFAAI42SAAB
         3 AAA9CQAAFAAI42TAAB AAA9CQAAFAAI42TAAB
         4 AAA9CQAAFAAI42UAAB AAA9CQAAFAAI42UAAB
         5 AAA9CQAAFAAI42VAAB AAA9CQAAFAAI42VAAB
         6 AAA9CQAAFAAI42WAAB AAA9CQAAFAAI42WAAB
         7 AAA9CQAAFAAI42XAAB AAA9CQAAFAAI42XAAB
         8 AAA9CQAAFAAI42YAAB AAA9CQAAFAAI42YAAB

If I had supplied the 1st and 20th rowids as a range to be used by some “chunking” code in a “between” predicate those rows would have slipped through a crack in the selection code (unless they were accidentally picked up in another badly defined chunk – which might then result in lock waits and deadlocks). So if you’ve used dbms_rowid in production code it might be a good idea to go and check exactly what your code does.

Footnote

The internal storage (e.g. in an index) of a rowid is 6 or 10 bytes (the latter relevant to global indexes on partitioned objects) but the varchar2() representation of a rowid is 18 characters long with the following breakdown:

 
Position   Content
--------   -------
 1 -  6:   Object ID
 7 -  9:   Relative File number
10 - 15:   Block number within file
16 - 18:   Row number within block

If you’re looking at a global (or globally partitioned) index on a partitioned table the entry could point to any one of the partitions in the table, so it has to hold 4 bytes for the object_id of the partition; but for an index on a non-partitioned table the table’s object_id is known before the index is chosen, so the table’s object_id doesn’t need to be stored in the index although it will appear in the external representation.