Oracle 19c – SQL Access Path: ROWID Scan

In Oracle Database 19c, the Oracle Optimizer determines the best method to retrieve data from tables. These methods are called SQL access paths.

One of the fastest access paths is the ROWID Scan, where Oracle retrieves rows directly using their ROWID, which uniquely identifies the physical location of a row inside a datafile.

1. What is a ROWID?

A ROWID is a unique identifier automatically assigned to every row in an Oracle table. It represents the physical address of the row in the database.

A ROWID contains information about:

• Datafile number
• Data block number
• Row position within the block

Example of a ROWID:

SQL> select rowid, salary from employees where employee_id=200;

ROWID                  SALARY
------------------ ----------
AAAQTzAAEAAAAGnAAC       4500

2. Table Access by ROWID

In execution plans, a ROWID access appears as:

SQL> EXPLAIN PLAN SET statement_id = 'wadhah_sql_rowid_scan' FOR select salary from employees where rowid='AAAQTzAAEAAAAGnAAC';

Explained.

SQL>

Execution plan example:

SQL> select PLAN_TABLE_OUTPUT from TABLE(DBMS_XPLAN.DISPLAY(null,'wadhah_sql_rowid_scan'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 549062733

----------------------------------------------------------------------------------------
| Id  | Operation                  | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |           |     1 |    16 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY USER ROWID| EMPLOYEES |     1 |    16 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

8 rows selected.

SQL>

3. Advantages of ROWID Access

• Extremely fast row retrieval
• Direct access to physical storage
• Efficient for index-based queries
• Reduces unnecessary table scans

4. Limitations

ROWIDs can change in certain situations:

• Table reorganization
• Export/Import
• Table move
• Shrink operations

Therefore, ROWIDs should not be stored permanently in applications.