Virtual Columns

Tested on:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option


CREATE TABLE CMAGNO.TB_VIRTUAL 
(
  NUM NUMBER,
  TEXT VARCHAR2(200),
  VL_1 NUMBER,
  COL_V1     AS (VL_1 * 2),
  COL_V2     NUMBER GENERATED ALWAYS AS (VL_1 + (VL_1/2)) VIRTUAL,
  CONSTRAINT PK_TB_VIRTUAL PRIMARY KEY (NUM)
);

Table created.

CREATE INDEX CMAGNO.IX_TB_VIRTUAL_01 
ON CMAGNO.TB_VIRTUAL(COL_V1);

Index created.

CREATE INDEX CMAGNO.IX_TB_VIRTUAL_02 
ON CMAGNO.TB_VIRTUAL(COL_V2);

Index created.

desc CMAGNO.TB_VIRTUAL
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NUM                                       NOT NULL NUMBER
 TEXT                                               VARCHAR2(200)
 VL_1                                               NUMBER
 COL_V1                                             NUMBER
 COL_V2                                             NUMBER

COLUMN data_default FORMAT A50
SELECT column_name, data_default, DATA_TYPE,HISTOGRAM
FROM   dba_tab_columns
WHERE  table_name = 'TB_VIRTUAL';

COLUMN_NAME                    DATA_DEFAULT                                       DATA_TYPE  HISTOGRAM
------------------------------ -------------------------------------------------- ---------- ---------------
COL_V2                         "VL_1"+"VL_1"/2                                    NUMBER     NONE
COL_V1                         "VL_1"*2                                           NUMBER     NONE
VL_1                                                                              NUMBER     NONE
TEXT                                                                              VARCHAR2   NONE
NUM                                                                               NUMBER     NONE


declare 
i number;
begin

for i in 1..1000 loop
    insert into cmagno.tb_virtual
    (NUM, TEXT, VL_1)
    values
    (i,'TEXT:'||TO_CHAR(I),I*10);
end loop;
commit;

end;
/

COL TEXT FORMAT A30

SELECT * FROM 
cmagno.tb_virtual
WHERE 
ROWNUM<10;

       NUM TEXT                                 VL_1     COL_V1     COL_V2
---------- ------------------------------ ---------- ---------- ----------
       342 TEXT:342                             3420       6840       5130
       343 TEXT:343                             3430       6860       5145
       344 TEXT:344                             3440       6880       5160
       345 TEXT:345                             3450       6900       5175
       346 TEXT:346                             3460       6920       5190
       347 TEXT:347                             3470       6940       5205
       348 TEXT:348                             3480       6960       5220
       349 TEXT:349                             3490       6980       5235
       350 TEXT:350                             3500       7000       5250


SET AUTOTRACE on explain

SELECT * 
FROM CMAGNO.TB_VIRTUAL
WHERE COL_V1=6840;

       NUM TEXT                                 VL_1     COL_V1     COL_V2
---------- ------------------------------ ---------- ---------- ----------
       342 TEXT:342                             3420       6840       5130


Execution Plan
----------------------------------------------------------
Plan hash value: 3962365088

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

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

   2 - access("COL_V1"=6840)

Note
-----
   - dynamic sampling used for this statement (level=2)


 SET AUTOTRACE OFF;

CREATE VIEW CMAGNO.VW_TB_VIRTUAL
AS
SELECT NUM, TEXT,  COL_V1, COL_V1 * 2 COL_V1_DOUBLE
FROM
CMAGNO.TB_VIRTUAL;

View created.

DESC CMAGNO.VW_TB_VIRTUAL

 Name                Null?    Type
 ------------------- -------- -----------------
 NUM                 NOT NULL NUMBER
 TEXT                         VARCHAR2(200)
 COL_V1                       NUMBER
 COL_V1_DOUBLE                NUMBER

SELECT * FROM 
CMAGNO.VW_TB_VIRTUAL
WHERE ROWNUM < 10;

       NUM TEXT                               COL_V1 COL_V1_DOUBLE
---------- ------------------------------ ---------- -------------
       342 TEXT:342                             6840         13680
       343 TEXT:343                             6860         13720
       344 TEXT:344                             6880         13760
       345 TEXT:345                             6900         13800
       346 TEXT:346                             6920         13840
       347 TEXT:347                             6940         13880
       348 TEXT:348                             6960         13920
       349 TEXT:349                             6980         13960
       350 TEXT:350                             7000         14000

==> LIMITATIONS
       
UPDATE
CMAGNO.TB_VIRTUAL
SET COL_V1=1;     2    3
CMAGNO.TB_VIRTUAL
       *
ERROR at line 2:
ORA-54017: UPDATE operation disallowed on virtual columns

==> Virtual column can’t be used for:

    temporary table
    index-organized tables
    external objects
    clusters
   
==> Virtual column can be used for:

    heap-organized tables,
    partitioning,
    indexing,