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:
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 rowed (and if you check stdbody.sql from $ORACLE_HOME/rdbms/admin, you’ll find that the pl/sql definition for rowed 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 following 20 rows I get from sorting by the character version of the 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” clause 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 your code.