Oracle Scratchpad

December 1, 2013

Rowids

Filed under: Infrastructure,Oracle,sorting — Jonathan Lewis @ 11:26 am BST Dec 1,2013

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.

 

2 Comments »

  1. btw, it’s interesting: what then is the “PL/SQL ROWID”?
    I can’t find any argument with this type…

    SQL> select type_name,type_oid,typecode
      2  from  dba_types
      3  where type_name like '%ROWID'
      4    and predefined='YES'
      5  /
    
    TYPE_NAME                      TYPE_OID                         TYPECODE
    ------------------------------ -------------------------------- ------------------------------
    BINARY ROWID                   0000000000000000000000000000002A BINARY ROWID
    PL/SQL ROWID                   0000000000000000000000000000002F PL/SQL ROWID
    UROWID                         00000000000000000000000000000046 UROWID
    
    SQL> select o.name,o.oid$,t.typecode from sys.type$ t, sys.obj$ o
      2  where o.oid$ = t.tvoid
      3    and o.subname IS NULL
      4    and upper(name) like '%ROWID'
      5  order by t.typecode;
    
    NAME                           OID$                               TYPECODE
    ------------------------------ -------------------------------- ----------
    BINARY ROWID                   0000000000000000000000000000002A         69
    UROWID                         00000000000000000000000000000046        104
    PL/SQL ROWID                   0000000000000000000000000000002F        262
    

    Comment by Sayan Malakshinov — December 1, 2013 @ 5:32 pm BST Dec 1,2013 | Reply

    • Sayan,

      There’s a song in “Lion King” (and a line from Hamlet) about there always being more to find out.
      Here’s a guess – how does Oracle record a rowid when in pl/sql that does “update current of” in a cursor loop ? Remember that execution plans have both a “access by index rowid” and “access by user rowid” operation – and we can use extended trace with bind variables to see the incoming type if we want to experiment.

      (Left as an exercise for the interested reader.)

      Comment by Jonathan Lewis — December 10, 2013 @ 10:03 am BST Dec 10,2013 | Reply


RSS feed for comments on this post. TrackBack URI

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

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,877 other followers