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
- Third, specify an
expressionin parentheses after the
ASkeyword. The values of the virtual column will derive from the
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) );
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
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
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
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
Second, query data from the
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
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
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
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.