Oracle 11g has introduced a new feature that allows us to create a "virtual column", an empty column that contains a function upon other table columns (the function itself is stored in the data dictionary). Oracle 11g enables us to store expressions directly in the base tables themselves as virtual columns.Virtual columns are more flexible than any of their prior alternatives.
The syntax for defining a virtual column is listed below.
column_name [datatype] [GENERATED ALWAYS] AS (expression) [VIRTUAL]
Creating a virtual column : Here we will create a table witha single column as follows .
SQL> create table vir_tab
( id number ,
sal number ,
bonus number generated always as (id + sal) virtual
);
Table created.
We can see that the virtual column is generated from a simple expression involving the other columns in our table. Note that the VIRTUAL keyword is optional and is included for what Oracle calls "syntactic clarity"
Virtual column values are not stored on disk. They are generated at runtime using their associated expression (in our example, N1 + N2). This has some implications for the way we insert data into tables with virtual columns, as we can see below.
SQL> insert into vir_tab values (100,11000,11100);
INSERT INTO t VALUES (10, 20, 30)
*
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns
Hence ,we cannot explicitily add data to virtual columns, so we will attempt an insert into the physical columns only as follows
SQL> insert into vir_tab values(100,11000);
INSERT INTO t VALUES (10, 20)
*
ERROR at line 1:
ORA-00947: not enough values
Here we see that we cannot insert columns,they are still considered part of the table's column list.This means that we must explicitily reference the physical columns in our insert statements, as follows .
SQL> insert into vir_tab (id,sal) values (100,11000);
1 row created.
Now we have successfully inserted the data and can query the table.
SQL> select * from vir_tab;
ID SAL BONUS
---- ---------- ----------
100 11000 11100
Indexes and Constraints on virtual columns :
SQL> create index sal_idx on vir_tab(sal);
For Constraint :
SQL> alter table vir_tab add constraint vir_id_pk primary key(id);
Benefits of Virtual Columns :
- 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 (e.g. a MONTH column that is derived from another DATE column).
- Easier for interval partitioning
There are few restrictions on the virtual columns.
1. We cannot write/insert the data into virtual columns.
2. There is no support for index_organized, external, object, cluster, temporary tables.
3. There is no support for Oracle-supplied datatypes, user-defined datatypes, LOBs, or LONG RAWs.
4. We can partition the table based on virtual column .We can use below query to define virtual columns defined in the users schema.
SQL> SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, HIDDEN_COLUMN
FROM USER_TAB_COLS WHERE VIRTUAL_COLUMN = ‘YES’;
5. we can not create virtual columns on Temporary tables, object types, clusters, External tables and Index Organized Tables
Enjoy :-)
