Oracle Virtual Column

Summary: in this tutorial, you will learn about Oracle virtual columns and how to use them in your database tables.

Introduction to the Oracle virtual column

A virtual column is a table column whose values are calculated automatically using other column values, or another deterministic expression.

Here is the syntax of a virtual column:

column_name [data_type] [GENERATED ALWAYS] AS (expression) [VIRTUAL]

In this syntax:

• First, specify the name ( column_name) of the virtual column.
• Second, specify the virtual column’s data type. If you omit the data type, the virtual column will take the data type of the result of the expression.
• Third, specify an expression in parentheses after the AS keyword. The values of the virtual column will derive from the expression.

Note that the GENERATED ALWAYS and VIRTUAL keywords are for clarity only.

This statement shows how to define a virtual column in the CREATE TABLE statement:

CREATE TABLE table_name (
    ...,
    virtual_column_name AS (expression)
);

And this statement illustrates how to add a virtual column to an existing table using the ALTER TABLE statement:

ALTER TABLE table_name
ADD (
    virtual_column_name AS (expression)
);

Oracle virtual column examples

Let’s take some examples of using virtual columns.

1) Creating a table with a virtual column example

First, create a table named parts which has a virtual column:

CREATE TABLE parts(
    part_id INT GENERATED ALWAYS AS IDENTITY,
    part_name VARCHAR2(50) NOT NULL,
    capacity INT NOT NULL,
    cost DEC(15,2) NOT NULL,
    list_price DEC(15,2) NOT NULL,
    gross_margin AS ((list_price - cost) / cost),
    PRIMARY KEY(part_id)
);

In this parts table, the gross_margin column is the virtual column whose values are derived from the list price and cost columns.

Second, insert some rows into the parts table:

INSERT INTO parts(part_name, capacity, cost, list_price)
VALUES('G.SKILL TridentZ RGB Series 16GB (2 x 8GB)', 16, 95,105);

INSERT INTO parts(part_name, capacity, cost, list_price)
VALUES('G.SKILL TridentZ RGB Series 32GB (4x8GB)', 32, 205,220);

INSERT INTO parts(part_name, capacity, cost, list_price)
VALUES('G.SKILL TridentZ RGB Series 16GB (1 x 8GB)', 8, 50,70);

Third, query data from the parts table:

SELECT * FROM parts;

Here is the output:

As you can see clearly from the output, the gross_margin virtual column has values calculated automatically from the values in the cost and list_price columns.

2) Adding a virtual column to an existing table example

First, add a new column named capacity_description to the parts table using the ALTER TABLE column:

ALTER TABLE parts
ADD (
    capacity_description AS (
            CASE 
                WHEN capacity <= 8 THEN 'Small' 
                WHEN capacity > 8 AND capacity <= 16 THEN 'Medium'
                WHEN capacity > 16 THEN 'Large'
            END)
)

The value of the capacity_description column is derived from the CASE expression.

Second, query data from the parts table:

SELECT * FROM parts;

Advantages and disadvantages of virtual columns

Virtual columns provide the following advantages:

• Virtual columns consume minimal space. Oracle only stores the metadata, not the data of the virtual columns.
• Virtual columns ensure the values are always in sync with the source columns. For example, if you have a date column as the normal column and have the month, quarter, and year columns as the virtual columns. The values in the month, quarter, and year are always in sync with the date column.
• Virtual columns help avoid using views to display derived values from other columns.

The disadvantage of virtual columns is:

• Virtual columns may reduce query performance because their values are calculated at run-time.

Virtual column limitations

These are limitations of virtual columns:

• Virtual columns are only supported in relational heap tables, but not in index-organized, external, object, cluster, or temporary tables.
• The virtual column cannot be an Oracle-supplied datatype, a user-defined type, or LOB or LONG RAW.

The expression in the virtual column has the following restrictions:

• It cannot refer to other virtual columns.
• It cannot refer to normal columns of other tables.
• It must return a scalar value.
• It may refer to a deterministic user-defined function, however, if it does, the virtual column cannot be used as a partitioning key column.

Show virtual columns of a table

To show virtual columns of a table, you query from the all_tab_cols view:

SELECT 
    column_name, 
    virtual_column,
    data_default
FROM 
    all_tab_cols
WHERE owner = '<owner_name>' 
AND table_name = '<table_name>';

If the value is the virtual_column is YES, it means that the corresponding column is a virtual column. Otherwise, it is a normal column.

The following statement lists all columns of the parts table, including the virtual columns:

SELECT 
    column_name, 
    virtual_column,
    data_default
FROM 
    all_tab_cols
WHERE owner = 'OT' 
AND table_name = 'PARTS';

In this tutorial, you have learned about the Oracle virtual columns and how to use them in database tables.