Virtual
Column is one of
the new features introduced in Oracle 11g. Virtual column values are not
stored on disk. They are generated at runtime using their associated expression
The syntax
for defining a virtual column is:
Column_Name
[Datatype] [GENERATED ALWAYS] AS [Expression] [VIRTUAL]
Here, Data
type: Column’s Data Type and Expression: SQL Expression
Though
Virtual Columns appear to be like normal table columns they are defined by an
expression and they derive their value from the result of the expression. The
values of these virtual columns are not stored in database; instead they are
calculated at run time dynamically.
How to
create a virtual column in a table
Example
1:
Create Table
Product
(
Product_Id
Number,
Product_Name
Varchar2 (100),
Value Number,
Increment Number,
New_Value Number Generated Always As (Value + Increment) Virtual
);
Example
2:
Create Table
Product_Sales
(Sls_Key Number,
Product_Id Number,
Sls_Amt Number,
Sls_Loc_Id Number,
Sales_Percent
Generated Always As
(
Case
When Sls_Loc_Id = 1 Then Sls_Amt
When Sls_Loc_Id =2 Then Sls_Amt * .01
End
) Virtual
);
Adding the
virtual column using the ALTER command:
Alter Table
Product Add (New_Value Generated Always As (Value + Increment));
You can also
create indexes on the virtual columns:
Create
Index Index_New_Value On Product (New_Value);
Some
important Points on Virtual Columns:
- All columns in the virtual
column, should belong to the same table on which we are creating the
virtual column
- We cannot do any update or
DML's on Virtual column.
- Virtual column expression
can’t reference any other virtual column.
- Indexes and constraints can
be created on virtual columns; also they can be used as partition key
- Virtual columns reduce the
use of views and derived columns.
virtual columns and
storage
To correctly demonstrate that a virtual column uses no
storage would require techniques such as block dumping, which is beyond the
scope of this article. We can, however, show that adding more virtual columns
to an existing table doesn't require any additional space. In the following
example, we will create two tables of 10,000 records each and compare their
relative sizes (one table will have several virtual columns). We will begin by
creating the tables as follows.
CREATE TABLE
NORML_COL_TBL
( c1 VARCHAR2(4000),
c2 VARCHAR2(4000),
c3 VARCHAR2(4000)
);
CREATE TABLE
succeeded.
CREATE TABLE
VITTUAL_COL_TBL
( c1 VARCHAR2(4000),
c2 VARCHAR2(4000),
c3 VARCHAR2(4000),
c4 VARCHAR2(4000) GENERATED ALWAYS AS
(UPPER(c1)) VIRTUAL,
c5 VARCHAR2(4000) GENERATED ALWAYS AS
(UPPER(c2)) VIRTUAL,
c6 VARCHAR2(4000) GENERATED ALWAYS AS
(UPPER(c3)) VIRTUAL
);
CREATE TABLE succeeded.
We will now load each table with 10,000 identical rows.
INSERT INTO NORML_COL_TBL
SELECT RPAD('x',4000),
RPAD('x',4000),
RPAD('x',4000)
FROM dual
CONNECT BY ROWNUM <= 10000;
INSERT INTO VITTUAL_COL_TBL(c1,c2,c3)
SELECT RPAD('x',4000),
RPAD('x',4000),
RPAD('x',4000)
FROM dual
CONNECT BY ROWNUM <= 10000;
Finally, we can compare the sizes of the two tables, as
follows.
SELECT segment_name,
bytes,
blocks,
extents
FROM user_segments
WHERE segment_name
LIKE '%_COL_TBL%';
SEGMENT_NAME
BYTES BLOCKS EXTENTS
---------------------------------------------------------------------------------
---------------------- ---------------------- ----------------------
VITTUAL_COL_TBL
251658240 30720 10
NORML_COL_TBL
251658240 30720 10
As expected, the tables' physical storage requirements
(excepting the dictionary metadata) are identical.