Databases Reference
In-Depth Information
Oracle ROWID
Every row in every table has an address. The address of a row is determined from a combination of the
following:
•
Data file number
•
Block number
•
Location of the row within the block
•
Object number
You can display the address of a row in a table by querying the ROWID pseudo-column. For example,
SQL> select rowid, cust_id from cust;
Here is some sample output:
ROWID CUST_ID
------------------ ----------
AAA3AIAABAAAHtQADE 27105
The
ROWID
pseudo-column value isn't physically stored in the database. Oracle calculates its value when
you query it. The
ROWID
contents are displayed as base 64 values that can contain the characters A-Z, a-
z, 0-9, +, and /. You can translate the
ROWID
value into meaningful information via the
DBMS_ROWID
package. For example, to display the relative file number in which a row is stored, issue this statement:
SQL> select dbms_rowid.rowid_relative_fno(rowid), cust_id from cust where cust_id=27105;
Here is some sample output:
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) CUST_ID
------------------------------------ ----------
5 27105
You can use the
ROWID
value in the
SELECT
and
WHERE
clauses of a SQL statement. In most cases, the
ROWID
uniquely identifies a row. However, it's possible to have rows in different tables that are stored in
the same cluster and so contain rows with the same
ROWID
.
Scenario 3: Only the Table Blocks Are Accessed
In some situations, even if there is an index, Oracle will determine that it's more efficient to use only the
table blocks. When Oracle inspects every row within a table, this is known as a full table scan. For
example, take this query:
SQL> select * from cust;
