Re: The formats of ROWID's

From: Mark D Powell <mark.powell_at_eds.com>
Date: 13 Aug 2001 06:20:29 -0700
Message-ID: <178d2795.0108130520.384de565@posting.google.com>

Dino Hsu <dino1_at_ms1.hinet.net> wrote in message news:<lqmcnt0luf28g6i81cd3cif0m2p1gtsfs2_at_4ax.com>...
> Dear all,
>
> Quoting a book:
> "... In Oracle 8i, ROWID data needs 80 bits (10 bytes) for storage.
> Oracle 8i ROWIDs consist of four components: and object number (32
> bits), a relative file number (10 bits), a block number (22 bits), and
> a row number (16 bits). Oracle 8i ROWIDs are displayed as 18-character
> representations of the location of data in the database, with each
> character represented in a base-64 format consisting of A-Z, a-z, +,
> and /. The first six characters correspond to the data object number,
> the next two are the relative file number, the next five are the block
> number, and the last three are the row number..."
>
> As a summary:
> object: 32 bits, 6 characters
> file: 10 bits, 2 characters
> block: 22 bits, 5 characters?
> row: 16 bits, 3 characters
>
> A base-64 digit (or character) can accomodate 6 bits, why does the
> 22-bit block need 5 characters while only 4 is enough? Besides,
> 6+2+5+3=16 not 18.
>
> On the other hand, in Chapter 12 Built-in data types of <<Concepts>>,
> the number of characters for object, file, block, row are 6, 3, 6, 3
> respectively without mentioning the bits.
>
> Anyone comment on this? Thanks in advance.
>
> Dino

I have never bothered to consider the actual number of bits it takes to hold each value but rather have just relied on how Oracle displays and allows the rowid to be used. I would hazard a guess that any discrepency between the number of bits necessary to hold the value and the number of bytes used in the rowid has to do with boundry alignment and retrieval performance considerations.

For those not familar with the Oracle rowid see the dbms_rowid package.

set echo off
rem
rem SQL statement to demonstrate the dbms_rowid package rem

rem  ---------------------------------------------------------------

rem The version 8 rowid looks like:
rem

rem  OOOOOOfffBBBBBBrrr  where base = 64
rem                               O = Object Id
rem                               F = Relative File No
rem                               B = Block No
rem                               R = Row No
rem
rem     version 7 was BBBBBBBB.RRRR.FFFF  where base = 16
rem                                                F = File No
rem  ---------------------------------------------------------------

rem 19981111 Mark D Powell New script rem

select rowid                                 "Row",
       dbms_rowid.rowid_type(rowid)          "Type",
       dbms_rowid.rowid_object(rowid)        "Obj",
       dbms_rowid.rowid_relative_fno(rowid)  "FNO",
       dbms_rowid.rowid_block_number(rowid)  "BN",
       dbms_rowid.rowid_row_number(rowid)    "RN",
       dbms_rowid.rowid_to_absolute_fno(rowid,'SYS','DUAL')  "AFNO"

from sys.dual
/

UT1> @dbmsrowid

Row                      Type        Obj        FNO         BN

------------------ ---------- ---------- ---------- ----------
----------
      AFNO
----------
AAAADDAABAAAALxAAA          1        195          1        753        
 0
         1

Mark D Powell --

Received on Mon Aug 13 2001 - 08:20:29 CDT