Community Pick: Many members of our community have endorsed this article.
Editor's Choice: This article has been selected by our editors as an exceptional contribution.

Decoding the Oracle ROWID (and some Base64 for fun)

Published:
Updated:
Most Oracle programmers have heard of ROWIDs. Many have even used ROWIDs at some point. A few even have an idea of what the ROWID is, logically, but only a very select few actually know the details. It is sufficient to know that the ROWID exists and how it can be used to solve problems, but there is actually some very interesting information encoded in the ROWID itself and it is useful for the DBA to know this.

In case you're not aware of the ROWID, lets have a short summary first.


The Quick Trick

The ROWID is simply a pseudocolumn (covered in more detail later) on every Oracle table, hidden from the describe view, but available in a query. The ROWID is unique for each row in a table, so it is, effectively, a surrogate primary key. This feature comes in handy in various instances, including:
   1) We have a table without a primary key and/or no date column to otherwise differentiate rows.
   2) We want to write a self-join query which needs to differentiate between the same row on each side of the join.

The first case is the most common, so lets see a quick sample.

Table T has a column "COL1", which should be unique, but someone made a mistake and did not add a primary key or unique constraint, and there are now duplicate rows. We want to clean it up.

delete from T a where exists (
                           select 1 from T b where a.COL1 = b.COL1
                                and b.ROWID < a.ROWID
                      );

Open in new window


The self-join query means "Delete all rows that have another row with the same COL1, but a "lesser" ROWID. Our choice of "lesser", while not logically required, is based on the fact that ROWIDs are ordered according to location on disk (which we will cover shortly) so we prefer to preserve the row that is closer to the beginning of the table. The main point is: without the ROWID, the only way to fix this table would be to create a new one based on the results of a query with GROUP BY COL1, or to use other columns to differentiate. While feasible, it might not be convenient for various reasons, so it is good to know the ROWID is available.

One important thing to note is that the ROWID of a logical row may change. Since it is based on the physical location of the row, when the location changes, the ROWID changes. Consequently, if a different row is then placed at the same location as an old row, it will have the old ROWID. So, the important point is not to rely on the actual values of the ROWID across time, but to make use of them at that point in time. Any operation that reorganizes a table or segment will change ROWIDs, including MOVE, SHRINK SPACE, partition splits, partition row updates and recovery operations that affect rows.

At this point, a database programmer can be confident that he knows all he needs to know about the ROWID. A DBA, on the other hand, often wants to know the meaning behind the numbers. The rest of the article is dedicated to the DBA.


A Closer Look

The ROWID is an attribute of the Physical Model only. It has nothing to do with the Logical Model. Porting the model to another database platform will not port the ROWID, though the concept of a "rowid" may exist in another form in the new database. Each database has its own way of dealing and/or exposing this sort of thing. Like all good DBAs, we spend a lot of time on both the Logical and Physical sides; to really be a good DBA you need to know the physical side of your database. Without that, you'll have no real idea of how to tune, apart from high level Logical model tuning and SQL tuning. The ROWID is not the only way to approach the Physical side, but it does play a critical, irreplaceable role in nearly every query that executes.

We said it is an pseudocolumn, so what is a pseudocolumn? Oracle defines it: "A pseudocolumn behaves like a table column, but is not actually stored in the table. You can select from pseudocolumns, but you cannot insert, update, or delete their values. A pseudocolumn is also similar to a function without arguments. However, functions without arguments typically return the same value for every row in the result set, whereas pseudocolumns typically return a different value for each row." [1]

It is a "special" pseoducolumn. You may use reserved words when naming objects or columns in Oracle, and may even create columns named after pseudocolumns (bad practice!), and use them just fine. For example, you can create a table with a column, quoted "ROWNUM" or after a function name like "COUNT" as long as the name is quoted in the DDL and in any SQL that uses the columns. ROWID, however, is not one of those. Oracle will not allow you to use "ROWID" in a quoted column name. Why? Well if it did, all sorts of things would go wrong, because Oracle, itself, also relies on the ROWID.

It also has an associated Oracle type, ROWID. We will not cover it in this article, but be aware you can use this in PL/SQL.

So, is the ROWID provided solely for our convenience?

No. The ROWID is intrinsic and critical to the operation of the database. The typical database table is a "heap" table, which means just a big pile of stuff. No order, just stick it where you can find a place. The problem is, heaps don't work too well for fast retrieval. It is like someone dumped a pile of papers on your desk. You need to sift through the whole pile to find any given paper. You would probably organize the pile into a filing cabinet, for fast retrieval in the future. Inside the database we need a fast way to retrieve random rows. Without this, the database has to scan each row, searching for the key you asked for, and will grind to a halt after a few simple queries on a large table due to the time-consuming, full table scans. This is where indexes come in. Most databases implement the classic index as a secondary structure. Secondary, in that the keys in the index are not stored in the same area as the table rows to which the keys refer. The index is stored "out of line." There is a special type of table, the Index Organized Table or IOT, which is a combined index and table in one. We will ignore those for now, as they are not the standard table and have special constraints. We will focus on the classical index.

The standard Oracle index is a type of B-Tree. It must solve the basic problem of fast retrieval of a row of data by a key value.  It does this by storing the key (primary key is one example) and a reference to the data row that the key points to, but does not typically store actual data; data resides in the table. The database can typically locate the key in an index with 2 or 3 block reads, but sometimes less than that. Once the index block is located, the next step is to return the actual row data associated. The database must be able to immediately translate the pointer in the index block to the block and row of the data table and retrieve it with a single IO operation. This pointer is the ROWID, and it is efficiently translated to a physical location of a block and row on disk.

Assuming we are viewing the ROWID in its "Oracle presented" format, it looks like this:  

   AAACiZAAFAAAAJEAAA

The Oracle format is a string encoding known as Base64. Selecting a ROWID from Oracle will result in a Base64 display of the value.

What is in the encoding? Four things: *
    1) The data object number of the object
    2) The datafile in which the row resides (first file is 1). The file number is relative to the
    3) The data block in the datafile in which the row resides
    4) The position of the row in the data block (first row is 0)
tablespace. *

The format is:   OOOOOO.FFF.BBBBBB.RRR
      OOOOOO is the object ID
      FFF is the file number
      BBBBBB is the block number
      RRR is the row number

The datafile number is unique in the database. You can retrieve it from the DBA_DATA_FILES view. Each datafile is broken into blocks. The block usually corresponds exactly to the low level (OS) block size of the file system. Usually 8k. The OS size is the most efficient for a read IO operation; there is no penalty for reading 8k vs 8 bytes. Even though blocks are organized into extents, and extents into segments, we do not need to refer to extents or segments to retrieve a block of data once we have the ROWID. Oracle decodes the ROWID, uses it to immediately select the open datafile by number, performs a seek to the block offset from the ROWID, and retrieves the block with a single read operation. Once the block is in memory, Oracle uses the row number, which is an offset into the block's row directory, to retrieve the data from the block. Except for the physical read IO operation, the rest of these calculations and retrievals of data are done in memory, with very few CPU cycles. If the block is in the data cache, already, then no IO happens at all. Furthermore, Oracle can partially or fully cache the index, so even with very large indexes, the correct index block can still be located with a single IO or less, on average.

Now that we know that the ROWID is critical to Oracle's internal functions, what do we, as DBAs, benefit from this knowledge? It is a very good educational exercise to fully understand where Oracle is storing your data, and it can really help you feel closer to the "machine" and realize, even with all of the management tools, there are still such things as hotspots and inefficient data storage at the block level. This will continue to be relevant information until all storage moves to Solid State, where IOs become as fast as memory reads and there are no seek latencies.

So, if you are still awake, and want to do a little forensics work, lets begin to demystify the ROWID.

Lets start with some known values.

* I've created a tablespace in my database, USERS, and it has datafile # 5. You can find the datafile #, as stated above, by viewing DBA_DATA_FILES.

* I also have created the classic sample schema, SCOTT. I created a table called T in the schema, with a couple of columns, and insert 10000 rows into the table. The table structure is irrelevant, but for the exercise you should at least insert enough data to force Oracle to allocate several blocks for the table.

sqlplus scott/tiger
                      
                      create table t
                      ( id integer primary key,
                        name varchar(1)
                      )
                      ;
                      
                      -- insert 10,000 sample rows
                      begin
                        for i in 1..10000 loop
                            insert into t values(i, 'A');
                        end loop;
                      end;
                      /

Open in new window


* Select some sample data, let's select the first three ids, as well as ids 501 through 503.

SQL> select rowid as therowid, id from t where id between 1 and 3 or id between 501 and 503 order by id;  [3]
                      
                      THEROWID                   ID
                      ------------------ ----------
                      AAACjyAAFAAAALNAAA          1
                      AAACjyAAFAAAALNAAB          2
                      AAACjyAAFAAAALNAAC          3
                      AAACjyAAFAAAALPACH        501
                      AAACjyAAFAAAALPACI        502
                      AAACjyAAFAAAALPACJ        503
                      
                      6 rows selected.

Open in new window


Now, let's discuss the format again, with Base64 in mind.

The Base64 alphabet is comprised of 6-bit values, the character set maps to ASCII, using A-Z, a-z, 0-9, + and /. You can relate this to hexadecimal format, or Base16, which uses 4-bits per character. While hex format maps well to bytes (2 hex digits per 8-bit byte is easy to print in ASCII using 0-9 and A-F), Base64 is not so easy, as you cannot represent an even number of Base64 alphabet symbols with a byte. Since the upper 2 bits of the first byte overlap to the next, and so forth, you need 4 characters to make 3 bytes. You can see its slightly denser than the 2 to 1 ratio of Base16, it is less convenient due to the bit overlap. For a more detailed treatment of Base64, read the original RFC for Mime, found here at the IETF website:   http://www.ietf.org/rfc/rfc2045.txt

In the ROWID, the most interesting number for us is the block number, as it gives us insight into the true data layout on disk, but lets start with a known entity, the row number, and use the first row inserted into the table as our test case. It should be the first entry in the row of a block, if the table was new and empty when we inserted. If we get that right, we can apply to the rest of the encoded values.

So lets redo the query above, and translate those ROWIDs into the actual numbers that we want. Lets extract the last segment, which is three characters of the Base64 string.

SQL> select substr(rowid, 16, 3)
                                 from t where id <= 2;
                      
                      ------------
                      AAA
                      AAB

Open in new window


Great, two rows, with the encoded block row numbers, but we need to decode the Base64 into a number we can understand, or they are of little use.

Recall that Base64 is encoded in 6-bit components. A bit of a problem, since the lowest level we can deal with is 8-bit bytes. We must understand that Base64 works on even intervals of 3-octet boundaries, so it requires a minimum of 24-bits at a time, which equals four 6-bit letters. So we cannot use odd length, arbitrary strings to represent Base64 or to pass to the APIs in Oracle, we must have four characters at a time to represent 3 raw bytes in a raw string. You may see the first challenge, since the ROWID format includes several odd length segments, for example the row number in the block for the ID 1 above is 'AAA', which is zero, but for Base64, we will need to pad that out to AAAA to result in 24-bits to decode.

We'll use LPAD() to do so in Oracle to left pad with zero bits.

SQL> select LPAD(substr(rowid, 16, 3), 4, 'A')
                                 from t where id <= 2;
                      
                      ----------------
                      AAAA
                      AAAB

Open in new window


If we did not do this, then the encoding API we use in the next step would reject the odd length Base64 intervals and the values would be wrong.

The next step is to decode the properly padded values from above. We need the UTL_ENCODE.BASE64_DECODE() function. Since this function expects a RAW value, we'll need to convert that 'AAAA' string into a RAW, using UTL_RAW.CAST_TO_RAW(). It does get a bit nested, but that's why we are building in layers, hang in there.

SQL> select utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 16, 3), 4, 'A')))
                                 from t where id <= 2;
                      
                      --------------------------------------------------------------------------------
                      
                      000000
                      000001

Open in new window


This looks good! At least, it is what we expected, sequent values, assuming A is zero in the Base64 alphabet.

Now the last step is to convert the hexidecimal RAW to a readable decimal number. We can simply use TO_NUMBER() with the 'X' format specifier. We will need to know how many X digits to expect, so for the above, there are six.

SQL> select to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 16, 3), 4, 'A'))), 'XXXXXX') as rowslot
                           from t where id <= 2;
                      
                         ROWSLOT
                      ----------
                               0
                               1

Open in new window


Perfect! We have, what appears to be, an accurate conversion of the Base64 ROWID to the real row slots in the data block.

Now lets take all of this we learned, apply it to the rest of the components in the block and extract the rest of the information.

SQL> select rowid as therowid, id,
                        to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid,1, 6), 8, 'A'))), 'XXXXXXXXXXXX') as objid,
                        to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 7, 3), 4, 'A'))), 'XXXXXX') as filenum,
                        to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 10, 6), 8, 'A'))), 'XXXXXXXXXXXX') as blocknum,
                        to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 16, 3), 4, 'A'))), 'XXXXXX') as rowslot
                        from t where id <= 2
                        ;
                      
                      THEROWID                   ID      OBJID    FILENUM   BLOCKNUM    ROWSLOT
                      ------------------ ---------- ---------- ---------- ---------- ----------
                      AAACjyAAFAAAALNAAA          1      10482          5        717          0
                      AAACjyAAFAAAALNAAB          2      10482          5        717          1

Open in new window


Now, the picture is clear. The first two rows exist in the same block, as expected, and they are adjacent in the block. The block is number 717 inside the 5th datafile. We can query the DBA_OBJECTS with OBJID of 10482, and, sure enough, we see the object 'T' owned by SCOTT. To make this a little more interesting lets add a couple of rows further into the dataset and see where Oracle put them.

SQL> select
                       rowid as therowid, id,
                       to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 1, 6), 8, 'A'))), 'XXXXXXXXXXXX') as objid,
                       to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 7, 3), 4, 'A'))), 'XXXXXX') as filenum,
                       to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 10, 6), 8, 'A'))), 'XXXXXXXXXXXX') as blocknum,
                       to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 16, 3), 4, 'A'))), 'XXXXXX') as rowslot
                      from t where id in(1, 2, 500, 501)
                      ;
                      
                      THEROWID                   ID      OBJID    FILENUM   BLOCKNUM    ROWSLOT
                      ------------------ ---------- ---------- ---------- ---------- ----------
                      AAACjyAAFAAAALNAAA          1      10482          5        717          0
                      AAACjyAAFAAAALNAAB          2      10482          5        717          1
                      AAACjyAAFAAAALPACG        500      10482          5        719        134
                      AAACjyAAFAAAALPACH        501      10482          5        719        135

Open in new window


We can see rows 500 and 501 are stored in a different block than 1 and 2, and if we multiply BLOCKNUM x the database BLOCKSIZE we will even have an idea of where the rows exist in the raw datafile.

Hopefully this short forensics exercise has helped you understand how to approach decoding any other value in the database, besides ROWIDs. Now I will reveal that you don't have to use all of this decoding trouble to view the elements of the ROWID, you can use the DBMS_ROWID package!

The above query could be rewritten as:

SQL> select
                       rowid as therowid, id,
                       dbms_rowid.rowid_object(rowid) as objid,
                       dbms_rowid.rowid_relative_fno(rowid) as relfilenum,
                       dbms_rowid.rowid_to_absolute_fno(rowid, 'SCOTT', 'T') as absfilenum,
                       dbms_rowid.rowid_block_number(rowid) as blocknum,
                       dbms_rowid.rowid_row_number(rowid) as rowslot
                       from t where id in(1, 2, 500, 501)
                      order by id
                      ;
                      
                      THEROWID                   ID      OBJID RELFILENUM ABSFILENUM   BLOCKNUM    ROWSLOT
                      ------------------ ---------- ---------- ---------- ---------- ---------- ----------
                      AAACjyAAFAAAALNAAA          1      10482          5          5        717   0
                      AAACjyAAFAAAALNAAB          2      10482          5          5        717   1
                      AAACjyAAFAAAALPACG        500      10482          5          5        719 134
                      AAACjyAAFAAAALPACH        501      10482          5          5        719 135

Open in new window



That may seem like a lot of trouble in light of the builtin DBMS_ROWID API, but if we had not worked through the actual decoding process before showing the builtin API, we might not have done a just job of fully demystifying the ROWID. By now I hope you feel you learned something you didn't know about the Oracle API, and most importantly, the mystery of the ROWID is gone, and you have more tools for investigation at your disposal, all from SQL*Plus!



Footnotes & References:

* - Oracle Database SQL Reference 10g Release 2 Part Number B14200-02

** - If you were paying attention you will notice this query has both the relative and absolute file number in Oracle. I have not determined why these match in all objects I query, as the documentation says the relative file number is relative to the tablespace, however, this does not seem to be supported by the DBA_DATA_FILES view or the DBMS_ROWID package at all. I will continue to research this minor point and update the article later.

*** - Column ROWID is aliased to THEROWID in the queries for Toad users, since Toad will hide the ROWID column unless specifically configured not to do so, as it uses it for grid editing, etc.


5
30,239 Views

Comments (3)

Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
I'd highlight this line in some way...

"Even though blocks are organized into extents, and extents into segments, we do not need to refer to extents or segments to retrieve a block of data once we have the ROWID."


It's still a misconception to this day that a large number of extents somehow hurts query performance.

It's late, I'll dig in and comment more later, looks like a good start though.
Top Expert 2009

Author

Commented:
@sean: Only makes a difference in multi-block scans, say in large table scans or range scans. So it still does affect in certain cases, but not really in random access. Still a good idea to have reasonable extent allocation, but I've had tables with thousands of extents with no apparent performance problem. I don't like to rebuild stuff just for the heck of it, I wait until I see a problem.

Other point in this is just because the DBA is managing extents at the tablespace level, does not mean the blocks are contiguous on disk. If the DBA is using a cooked filesystem, the blocks could be anywhere. That's one advantage to ASM or RAW in that regard.

I still would like your review, as another Oracle guy. I'm considering redoing it just a bit once I get several comments.
Helena Markováprogrammer-analyst
CERTIFIED EXPERT

Commented:
I am not a DBA, but the article was very interesting and useful one for me.

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.