Virtual Columns in a Table

When queried, virtual columns appear to be normal table columns, but their values are derived rather than being stored on disc. The virtual column is also listed in the data dictionary. That column is not physically created but calculated always when accessed. It has the advantage of not having multiple schema objects, and another advantage is the optimizer can gather histograms on the column if needed or you may create an index on the column and also partition on the virtual column if desired.

The syntax for defining a virtual column is listed below.

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

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.

eg:

alter table plch_employees add (
   co_level varchar2(1) generated always as (
      case
         when upper(title) like 'C_O' then 'Y'
         else 'N'
      end
   ) virtual
)
/

eg:Below is an example creating a virtual column.

CREATE TABLE employees (
  id          NUMBER,
  first_name  VARCHAR2(10),
  last_name   VARCHAR2(10),
  salary      NUMBER(9,2),
  comm1       NUMBER(3),
  comm2       NUMBER(3),
  salary1     AS (ROUND(salary*(1+comm1/100),2)),
  salary2     NUMBER GENERATED ALWAYS AS (ROUND(salary*(1+comm2/100),2)) VIRTUAL,
  CONSTRAINT employees_pk PRIMARY KEY (id)
);

INSERT INTO employees (id, first_name, last_name, salary, comm1, comm2)
VALUES (1, 'JOHN', 'DOE', 100, 5, 10);