Oracle SQL: Pseudo-Columns

Oracle SQL pseudo-columns function like regular table columns but do not store data physically on disk. They are useful for administrative, navigational, and sequence-based queries. Key pseudo-columns include ROWID, ROWNUM, NEXTVAL, CURRVAL, COLUMN_VALUE, ORA_ROWSCN, and UID and USER.

ROWID provides a unique physical address for a row, while ROWNUM assigns a sequential integer to each row returned by a query. NEXTVAL and CURRVAL retrieve values from sequence objects. COLUMN_VALUE is used for collection types or table functions, ORA_ROWSCN returns the conservative upper bound System Change Number (SCN) of the most recent change, and UID and USER return the current user ID and username.

Pseudo-columns are assigned after WHERE clause evaluation but before ORDER BY and GROUP BY. For hierarchical queries, CONNECT_BY pseudo-columns like CONNECT_BY_ISLEAF, CONNECT_BY_ISCYCLE, and SYS_CONNECT_BY_PATH are used to organize nodes into a tree structure. These pseudo-columns help inspect tree structures and manage loops.

Pseudo-columns cannot be created or indexed directly, but virtual columns, introduced in Oracle 11g, allow custom expression-based columns that can be indexed. Virtual columns differ from pseudo-columns as they are stored in the table metadata and can be physically part of the table.