Showing posts with label Oracle Virtual Columns. Show all posts
Showing posts with label Oracle Virtual Columns. Show all posts

Saturday, November 19, 2011

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'



image



   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:  )

image


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 )



image
   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.


image


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:  )



image

Udemy