DB: Virtual and Invisible Columns

Following post will help you to understand the usage of couple of new features introduced in Oracle database.

Virtual Column (Introduced in 11g R1):

Virtual columns are expressions that are based on one or more existing columns in the table. A virtual column is only stored as metadata. It does not consume physical space, but it can be indexed. The virtual column also contains optimizer statistics and histograms.

When queried, virtual columns appear to be normal table columns, but their values are derived rather than being stored on disc. The syntax for defining a virtual column is listed below.

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

If the datatype is omitted, it is determined based on the result of the expression. The GENERATED ALWAYS and VIRTUAL keywords are provided for clarity only.

Benefits:

• Automatic re-computation of derived columns for ad-hoc query tools
• Reduction in redundant disk space for columns that must be derived from other columns
• Using virtual columns in tables also eliminates the need to use views to display derived column values.
• Using a virtual column also simplifies the use of derived columns. Transparently derived values do not require the application to calculate and insert an additional value. This also prevents the need to use a trigger on the table to provide an alternate implementation of this functionality.

Limitations:

• Virtual columns may not reference other virtual column values:
• Virtual columns only work within the specified table, and you cannot reference columns within other tables.
• Virtual columns can be referenced in the WHERE clause of updates and deletes, but they cannot be manipulated by DML.

Ex:
CREATE TABLE EMP (
  id            NUMBER,
  name      VARCHAR2(100),
  salary      NUMBER(9,2),
  commission  NUMBER GENERATED ALWAYS AS (ROUND(salary/100,2)) VIRTUAL,
  CONSTRAINT emp_pk PRIMARY KEY (id)
);

INSERT INTO EMP (id, name, salary)  VALUES (1, 'LKAKARLA', 100);

SELECT * FROM EMP;

Invisible Column (Introduced in 12c R1):

This new feature in 12c allows to make invisible columns for generic queries, operations. When a column is defined as invisible, the column won’t appear in generic queries, unless the column is explicitly referred to in the SQL statement or condition, or DESCRIBED in the table definition. You must explicitly refer to the invisible column name with the INSERT statement to insert the database into invisible columns.

Syntax: 
Invisible Column: column_name [datatype] [INVISIBLE]
Invisable Virtual Column: column_name [datatype] [INVISIBLE] [GENERATED ALWAYS] AS (expression) [VIRTUAL]

Benefits:

• Hide important data - columns from being displayed in the generic queries
• Invisible columns can be useful during application migrations. Making new columns invisible means they won't be seen by any existing applications, but can still be referenced by any new applications, making the online migration of applications much simpler.
• Virtual columns can be made invisible.
• Ability to logically reorder the columns in a table (refer link)

Limitations:

• Temporary tables, external tables and cluster tables won’t support invisible columns.
• Following operations don’t see invisible columns
• SELECT * FROM statements in SQL
• The DESCRIBE statement when used in either SQL*PLUS or via Oracle Call Interface (OCI) (SET COLINVISIBLE ON in SQL*PLUS to see invisible columns)
• %ROWTYPE attribute declarations in PL/SQL

Ex:
CREATE TABLE EMP (
  id            NUMBER,
  name      VARCHAR2(100),
  salary      NUMBER(9,2),
  dob         DATE INVISIBLE,
  commission  NUMBER GENERATED ALWAYS AS (ROUND(salary/100,2)) VIRTUAL,
  bonus       NUMBER INVISIBLE GENERATED ALWAYS AS (ROUND(salary/200,2)) VIRTUAL,
  CONSTRAINT emp_pk PRIMARY KEY (id)
);

INSERT INTO EMP (id, name, salary, dob)  VALUES (1, 'LKAKARLA', 100, SYSDATE);

DESC EMP;

SET COLINVISIBLE ON;

SELECT * FROM EMP;

SELECT ID, NAME, SALARY, DOB, COMMISSION, BONUS FROM EMP ;