Oracle Virtual Columns: A Powerful Feature in Database Design

Oracle 11g introduced the concept of Virtual Columns, a unique feature that allows dynamic calculations within a table. These columns behave like regular table columns but with some significant differences, making them highly useful for specific use cases.

What Are Virtual Columns?

Virtual Columns are defined by an expression, and their values are computed at runtime when queried. Unlike regular columns, the values of Virtual Columns are not physically stored in the database but are derived dynamically based on the specified expression.

Key Characteristics

• Read-Only: The values of Virtual Columns are read-only and cannot be updated using DML operations (e.g., UPDATE statements).
• Expression-Based: Their values are derived from an expression defined during table creation.
• Storage: The values are computed dynamically and are not stored physically.
• Data Type: Oracle determines the data type of the Virtual Column based on the result of the expression if not explicitly defined.

Syntax for Defining a Virtual Column

column_name [datatype] [GENERATED ALWAYS] AS [expression] [VIRTUAL]
        

The parameters in square brackets ([ ]) are optional. If the data type is omitted, Oracle infers it from the expression.

Features of Virtual Columns

Virtual Columns, despite their differences, share many functionalities with regular columns:

• Query Support: They can be used in the WHERE clause of SELECT, UPDATE, and DELETE statements.
• Statistics: You can collect statistics on Virtual Columns for query optimization.
• Partitioning: They can serve as partition keys in virtual column-based partitioning.
• Indexing: Function-based indexes can be created on Virtual Columns.
• Constraints: Constraints such as primary keys or unique keys can be applied to them.

Creating a Virtual Column: Example

Let’s create a table with a Virtual Column to demonstrate its usage:

CREATE TABLE VTEST (
    emp_id       NUMBER,
    emp_no       VARCHAR2(50),
    monthly_sal  NUMBER(10,2),
    bonus        NUMBER(10,2),
    tot_sal      NUMBER(10,2) GENERATED ALWAYS AS (monthly_sal * 10 + bonus)
);
        

Here, the Virtual Column tot_sal dynamically calculates the total salary based on the expression (monthly_sal * 10 + bonus). Note: Unlike the DEFAULT clause, you can reference other columns in the expression for Virtual Columns.

Checking Virtual Columns in Data Dictionary

You can use the USER_TAB_COLS view to check details about columns, including whether they are virtual:

SELECT column_name, data_type, data_length, data_default, virtual_column
FROM user_tab_cols
WHERE table_name = 'VTEST';
        

Output:

Working with Virtual Columns

When inserting data into a table with Virtual Columns, you only provide values for non-virtual columns. The Virtual Column’s value is computed automatically.

Example: Inserting and Querying Data

-- Insert data into the table
INSERT INTO VTEST (emp_id, emp_no, monthly_sal, bonus) VALUES (1, 'arvind', 10000, 1000);
INSERT INTO VTEST (emp_id, emp_no, monthly_sal, bonus) VALUES (2, 'reddy', 20000, 2000);

-- Commit the transaction
COMMIT;

-- Query the table
SELECT * FROM VTEST;
        

Output:

Key Notes

• Dynamic Calculation: Virtual Columns calculate values on-the-fly and do not store redundant data.
• Partitioning and Indexing: They can be used for partition keys and support function-based indexing.

Conclusion

Virtual Columns in Oracle are a versatile feature for dynamically computing values. By leveraging features like indexing, constraints, and partitioning, Virtual Columns enhance database design while maintaining efficiency.