Virtual Columns in tables 11G

This article presents new feature of 11g Virtual column.

Virtual column – a column that is not stored on disk. Value of the column is calculated by expression or function.

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

GENERATED ALWAYS and VIRTUAL keywords are optional

Example:

--create deterministic function used by virtual column
CREATE OR REPLACE FUNCTION add_fnc(p_id NUMBER)
RETURN NUMBER
deterministic
IS
BEGIN
  RETURN p_id + 3;
END;
/
 
CREATE TABLE test_tbl
(
  static_col   NUMBER,
  --virtual column using simple syntax expression
  vir_col1 NUMBER AS (static_col + 1),
  --virtual column using long syntax expression
  vir_col2 NUMBER generated always AS (static_col+2)        virtual, 
  --virtual column long syntax with function
  vir_col3 NUMBER generated always AS (add_fnc(static_col)) virtual
)
--partition by virtual columns
PARTITION BY RANGE(vir_col2)
subpartition BY hash(vir_col1) subpartitions 4
(PARTITION p1 VALUES less than(maxvalue));
 
--add primary key using virtual column
ALTER TABLE test_tbl add constraint test_tbl primary key(vir_col1);

Possible:

  • create index on virtual columns
  • collect statistic on virtual columns
  • partition table using virtual columns
  • create PK and FK constraints on virtual columns
  • save space 🙂

Impossible – quite many for 11.2.0.3:

  • can’t be created for index organized tables
  • DML is not allowed on virtual column – seems to be obvious
  • “expression” can’t refer to other virtual column
  • type can’t be user-defined type, LOB, LONG RAW
  • can’t partition by virtual column which is using function
  • about others you can read in Oracle help

Have a fun 🙂

Tomasz

 

One thought on “Virtual Columns in tables 11G

Leave a Reply

Your email address will not be published. Required fields are marked *