Understanding Oracle ROWID: Syntax, Use Cases & Best Practices

​1. Core Characteristics of ROWID

Syntax Definition

-- Example: Query ROWID and all columns in the employees table
SELECT ROWID, t.* FROM employees t;

‍

Key Characteristics​

• ​Uniqueness: ROWID is globally unique within a table, even after data updates (except during row migration).
• ​Physical Addressing: Decompose ROWID using the DBMS_ROWID package:

SELECT 
  DBMS_ROWID.ROWID_OBJECT(ROWID) AS object_id,
  DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) AS file_id,
  DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) AS block_id,
  DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) AS row_num
FROM employees;

‍

2. Common Pitfall: SELECT ROWID, * Syntax Error​

​Problem Reproduction

-- Invalid syntax: Mixing ROWID with * directly
SELECT ROWID, * FROM employees; -- ORA-00936: Missing expression

‍

​Root Cause​

• ​Grammar Restriction: Oracle requires * to be qualified with a table name/alias.
• ​Scope Ambiguity: ROWID (a pseudo-column) conflicts with implicit column resolution when used with *.

​Solution

-- Correct approach: Alias qualification
SELECT e.ROWID, e.* FROM employees e;

‍

3. Alternatives & Best Practices​

​Explicit Column Listing (Recommended)​

SELECT ROWID, employee_id, first_name, last_name 
FROM employees;

‍

Subquery Workaround (Legacy Code Compatibility)​​

SELECT ROWID, t.* 
FROM (SELECT * FROM employees) t;

‍

Key Recommendations​

• ​Always Use Aliases: Prevent SELECT ROWID, * syntax issues.
• ​Monitor Performance: ROWID is ideal for low-concurrency OLTP; use indexes for high-scale reads.
• ​Version Awareness: ROWID format changed in Oracle 12c with persistent ROWIDs—test upgrades carefully.

​Summary​

ROWID underpins Oracle’s row-level addressing but demands strict syntax adherence. By leveraging aliases, explicit column names, and monitoring tools, you can harness its efficiency while avoiding pitfalls.

Let me know if further refinements are needed! 😊

‍