Fun with Oracle 11g virtual columns - revisited

I've been having some fun with Oracle 11g's virtual columns feature. For those who are uninitiated, there's already a qwzillion blog posts about this feature, including this good one from Tim Hall among others.

Following are a couple points I've noted about virtual columns that haven't been caught in the Oracle documentation nor any of the regular blogs I read, which may be of interest to others. Take the following example:

SQL> CREATE TABLE virtual_column_test (
  2    columna NUMBER
  3   ,columnb AS (columna + 1)
  4   ,columnc AS (columna + 2));

Table created.

1) You can use the DEFAULT keyword in an INSERT clause to avoid having to populate the virtual columns:

SQL> INSERT INTO virtual_column_test
  2  VALUES (1, DEFAULT, DEFAULT);

1 row created.

2) You are allowed to create constraints on the virtual column such as:

SQL> ALTER TABLE virtual_column_test
  2    ADD CONSTRAINT vct_chk
  3    CHECK (columnc > 3);

Table altered.

SQL> INSERT INTO virtual_column_test
  2  VALUES (1, DEFAULT, DEFAULT);
INSERT INTO virtual_column_test VALUES (1, DEFAULT, DEFAULT)
*
ERROR at line 1:
ORA-02290: check constraint (SAGE.VCT_CHK) violated

SQL> INSERT INTO virtual_column_test
  2  VALUES (2, DEFAULT, DEFAULT);

1 row created.

3) And yes, bizarrely you can create a foreign key constraint:

SQL> CREATE TABLE primary_table (
  2    id NUMBER PRIMARY KEY);

Table created.

SQL> CREATE TABLE child_table (
  2    id NUMBER PRIMARY KEY
  3   ,primary_table_id AS (id + 1)
  4      CONSTRAINT child_table_fk
  5      REFERENCES primary_table (id));

Table created.

SQL> INSERT INTO primary_table VALUES (1);

1 row created.

SQL> INSERT INTO child_table
  2  VALUES (1, DEFAULT);
INSERT INTO child_table
*
ERROR at line 1:
ORA-02291: integrity constraint (SAGE.CHILD_TABLE_FK) violated - parent key not found

SQL> INSERT INTO primary_table VALUES (2);

1 row created.

SQL> INSERT INTO child_table
  2  VALUES (1, DEFAULT);

1 row created.

In addition the Oracle errors code highlight some further limitations.

4) You may not drop a column if it is referenced in a virtual column:

SQL> ALTER TABLE virtual_column_test DROP COLUMN columna;
ALTER TABLE virtual_column_test DROP COLUMN columna
*
ERROR at line 1:
ORA-54031: column to be dropped is used in a virtual column expression

5) You may not rename a column that is used by a virtual column:

SQL> ALTER TABLE virtual_column_test
  2  RENAME COLUMN columna TO columnd;
ALTER TABLE virtual_column_test RENAME COLUMN columna TO columnd
*
ERROR at line 1:
ORA-54032: column to be renamed is used in a virtual column expression

6) There are also restrictions around changing the datatype of a column a virtual column is based on, though this is similar to the restriction on altering a normal table's column's datatype when the table already contains data.

All in all virtual columns look like a useful little addition to the database that we may all start making use of. It would be interesting to read some stats on the performance hit, particular when it applies, as well as how the database optimises if a virtual column is present.