Oracle SQL: Pseudo-Columns

1. Overview & Core Concepts

• Definition: A pseudo-column behaves like a table column, but it is not actually stored on disk in the table.
• Capabilities & Restrictions: You can SELECT from pseudo-columns, but you cannot perform INSERT, UPDATE, or DELETE operations on their values.

2. Common Oracle Pseudo-Columns

Oracle provides several built-in pseudo-columns for administrative, navigational, and sequence-based queries:

1. ROWID: Returns the unique physical address of a row within a database table.
2. ROWNUM: Assigns a sequential integer (starting from 1) to each row returned by a query result set.
3. NEXTVAL: Retrieves the next available value from a sequence object.
4. CURRVAL: Retrieves the current value of a sequence in the current session.

5. COLUMN_VALUE: Used primarily when querying collection types or table functions like XMLTABLE.

   • *Example:*SQL
     

     SELECT column_value
     FROM (XMLTABLE('<a>123</a>'));
     

6. ORA_ROWSCN: Returns the conservative upper bound System Change Number (SCN) of the most recent change made to the row. Useful for tracking data staleness or building flashback implementations.

7. UID & USER: Returns the unique integer user ID and the username of the current user session.

   • *Example Output from Dual Table:*SQL
     

     SELECT uid, user FROM dual;
     

     UID	USER
     102	HR

8. LEVEL: Used with the SELECT ... CONNECT BY hierarchical query clause to organize flat rows into a tree structure. It returns the current depth level of a node within the hierarchy.

3. Hierarchical Queries & Tree Pseudo-Columns (CONNECT BY)

When working with hierarchical data (such as an employee-manager reporting line), Oracle provides specialized pseudo-columns to inspect tree structures:

Example: Hierarchical Query Structure

SQL

SELECT
    ename,
    empno,
    CONNECT_BY_ISLEAF,
    CONNECT_BY_ISCYCLE,
    LEVEL,
    SYS_CONNECT_BY_PATH(ename, '->')
FROM
    emp
CONNECT BY NOCYCLE
    PRIOR empno = mgr;

Explanation of Hierarchical Pseudo-Columns used in Example:

• CONNECT_BY_ISLEAF: Returns 1 if the current row is a leaf node (has no children in the tree structure), and 0 otherwise.
• CONNECT_BY_ISCYCLE: Returns 1 if the current row has a child that is an ancestor of itself (a loop in the hierarchy), and 0 otherwise. (Requires the NOCYCLE keyword in the CONNECT BY clause to avoid infinite loops).
• SYS_CONNECT_BY_PATH(column, char): Returns the path of column values from the root to the current node, separated by the specified character string.

4. Advanced Interview Insights & Frequently Asked Questions

NOTE: Interviewers heavily test pseudo-columns, especially regarding execution order and limitations.

• What is the evaluation order of ROWNUM in a query?
  • ROWNUM is assigned after the WHERE clause is evaluated, but before ORDER BY and GROUP BY clauses. This is why filtering with ROWNUM > 1 directly will always return zero rows (since row 1 fails the condition, it is discarded, and the next row becomes row 1). To handle pagination safely with ROWNUM, you must use an inline view or subquery that encapsulates the ORDER BY.
• Can you create your own custom pseudo-columns?
  • No, pseudo-columns are system-defined by Oracle. However, virtual columns (introduced in Oracle 11g) allow you to define custom expression-based columns that are stored logically or physically as metadata.
• What is the difference between ROWID and ROWNUM?
  • ROWID is a permanent, unique physical address of a row on disk (stable across transactions until the row is deleted or moved). ROWNUM is temporary, dynamic, and assigned on-the-fly to rows as they are fetched into the result set during statement execution.
• Can you create an index on a ROWNUM or ROWID pseudocolumn?
  • Answer: You cannot index ROWNUM, but ROWID acts as the implicit physical primary address of every row and is inherently indexed via the rowid access path. You can, however, create indexes on Virtual Columns.
• What is the purpose of CONNECT_BY_ISCYCLE?
  • Answer: When performing hierarchical queries with loops in the data, CONNECT_BY_ISCYCLE returns 1 if the current row has a child that is also its ancestor (a loop), preventing infinite loops when paired with the NOCYCLE clause.

IMPORTANT NOTES FOR INTERVIEW:

• Virtual columns do not consume storage space for data (unlike regular columns), but metadata is stored in the dictionary, and they can be indexed to improve query performance.
• ROWNUM is assigned before any sorting or aggregation happens in a query. If you use ORDER BY with ROWNUM, rows are numbered arbitrarily unless wrapped in a subquery.

---

💡 Practical Code Example: Hierarchical Queries with Pseudocolumns

SQL

SELECT
    ename,
    empno,
    CONNECT_BY_ISLEAF,
    CONNECT_BY_ISCYCLE,
    LEVEL,
    SYS_CONNECT_BY_PATH(ename, '->') AS hierarchy_path
FROM emp
CONNECT BY NOCYCLE PRIOR empno = mgr;

⚖️ Difference: Pseudocolumn vs. Virtual Column

Feature	Pseudocolumn	Virtual Column
Table Storage	Not part of the table definition/storage.	Physically part of the table metadata structure, though values are derived on-the-fly.
Value Derivation	Generated dynamically by the query engine (e.g., ROWNUM, LEVEL).	Derived from expressions/other columns of the same table.
Indexing	Cannot be indexed directly.	Can be indexed (including B-tree and function-based indexes).
DML Operations	Selection only (No INSERT/UPDATE/DELETE).	Selection only; values are managed automatically by the database via expressions (No direct DML on the virtual column).

🏗️ Table Structure Example: Virtual Columns

CREATE TABLE Test (
    a   NUMBER,
    b   NUMBER,
    SUM AS (a + b),  -- Virtual Column
    Sub AS (a - b),  -- Virtual Column
    Mul AS (a * b),  -- Virtual Column
    Div AS (a / b)   -- Virtual Column
);