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