Oracle Virtual Columns

With Oracle 11g, you can create virtual columns in tables. Values are derived on the fly and not stored.

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

If datatype is omitted, it is determined based on the expression.

 

   1:  ALTER TABLE EMPLOYEES2

   2:  ADD

   3:  (

   4:     CEO_IND VARCHAR2(1) AS (CASE WHEN EMPLOYEE_ID=100 THEN 'Y' ELSE NULL END)

   5:  )

   1:  SELECT * FROM EMPLOYEES2

   2:  WHERE CEO_IND='Y'

   1:  ALTER TABLE EMPLOYEES2

   2:  ADD

   3:  (

   4:     HML_SALARY_IND VARCHAR2(1) AS (CASE WHEN SALARY <=10000 THEN 'L' WHEN SALARY >10000 AND SALARY <=20000 THEN 'M' WHEN SALARY >20000 THEN 'H' ELSE NULL END)

   5:  )

Virtual columns cannot be based on other virtual columns

   1:  ALTER TABLE EMPLOYEES2

   2:  ADD

   3:  (

   4:     ELIGIBLE_BONUS AS (CASE WHEN CEO_IND='Y' THEN SALARY*3 ELSE SALARY*0.5 END)

   5:  )

   6:   

   7:  Error at line 1

   8:  ORA-54012: virtual column is referenced in a column expression

No DMLs are permitted on virtual columns

   1:  INSERT INTO  EMPLOYEES2(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)

   2:  VALUES (207, 'John', 'Doe', 'JDOE', '515.123.8182', TO_DATE('6/10/2002', 'MM/DD/YYYY'), 'MK_MAN', 15000, 0, 100, 20 )

   1:  UPDATE EMPLOYEES2 SET HML_SALARY_IND=NULL

   2:   

   3:  Error at line 1

   4:  ORA-54017: UPDATE operation disallowed on virtual columns

Virtual Columns can be indexed

   1:  CREATE INDEX HML_SAL_IDX ON EMPLOYEES2(HML_SALARY_IND)

Index created.

Tables can be partitioned based on virtual columns:

 

   1:  CREATE TABLE EMPLOYEES2_UR

   2:  (

   3:    EMPLOYEE_ID     NUMBER(6),

   4:    FIRST_NAME      VARCHAR2(20 BYTE),

   5:    LAST_NAME       VARCHAR2(25 BYTE) CONSTRAINT EMP_LAST_NAME_NN2 NOT NULL,

   6:    EMAIL           VARCHAR2(25 BYTE) CONSTRAINT EMP_EMAIL_NN2 NOT NULL,

   7:    PHONE_NUMBER    VARCHAR2(20 BYTE),

   8:    HIRE_DATE       DATE CONSTRAINT EMP_HIRE_DATE_NN2 NOT NULL,

   9:    JOB_ID          VARCHAR2(10 BYTE) CONSTRAINT EMP_JOB_NN2 NOT NULL,

  10:    SALARY          NUMBER(8,2),

  11:    COMMISSION_PCT  NUMBER(2,2),

  12:    MANAGER_ID      NUMBER(6),

  13:    DEPARTMENT_ID   NUMBER(4),

  14:    CEO_IND VARCHAR2(1) AS (CASE WHEN EMPLOYEE_ID=100 THEN 'Y' ELSE NULL END),

  15:    HML_SALARY_IND VARCHAR2(1) AS (CASE WHEN SALARY <=10000 THEN 'L' WHEN SALARY >10000 AND SALARY <=20000 THEN 'M' WHEN SALARY >20000 THEN 'H' ELSE NULL END)

  16:  )

  17:  PARTITION BY LIST(HML_SALARY_IND)

  18:  (

  19:     PARTITION PART_H VALUES('H'),

  20:     PARTITION PART_M VALUES('M'),

  21:     PARTITION PART_L VALUES('L') 

  22:  )