About Rowids
- ROWIDs uniquely identifiy a row in the database. (although there are some exceptions).
- ROWID may refer to a datatype and a pseudocolumn. In its different forms it is a representation of the physical location of a row within the database.
- The ROWID for a row can change as the result of dumping and reloading the database.
- For this reason ROWID values should not be used across transaction lines.
- You cannot set the value of a ROWID pseudocolumn.
- ROWID is not physically stored in the database.It is inferred from the file and block address of the data.
ROWIDs are used in the construction of
indexes. In addition to this,
- Rowids are the fastest means of accessing particular rows.
- Rowids provide the ability to see how a table is organized.
- Rowids are unique identifiers for rows in a given table.
| Types of ROWIDs: Physical Rowid, Logical Rowid and Foreign Rowid |
Physical ROWIDs:
- Store the addresses of rows in heap-organized tables, clustered tables, and table and index partitions.
- Provide the fastest access to a row.
- Contain the physical address of a row and allow its retrieval in a single block access.
- Rowid corresponds to the physical address of a row's row piece (or the initial row piece if the row is chained in multiple row pieces).
- In case of clustered tables, rows in different tables that are in the same data block can have the same rowid.
- Physical rowids have two formats: restricted and extended.
- In a logical rowid, a physical guess identifies the block in which a row was stored at the time the guess is made, and the database uses the guess to search the block directly.
- However, as new rows are inserted, guesses can become stale.
- Guess staleness can be monitored with DBMS_STATS (index statistics capture the percentage of rows with valid guesses).
- Statistic stored in the
DBA_INDEXES
(PCT_DIRECT_ACCESS
column).
- To refresh guess information:
- Rebuild secondary indexes (resource intensive).
ALTER INDEX...UPDATE BLOCK REFERENCES
Logical ROWIDs:
- Strore the addresses of rows in index-organized tables (IOTs).
- Used in index-organized tables (IOT), these are based on the table's primary key.
- Logical rowids change when the IOT's primary key is updated.
- This is because table data is stored in the index leaves and thus do not have a permanent physical address.
- Indexes that use logical rowids include a physical guess, which identifies the block location of the row in the IOT at the time the guess was made.
- Since logical rowids do not indicate the exact physical location of a row, they cannot be used to see how a table is organized.
| Restricted x Extended ROWIDs |
Restricted ROWIDs:
- Provided for backward compatibility with applications developed with Oracle 7 and earlier
- Until Oracle 7, ROWIDs used 8 bytes
- The old format is known as Restricted rowid format.
- Restricted ROWID format stores (block number, Row number, file number)
|
- Restricted Rowid (8 bytes)
- Block# (bytes 1-4).Row#(bytes 5-6).File#(bytes 7-8)
|
Extended ROWIDs
- Starting with Oracle 8 Rowid's format changed.
- Extended rowid: four-piece format.
- Uses a base 64 encodig (A-Z,a-z,0-9,+,/)
- It is now 10 bytes long and includes the ID of the Object to which the row belongs.
- Until Oracle 7 file numbers were unique within a database.
- With the new format, ROWIDs stores the relative file number (the file number within the tablespace in which the object is stored).
- Until Oracle 7, the 2-byte representation of file numbers limited the maximum number of files a database could have to 65533.
- This limit now is for the number of files each tablespace can have.
- An extended rowid includes a data object number. This rowid type uses a base 64 encoding of the physical address for each row.
|
- AAAC9E: The data object number identifies the segment. Note:
- A data object number is assigned to every database segment (table, index, etc).
- HOWEVER, Schema objects in the same segment (such as a table cluster) have the SAME Object ID.
- AAE: The data file number: Tablespace-relative. Identifies the file that contains the row.
- AAAABX: The data block number Identifies the block that contains the row. Relative to the datafile.
- AAA: The row number: identifies the row in the block.
ObjID(bytes 1-4) . RelativeFN(byte 5 + 4bits) . Block#(4bits + byte 7) . Row#(bytes 9-10)
|
ROWID Pseudocolumn- Every Oracle table has a pseudocolumn named ROWID. Its value, however, is not actually stored in the table.
- You can select from pseudocolumns, but you cannot insert, update, or delete their values.
- Values of the ROWID pseudocolumn are strings representing ,in hexadecimal format, the address of each row.
- You can parse the value returned from rowid to understand the physical storage of rows in the database.
(a) Query the extended rowid
SQL> select rowid, last_name from hr.employees where employee_id=100;
ROWID LAST_NAME
------------------ -----------
AAAR5VAAFAAAADPAAA King
(b) To see the old rowid format (restricted rowid), you can use the DBMS_ROWID
package:
SQL> select dbms_rowid.rowid_to_restricted(rowid,1) old_rowid
2 from hr.employees
3* where employee_id = 100
OLD_ROWID
------------------
000000CF.0000.0005
Restriced rowid format:
000000CF: data block that contains the row.
- Block numbers are relative to the datafile, not tablespace.
0000:
row numbers start with 0.
0005:
data file that contains the row.
- With the restricted format, to find out the segment (table, index) a rowid belonged to it was necessary to compare ROWID's block and file numbers with entries in the
DBA_EXTENTS
view.
- With extended format, you can use
DBMS_ROWID
as below:
SQL> select first_name, dbms_rowid.rowid_object(rowid) "ObjectID",
2 dbms_rowid.rowid_relative_fno(rowid) "File#",
3 dbms_rowid.rowid_block_number(rowid) "Block#",
4 dbms_rowid.rowid_row_number(rowid) "Row#"
5 from hr.employees
6 where employee_id = 100;
FIRST_NAME ObjectID File# Block# Row#
-------------------- ---------- ---------- ---------- ----------
Steven 73301 5 207 0
Now query DBA_OBJECTS
to find out what database object the row belongs to
SQL> select owner, object_name, object_type, data_object_id, object_id
2 from dba_objects
3* where data_object_id = 73301;
OWNER OBJECT_NAME OBJECT_TYPE DATA_OBJECT_ID OBJECT_ID
----------- --------------- -------------- --------------- ----------
HR EMPLOYEES TABLE 73301 73933
When does a ROWID change?
- If row movement is enabled: ROWID can change because of partition key updates, Flashback Table operations, shrink table operations, and so on.
- If row movement is disabled: ROWID can change if the row is exported and imported using Oracle Database utilities
Can you use ROWIDs as a table's primary key?
- No. Oracle won't let you create a PK constraint on a rowid column.
- In addition, besides not being always unique (rows in clustered tables may share a rowid), a row's rowid can change (i.e., after and export/import or partition movement operation).
Can I store ROWIDs in a table column?
- Yes. You can create table column using the
ROWID
data type.
- However, ROWID values should not be used across transaction lines.
- Storing a rowid with the intent of using it latter (i.e. as an audit trail record), may lead to errors and incorrect information, since rowids may change as the result of:
- Export/import operation
- ALTER TABLE...MOVE
- ALTER TABLE...SHRINK SPACE
- FLASHBACK TABLE...
- Partition operation (split, combination)
- Data update that results in row being moved to another partition