DEV Community

SANDEEP KUMAR
SANDEEP KUMAR

Posted on

Oracle SQL: Unused Columns & Virtual Columns

#oracle #oraclesql #sql #plsql

1. Unused Columns (SET UNUSED)

Overview & Concept

  • Definition: Marking a column as UNUSED is a form of logical deletion. The column metadata is hidden, but the physical data remains untouched in the table blocks.
  • Performance Benefit: Unlike a traditional DROP COLUMN (which locks the table and rewrites every data block to reclaim space immediately), SET UNUSED is an instant metadata-only operation. This prevents long locks and heavy I/O overhead on large production tables.

Key Characteristics

  • Metadata Hiding: Once a column is marked as unused, it cannot be viewed via the DESC command or standard SELECT * queries.
  • Reusing Column Names: Because Oracle internally renames unused columns in the data dictionary with system-generated names (e.g., SYS_00001_...), you can immediately create a new column with the same name on the table.
  • Reversibility: You cannot directly "un-set" an unused column using a simple undo command. To reclaim space permanently, the column must eventually be dropped.

Core SQL Operations & Examples

  • 1. Mark a Column as Unused:SQL

    ALTER TABLE cust_details
SET UNUSED (cust_account_number

  • 2. View Unused Columns:SQL

    To inspect unused columns in a table, query data dictionary views such as DBA_UNUSED_COL_TABS, USER_UNUSED_COLS, or use session settings:

    SET COLINVISIBLE ON;
DESC cust_details;
*(Alternatively, query `USER_UNUSED_COLS` to see table names and counts of unused columns).*

  • 3. Drop All Unused Columns Permanently:SQL

    This command physically removes the data and reclaims disk space. It can be resource-intensive on large tables:

    ALTER TABLE cust_details
DROP UNUSED COLUMNS;

2. Virtual Columns

Overview & Concept

  • Definition: A virtual column is a derived column defined in a table with an underlying expression that computes values from other physical columns in the same row.
  • Storage Behavior: Virtual columns do not store data physically on disk by default. Instead, the database evaluates the expression dynamically at runtime. (Note: Oracle also supports STORED virtual columns if persistence is required for indexing or performance).

Rules, Limitations, and Restrictions

  • DML Restrictions: You cannot perform direct DML operations (INSERT, UPDATE, DELETE) on virtual columns. Their values are managed entirely by the underlying expression.
  • Table Type Support: Virtual columns can only be defined on Heap-organized tables. They are not supported for external tables, Index-Organized Tables (IOT), clustered tables, or temporary tables.
  • Dependencies: A virtual column cannot reference another virtual column; it must reference physical columns within the same table.
  • Data Type: The expression for a virtual column must evaluate to a scalar data type only.
  • Partitioning: Virtual columns can be used as partition keys (e.g., partitioning a table by a year extracted from a date column).

Indexing Virtual Columns

  • You can define indexes on virtual columns.
  • These indexes behave identically to Function-Based Indexes, allowing the optimizer to pre-calculate and index the results of the virtual column expression for faster retrieval.

SQL Syntax & Example

SQL

CREATE TABLE t1 (
    id   NUMBER,
    col1 NUMBER,
    col2 NUMBER,
    col3 NUMBER GENERATED ALWAYS AS (col1 * col2) VIRTUAL
);
Enter fullscreen mode Exit fullscreen mode

3. Senior Architect & Interview Masterclass

  • What is the difference between DROP COLUMN and SET UNUSED?
    • DROP COLUMN immediately removes the column definition and physically purges the data from all blocks, which can cause severe locking and performance degradation on large tables.
    • SET UNUSED instantly updates the data dictionary metadata, marking the column as dead without touching the physical blocks. Space reclamation is deferred until ALTER TABLE ... DROP UNUSED COLUMNS is executed manually (often scheduled during maintenance windows)[cite: 5].
  • Can you add a primary key constraint or unique constraint on a Virtual Column?
    • Yes! If the underlying expression guarantees uniqueness or non-null behavior, you can add constraints (like NOT NULL or UNIQUE) and create indexes on virtual columns, making them extremely powerful for enforcing business rules without duplicating storage.
  • What happens to indexes when you mark a column as UNUSED?
    • If the column was part of any indexes, those indexes become unusable/invalid and must be dropped or rebuilt after dropping the unused columns permanently.

Top comments (0)