Back to articles list
- 5 minutes read

Virtual Column

The concept of views and function-based indexes has been known for many years. One of the brand new solutions is a virtual column – a feature introduced in Oracle 11g. Apart from database giant, some well known DB vendors, like MariaDB and SQL Server, support the idea of computed columns. So let’s give virtual columns a try and examine their basic usage.

Generally, there are two kinds of virtual columns:

  1. Virtual virtual column
  2. Persistent virtual column

A virtual virtual column is a column whose value is computed as a reaction for a defined operation. This means that they are computed “on the fly” – only when needed. It’s accessible like any other column except that there is no physical space associated with it. If a SQL query doesn’t reference a virtual column, the value is not calculated.

A persistent virtual column could be considered as a half virtual half regular column. The main difference is that computed values are stored in the database (The value is computed once on insert or on update. It doesn’t have to be computed in every select statement).

Oracle supports only virtual virtual columns, not persistent virtual columns.

Of course, it’s impossible to insert a value into a virtual column. The attempt will cause a SQL error:

SQL Error: ORA-54013: -  "INSERT operation disallowed on virtual columns"

Syntax:

create table table_name ( ...
            ...
	column_name [datatype] [GENERATED ALWAYS] AS (expression) [VIRTUAL]
);

Keyword AS is sufficient to create a virtual column.

Others: GENERATED ALWAYS and VIRTUAL are optional as well as datatype. If datatype is omitted, the virtual column datatype is based on the result of the expression.

Example:

Let’s try an example. You can insert some data in a table called product.




CREATE TABLE product (
   id number(5),
   name varchar2(50),
   price number(5,2),
   vat number(5,2),
   price_including_vat as (price + price * vat),
   CONSTRAINT product_pk PRIMARY KEY (id)
);


The column price_including_vat will be computed as defined in a table creation statement. For example:

insert into product values (1, 'book', 20, 0.07);

The corresponding row will look as follows:

id name price vat price_including_vat
1 book 20 0.07 21.4

Getting Information About a Virtual Column

To get information about an expression that produces the value of a virtual column, check the data_default column in view USER_TAB_COLS:

SELECT column_name , data_type, data_default
FROM   USER_TAB_COLS
WHERE  table_name = 'PRODUCT';

Virtual Column

Virtual Column and Function-Based Indexes

A function-based index computes the value of an expression that involves one or more columns and stores it in the index. They improve the performance of queries that use the index expression.While creating a function-based index, Oracle adds an extra virtual column to store the value of the indexed expression.

We can easily verify that the extra column is virtual and ‘hidden’ (we can’t retrieve the value from this column using a select statement from that table) by checking the view USER_TAB_COLS, which describes the columns of the tables, views and clusters owned by the current user.

Note: It’s possible to create an index on a virtual column, but we can’t create a function-based index on the same expression.

Take a look at the example table:

create table t1(a varchar2(100), b number);

While retrieving information about columns in the table, we receive:

select column_name, column_id, internal_column_id, hidden_column, virtual_column
from user_tab_cols
where table_name = 'T1';

Virtual virtual column

Let’s create a function-based index:

create index idx1 on t1(upper(a));

After creation of index, the situation looks as follows. We have another extra column.

select column_name, column_id, internal_column_id, hidden_column, virtual_column
from user_tab_cols
where table_name = 'T1';

Persistent virtual column, function-based index

Advantages:

A virtual column in a table provides benefits in disk space utilization. It only stores metadata. It doesn’t consume physical space (only the small amount of metadata in the data dictionary), but it can be indexed (any indexes created on virtual columns will require storage space). It can also contain optimizer statistics and histograms. Using virtual columns in tables also eliminates the need to use views to display derived column values.

Limitations:

Virtual column can be used for: Virtual column can’t be used for:
  • heap-organized tables,
  • partitioning,
  • indexing,
  • temporary table
  • index-organized tables
  • external objects
  • clusters

It can be part of:

  • primary key,
  • foreign key,
  • partitioning key (in exception, when the expression that generate virtual column contains a PL/SQL deterministic function).

According to the Oracle documentation, the expression used in the virtual column definition has the restrictions:

  • It cannot refer to another virtual column by name.
  • It can only refer to columns defined in the same table.
  • If it refers to a deterministic user-defined function, it cannot be used as a partitioning key column.
  • The output of the expression must be a scalar value. It cannot return an Oracle supplied datatype, a user-defined type, LOB or LONG RAW.
  • It is impossible to reference to other virtual columns.

Virtual Column Based Partitioning

Partitioning is used to decompose large tables and indexes into smaller and more manageable pieces. Since the release of version Oracle 11g (Partitioning is enabled in Oracle Enterprise Edition), you can use a virtual column as a partitioning key.

Example:

create table employee (
  id number(5),
  name varchar2(50),
  surname varchar2(50),
  birth_date date,
  hired_date date,
  age_hired as floor((months_between(birth_date, hired_date) /12)),
  CONSTRAINT employee_pk PRIMARY KEY (id)
   )
      partition by range (age_hired)
  (
      partition young values less than (30),
      partition average values less than (40),
      partition old values less than maxvalue
  );

Virtual Column vs. View

It’s of course possible to create an analogical virtual column by creating a view with an appropriate expression.

What we don’t have in the ‘view’ approach are statistics and histograms.

go to top