Virtual Columns in Oracle 11g

Introduction:

Oracle 11g introduced the concept of ‘Virtual Column’ in a table. In the old versions of oracle db, when we want to use expressions and computations we create database views and if we want to create index for that expression we create Function-Based Indexes. In Oracle 11g we can store those expressions in the tables themselves as virtual columns.

Key Points about the Virtual Columns:

• The virtual columns are defined by an expression. The result of evaluation of this expression becomes the value of the column.
• The values of the virtual column are not stored in the database. Rather, it’s computed at run-time when you query the data.
• We cannot explicitly add data to virtual columns.
• Virtual columns are valid for indexes and constraints. Indexes on virtual columns are essentially function-based indexes.
• Virtual columns can be added after table creation with an ALTER TABLE statement. 
• Virtual columns can include in PL/SQL functions, the only restriction is that the function must be deterministic. The function itself can be standalone or in a package.
• We can create comments on virtual columns in the same way as physical columns.
• Virtual columns do not consume any table storage other than the small amount of metadata in the data dictionary.
• PL/SQL treats virtual columns in the same way as physical columns. This means, of course, that we can fetch them, reference them and use them as datatype anchors.
• Virtual columns can be used in key constraints. They can also be used as partition keys in partitioned tables.
• Virtual columns can be used in the WHERE clause of UPDATE and DELETE statement but they can’t be modified by DML.
• Statistics can be collected on them.
• Tables containing virtual columns can still be eligible for result caching.

Why to use Virtual Columns?

Virtual Columns are useful in those situations where values derived from the real column values are frequently required, yet would be a denormalization in the data design when implemented as real column. In the EMP table, this applies for example to INCOME (the sum of SAL and COMM), HIREYEAR (the year component of the HIREDATE). Using Virtual Columns will reduce the need for Views to provide derived column values.When queried, virtual columns appear to be normal table columns, but their values are derived rather than being stored on disc.

What is the syntax?

The syntax for defining a virtual column is listed below.

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

Where:

• If the datatype is omitted, it is determined based on the result of the expression.
• The GENERATED ALWAYS and VIRTUAL keywords are provided for clarity only.

Example:

Create a table with virtual columns:

Introduction:

Oracle 11g introduced the concept of ‘Virtual Column’ in a table. In the old versions of oracle db, when we want to use expressions and computations we create database views and if we want to create index for that expression we create Function-Based Indexes. In Oracle 11g we can store those expressions in the tables themselves as virtual columns.

Key Points about the Virtual Columns:

• The virtual columns are defined by an expression. The result of evaluation of this expression becomes the value of the column.
• The values of the virtual column are not stored in the database. Rather, it’s computed at run-time when you query the data.
• We cannot explicitly add data to virtual columns.
• Virtual columns are valid for indexes and constraints. Indexes on virtual columns are essentially function-based indexes.
• Virtual columns can be added after table creation with an ALTER TABLE statement. 
• Virtual columns can include in PL/SQL functions, the only restriction is that the function must be deterministic. The function itself can be standalone or in a package.
• We can create comments on virtual columns in the same way as physical columns.
• Virtual columns do not consume any table storage other than the small amount of metadata in the data dictionary.
• PL/SQL treats virtual columns in the same way as physical columns. This means, of course, that we can fetch them, reference them and use them as datatype anchors.
• Virtual columns can be used in key constraints. They can also be used as partition keys in partitioned tables.
• Virtual columns can be used in the WHERE clause of UPDATE and DELETE statement but they can’t be modified by DML.
• Statistics can be collected on them.
• Tables containing virtual columns can still be eligible for result caching.

Why to use Virtual Columns?

Virtual Columns are useful in those situations where values derived from the real column values are frequently required, yet would be a denormalization in the data design when implemented as real column. In the EMP table, this applies for example to INCOME (the sum of SAL and COMM), HIREYEAR (the year component of the HIREDATE). Using Virtual Columns will reduce the need for Views to provide derived column values.When queried, virtual columns appear to be normal table columns, but their values are derived rather than being stored on disc.

What is the syntax?

The syntax for defining a virtual column is listed below.

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

Where:

• If the datatype is omitted, it is determined based on the result of the expression.
• The GENERATED ALWAYS and VIRTUAL keywords are provided for clarity only.

Example:

Create a table with virtual columns:

Introduction:

Oracle 11g introduced the concept of ‘Virtual Column’ in a table. In the old versions of oracle db, when we want to use expressions and computations we create database views and if we want to create index for that expression we create Function-Based Indexes. In Oracle 11g we can store those expressions in the tables themselves as virtual columns.

Key Points about the Virtual Columns:

• The virtual columns are defined by an expression. The result of evaluation of this expression becomes the value of the column.
• The values of the virtual column are not stored in the database. Rather, it’s computed at run-time when you query the data.
• We cannot explicitly add data to virtual columns.
• Virtual columns are valid for indexes and constraints. Indexes on virtual columns are essentially function-based indexes.
• Virtual columns can be added after table creation with an ALTER TABLE statement. 
• Virtual columns can include in PL/SQL functions, the only restriction is that the function must be deterministic. The function itself can be standalone or in a package.
• We can create comments on virtual columns in the same way as physical columns.
• Virtual columns do not consume any table storage other than the small amount of metadata in the data dictionary.
• PL/SQL treats virtual columns in the same way as physical columns. This means, of course, that we can fetch them, reference them and use them as datatype anchors.
• Virtual columns can be used in key constraints. They can also be used as partition keys in partitioned tables.
• Virtual columns can be used in the WHERE clause of UPDATE and DELETE statement but they can’t be modified by DML.
• Statistics can be collected on them.
• Tables containing virtual columns can still be eligible for result caching.

Why to use Virtual Columns?

Virtual Columns are useful in those situations where values derived from the real column values are frequently required, yet would be a denormalization in the data design when implemented as real column. In the EMP table, this applies for example to INCOME (the sum of SAL and COMM), HIREYEAR (the year component of the HIREDATE). Using Virtual Columns will reduce the need for Views to provide derived column values.When queried, virtual columns appear to be normal table columns, but their values are derived rather than being stored on disc.

What is the syntax?

The syntax for defining a virtual column is listed below.

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

Where:

• If the datatype is omitted, it is determined based on the result of the expression.
• The GENERATED ALWAYS and VIRTUAL keywords are provided for clarity only.

Example:

Create a table with virtual columns:

CREATE TABLE employees (

  emp_id      NUMBER PRIMARY KEY,

  first_name  VARCHAR2(30),

  last_name   VARCHAR2(30),

  salary      NUMBER(9,2),

  doj         DATE,

  comm        NUMBER(3),

  yoj         GENERATED ALWAYS AS (to_char(doj,'yyyy')) VIRTUAL,

  salary_pack NUMBER GENERATED ALWAYS AS (ROUND(salary*(1+comm/100),2)) VIRTUAL

);

 

You can view the virtual columns by describing the table.

You need to explicitly reference the physical columns in the insert statements. Otherwise you will get the below error:

We cannot explicitly add data to virtual columns. If you try, you will get the below error:

Hence the proper insert statement will be:
INSERT INTO employees (emp_id,first_name,last_name,salary,doj,comm) VALUES (100,’James’,’Anderson’, 10000,’01-JAN-2000′,4);
Now we have data in our example table, we can query our virtual column, as follows.

Our expression is evaluated at runtime and gives the output we see above.
Virtual columns can be used in the WHERE clause of UPDATE and DELETE statement but they can’t be modified by DML.

UPDATE employees SET YOJ='2001' WHERE EMP_ID=100;

--Not allowed.

--ORA-54017: UPDATE operation disallowed on virtual columns

UPDATE employees SET SALARY='11000' WHERE YOJ='2000';

--Allowed. The record will be updated.

 

Limitations on Virtual Columns:

• A virtual column can only be of scalar datatype or XMLDATATYE. It can’t be a user defined type, LOB or RAW.
• It can only refer to columns defined in the same table.
• Virtual columns can be referenced in the WHERE clause of updates and deletes, but they cannot be manipulated by DML.
• The virtual column expression can’t reference any other virtual column.
• Virtual columns can only be created on ordinary tables. They can’t be created on index-organized, external, object, cluster or temporary tables.
• If a deterministic function is used as virtual column expression, that virtual column can’t be used as a partitioning key for virtual column-based partitioning.