Lesson 32 - Virtual Columns

Virtual Column

We can create virtual columns in tables as an expression. These kind of columns do not consume  any spaces in disk as it is computed at the run time.

Syntax  :

column_name datatype  GENERATED ALWAYS AS expression  VIRTUAL

datatype, GENERATED ALWAYS and VIRTUAL are optional key words.

If  we omitt datatype,  it is determined based on the result of the expression given.

The expression  should not be another virtual column  and it should refer only columns defined in same table.

And output of the expression must be a scalar value. It  should not be Oracle supplied datatype, a user-defined type, or LOB or LONG RAW.

Example :

SQL> create table salary_master(emp_id number(5),salary number(6),Tax NUMBER GENERATED ALWAYS AS  (salary*30/100) VIRTUAL);

Table created.

SQL> insert into salary_master(emp_id,salary) values(1,45000);

1 row created.

SQL>  insert into salary_master(emp_id,salary) values(2,75000);

1 row created.

SQL> insert into salary_master(emp_id,salary) values(3,85000);

1 row created.

SQL> insert into salary_master(emp_id,salary) values(4,25000);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from salary_master;

    EMP_ID     SALARY        TAX

---------- ---------- ----------
         1      45000      13500
         2      75000      22500
         3      85000      25500
         4      25000       7500

Points to remember with Virtual columns

1)  Constraints can be created on virtual columns.

2) DML operations are not allowed

3) function-based indexes can be created on these columns.