Oracle ROWID and its Uniqueness

ROWID Data Type

Oracle Database uses a ROWID datatype to store the address (rowid) of every row in the database.

■ Physical rowids store the addresses of rows in ordinary tables (excluding index-organized tables), clustered tables, table partitions and subpartitions, indexes, and index partitions and subpartitions.
■ Logical rowids store the addresses of rows in index-organized tables.

A single datatype called the universal rowid, or UROWID, supports both logical and physical rowids, as well as rowids of foreign tables such as non-Oracle tables accessed through a gateway.

Physical Rowids
          Physical rowids provide the fastest possible access to a row of a given table. They contain the physical address of a row (down to the specific block) and allow you to retrieve the row in a single block access. A physical rowid datatype has one of two formats:

• The extended rowid format
• A restricted rowid format is also available for backward compatibility with applications developed with Oracle Database Version 7 or earlier releases.

Extended Rowids
          Extended rowids use a base 64 encoding of the physical address for each row selected. The encoding characters are A-Z, a-z, 0-9, +, and /. For example, the following query:

SELECT ROWID, last_name FROM employees WHERE department_id = 20;

can return the following row information:

ROWID LAST_NAME
—————— ———-
AAAAaoAATAAABrXAAA BORTINS
AAAAaoAATAAABrXAAE RUGGLES
AAAAaoAATAAABrXAAG CHEN
AAAAaoAATAAABrXAAN BLUMBERG

An extended rowid has a four-piece format, OOOOOOFFFBBBBBBRRR:

■ OOOOOO: The data object number that identifies the database segment (AAAAao in the example). Schema objects in the same segment, such as a cluster of tables, have the same data object number.
■ FFF: The tablespace-relative datafile number of the datafile that contains the row (file AAT in the example).
■ BBBBBB: The data block that contains the row (block AAABrX in the example). Block numbers are relative to their datafile, not tablespace. Therefore, two rows with identical block numbers could reside in two different datafiles of the same tablespace.
■ RRR: The row in the block.

You can retrieve the data object number from data dictionary views USER_OBJECTS, DBA_OBJECTS, and ALL_OBJECTS.

SELECT DATA_OBJECT_ID FROM DBA_OBJECTS WHERE OWNER = ‘SCOTT’ AND OBJECT_NAME = ‘EMPLOYEES’;

You can also use the DBMS_ROWID package to extract information from an extended rowid or to convert a rowid from extended format to restricted format (or vice versa).

ROWID PseudoColumn
          Each table in an Oracle database internally has a pseudocolumn named ROWID. This pseudocolumn is not evident when listing the structure of a table by executing a SELECT * FROM … statement, or a DESCRIBE … statement using SQL*Plus, nor does the pseudocolumn take up space in the table. However, each row’s address can be retrieved with a SQL query using the reserved word ROWID as a column name, for example:
                             SELECT ROWID, last_name FROM employees;

More about ROWID
          Below are some characteristics or advantages of row id values:

• They are the fastest way to access a single row.
• They can show you how the rows in a table are stored.
• They are unique identifiers for rows in a table.
• They can be reused or reassigned since they are inferred from the storage information. i.e. Assuming there are three rows in a table, deleting a row and then adding a new one could result in the newly inserted row to have the same rowid as the rowid of the row which got deleted. If they are not reused, old space would never be used.

ROWID Uniqueness
             RowIDs are unique only within a table. Every row in a nonclustered table is assigned a unique rowid that corresponds to the physical address of a row’s row piece (or the initial row piece if the row is chained among multiple row pieces).

A row piece can be understood from the below diagram which details on the format of a row piece.

In the case of clustered tables, two rows of two different tables that are stored in the same data block can have the same rowid.

Clustered Tables
            A cluster is a schema object that contains one or more tables that all have one or more columns in common. Rows of one or more tables that share the same value in these common columns are physically stored together within the database. Generally, you should only cluster tables that are frequently joined on the cluster key columns in SQL statements. For example, the employees and departments table share the department_id column. When you cluster the employees and departments tables, Oracle Database physically stores all rows for each department from both the employees and departments tables in the same data blocks.

Clustering multiple tables improves the performance of joins, but it is likely to reduce the performance of full table scans, INSERT statements, and UPDATE statements that modify cluster key values. A cluster can be either an indexed cluster or a hash cluster. Follow this link to know more about clustered tables and how to create them.

When do ROWIDs change?
           Below are some of the operations which can cause ROWID to change

1. Updating the partition key of a row in a partitioned table such that the row must move from one partition to another
2. Using the FLASHBACK table command to restore a database table to a prior point in time
3. Performing MOVE operations and many partition operations such as splitting or merge partitions
4. Using the ALTER TABLE SHRINK SPACE command to perform a segment shrink
5. Importing and exporting a table
6. Enabling Row movement. (explained below)
7. Rowids can change in an index organized table. (explained below)

Row Movement
Row movement is associated with partitioned and non-partitioned tables For partitioned tables when ROW MOVEMENT is enabled users have the ability to update partitioning key columns in such a way that a row no longer belongs in its current partition, causing such rows to migrate to the appropriate partition. Row movement is enabled at the table level and is disabled by default.

For example:

CREATE TABLE sales( sale_id NUMBER NOT NULL,
sale_date DATE, prod_id NUMBER, qty NUMBER)
PARTITION BY RANGE(prod_id)
(PARTITION salesp1 VALUES LESS THAN(10),
PARTITION salesp2 VALUES LESS THAN(20),
PARTITION salesp3 VALUES LESS THAN(30),
PARTITION salesp4 VALUES LESS THAN(40))

Partition salesp1 above will have prod_id values upto 9 (since partition high values are non-inclusive). If you issue an update statement like this:

SQL> update sales set prod_id = 15 where prod_id = 5;

For the above statement to succeed, oracle has to migrate the row that you are updating from partition salesp1 to salesp2. Under those circumstances, it will raise this error:

ORA-14402: updating partition key column would cause a partition change

You can overcome the above error by issuing this statement:

SQL> ALTER TABLE sales ENABLE ROW MOVEMENT;

— You can now issue the update statement successfully

SQL> update sales set prod_id = 15 where prod_id = 5;
4 rows updated
SQL> commit;

Row movement is allowed for non-partitioned tables starting 9i. Row movement comes in to affect for non-partitioned tables when you do operations like table compression.

Index Organized Tables (IOT) and Logical ROWIDs
         Rows in index-organized tables do not have permanent physical addresses—they are stored in the index leaves and can move within the block or to a different block as a result of insertions. Therefore their row identifiers cannot be based on physical addresses. Instead, Oracle provides index-organized tables with logical row identifiers, called logical rowids, that are based on the table’s primary key. Oracle Database uses these logical rowids for the construction of secondary indexes on index-organized tables. To understand the basics of index organized tables, navigate here.