How to Use Pseudocolumns in Oracle

A pseudocolumn behaves like a column of every table, yet is not stored in any of them: Oracle computes it as the query runs. ROWNUM numbers the rows a query returns, ROWID gives each row's physical address, ORA_ROWSCN tells when a row last changed, and COLUMN_VALUE names the value of a collection used as a table. Each is useful, and ROWNUM in particular has a trap.

Code for This Guide

The main examples are in the examples/basic-elements folder of the Oracle Database 26ai code repository on GitHub, each with its output. They query NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

The Main Pseudocolumns

ROWNUM

ROWNUM numbers the rows as the query returns them. It is assigned before ORDER BY, so ROWNUM <= 3 with an ORDER BY in the same query returns three arbitrary rows, sorted. To number sorted rows, sort in a subquery and apply ROWNUM outside.

Example:

select rownum, airport_code, city
from   airports
where  rownum <= 3;

-- ROWNUM is assigned before ORDER BY: sort in a subquery first
select rownum, airport_code, elevation_ft
from   (select airport_code, elevation_ft from airports order by elevation_ft desc)
where  rownum <= 3;

Output:

   ROWNUM AIRPORT_CODE    CITY
_________ _______________ _________
        1 DXB             Dubai
        2 LHR             London
        3 CDG             Paris

   ROWNUM AIRPORT_CODE       ELEVATION_FT
_________ _______________ _______________
        1 JNB                        5558
        2 NBO                        5327
        3 KTM                        4390

The first query gets the first three rows the table happens to return. The second sorts first and so gets the three highest airports.

Why ROWNUM > 1 Returns Nothing

ROWNUM is given to a row only when the row passes the WHERE clause. The first row fetched is number 1, fails ROWNUM > 1, and is discarded; the next row then becomes number 1 and fails too, and so on. The condition can never be true. For a page of rows further down, use OFFSET and FETCH, which also need no subquery.

Example:

select count(*) as rows_found from airports where rownum > 1;

-- the second to fourth highest airports with FETCH: no subquery needed
select airport_code, elevation_ft
from   airports
order  by elevation_ft desc nulls last
offset 1 row fetch next 3 rows only;

Output:

   ROWS_FOUND
_____________
            0

AIRPORT_CODE       ELEVATION_FT
_______________ _______________
NBO                        5327
KTM                        4390
BLR                        3002

FETCH FIRST and OFFSET replace most uses of ROWNUM in new code.

ROWID

ROWID is the address of a row: the data object, file, block, and position within the block, encoded as an 18-character string. Looking a row up by ROWID is the fastest access there is. DBMS_ROWID splits it into its parts.

Example:

-- the ROWID of a row, its parts, and a lookup by ROWID
select airport_code, rowid as row_address,
       dbms_rowid.rowid_block_number(rowid) as block_no,
       dbms_rowid.rowid_row_number(rowid)   as row_in_block
from   airports
where  airport_code in ('DXB', 'SIN');

select airport_code, city
from   airports
where  rowid = (select rowid from airports where airport_code = 'SIN');

Output:

AIRPORT_CODE    ROW_ADDRESS              BLOCK_NO    ROW_IN_BLOCK
_______________ _____________________ ___________ _______________
DXB             AAAUejAAAAAAATdAAA           1245               0
SIN             AAAUejAAAAAAATdAAO           1245              14

AIRPORT_CODE    CITY
_______________ ____________
SIN             Singapore

Both airports are in the same block, rows 0 and 14. A ROWID is stable while the row exists, but it can change if the row moves, for example after a table is reorganized or a row moves between partitions, so use it within a transaction and never store it as a key.

ORA_ROWSCN and COLUMN_VALUE

ORA_ROWSCN is the system change number of the most recent change to a row. By default Oracle tracks it per data block, not per row, which is why two airports stored in the same block show the same SCN. A table created with ROWDEPENDENCIES tracks it per row. COLUMN_VALUE is the name Oracle gives the value of a collection of scalars, such as sys.odcinumberlist, when it is queried with TABLE().

Example:

select airport_code, ora_rowscn from airports where airport_code in ('DXB', 'SIN');

select column_value from table(sys.odcinumberlist(10, 20, 30));

Output:

AIRPORT_CODE       ORA_ROWSCN
_______________ _____________
DXB                   4798557
SIN                   4798557

   COLUMN_VALUE
_______________
             10
             20
             30

ORA_ROWSCN is handy for optimistic locking: read it with the row, and when you update, check that it has not changed in the meantime. The SCN values on your database will differ.

Related Guides

• How to Query Hierarchies with CONNECT BY in Oracle
• How to Query Past Data with Flashback Query in Oracle
• How to Use ORDER BY in Oracle Database 23ai Queries

Conclusion

Pseudocolumns are values Oracle computes for every row without storing them. ROWNUM numbers rows before ORDER BY and can never satisfy ROWNUM > 1, so prefer FETCH FIRST and OFFSET; ROWID is a row's address, fast for lookups but not a key; ORA_ROWSCN shows the last change, per block unless the table has ROWDEPENDENCIES; and COLUMN_VALUE names the values of a scalar collection.