Oracle virtual column

Begin oracle 11g, it can declare a new column type which is virtual column. It does not consume data storage space, only store in the table definition. It would be used the defined formula of virtual column as the selective result, when it is selected by app.

Example:

winkey@SQL>create table test1 (name varchar2(20), english number(5), math number(5), chinese number(5),
  2    sum generated always as (english+math+chinese) virtual);

Table created.

winkey@SQL>desc test1
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME						    VARCHAR2(20)
 ENGLISH					    NUMBER(5)
 MATH						    NUMBER(5)
 CHINESE					    NUMBER(5)
 SUM						    NUMBER

winkey@SQL>set line 150 
winkey@SQL>COL DATA_TYPE FOR A30
winkey@SQL>COL DATA_TYPE FOR A30
winkey@SQL>select column_name, data_type, data_default from user_tab_columns where table_name='TEST1';
COLUMN_NAME		       DATA_TYPE		      DATA_DEFAULT
------------------------------ ------------------------------ --------------------------------------------------------------------------------
NAME			       VARCHAR2
ENGLISH 		       NUMBER
MATH			       NUMBER
CHINESE 		       NUMBER
SUM			       NUMBER			      "ENGLISH"+"MATH"+"CHINESE"  /*virtual column*/


winkey@SQL>insert into test1 values('winkey', 100, 100, 100, 300);
insert into test1 values('winkey', 100, 100, 100, 300)             /*Virtual column can not be insert contents */
            *
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns


winkey@SQL>insert into test1 values('winkey', 100, 100, 100);    /* This method can not be insert contents */
insert into test1 values('winkey', 100, 100, 100)
            *
ERROR at line 1:
ORA-00947: not enough values


winkey@SQL>insert into test1(name, english, math, chinese) values('winkey', 100, 100, 100);  /* That is right */

1 row created.

winkey@SQL>commit;

Commit complete.






winkey@SQL>select * from test1;

NAME			ENGLISH       MATH    CHINESE	     SUM
-------------------- ---------- ---------- ---------- ----------
winkey			    100        100	  100	     300

winkey@SQL>

For virtual column, it can be managed as general column except for inserting. Such as creating index, primary key on the virtual column and so on.

winkey@SQL>select * from test1 where sum = 300;

NAME			ENGLISH       MATH    CHINESE	     SUM
-------------------- ---------- ---------- ---------- ----------
winkey			    100        100	  100	     300


Execution Plan
----------------------------------------------------------
Plan hash value: 4122059633

---------------------------------------------------------------------------
| Id  | Operation	  | Name  | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	  |	1 |    64 |	3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST1 |	1 |    64 |	3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("SUM"=300)

winkey@SQL>create index idx_sum_t1 on test1(sum);

Index created.

winkey@SQL>select * from test1 where sum = 300;

NAME			ENGLISH       MATH    CHINESE	     SUM
-------------------- ---------- ---------- ---------- ----------
winkey			    100        100	  100	     300


Execution Plan
----------------------------------------------------------
Plan hash value: 3856151525

------------------------------------------------------------------------------------------
| Id  | Operation		    | Name	 | Rows  | Bytes | Cost (%CPU)| Time	 |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |		 |     1 |    64 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST1	 |     1 |    64 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN	    | IDX_SUM_T1 |     1 |	 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("SUM"=300)