askMLabs

Virtual Column

Let us say we have the following table

Item_id  number
item_name varchar2(50)
item_cost number
Item_desc varchar2(100)

SQL> desc test
Name                                                  Null?    Type
----------------------------------------------------- -------- ------------------------------------
ITEM_ID                                                        NUMBER
ITEM_NAME                                                      VARCHAR2(25)
ITEM_COST                                                      NUMBER

We want to add a column called GRADE to the table which identifies the grade of item based on the cost ( G1,G2,G3 etc ). This will help in identifyng the quality of the item based on the grade.

The logic to implement is :

ITEM_COST                                    --   GRADE
<=10000                                           --    G1
>10000 AND <100000                     --    G2
>100000 AND <1000000                 --    G3
Else                                                   --    G4

The oracle internally should decide the grade based on the cost of the item while inserting the row into the table. The only option we have till now is to write a trigger which will fire while inserting the row to the table and that trigger code will decide the grade with the above logic and inserts the record to the table. This approach is very tedious and performance issues would arise due to context switching from and into the trigger code.

Oracle 11g gives the concept of virtual column. Virtual columns offer the flexibility to add columns that convey business sense without adding any complexity or performance impact. In fact the value of a virtual column in a row is derived by evaluating an expression. The expression can include columns from the same table, constants, SQL functions, or even user-defined PL/SQL functions. You can create index and do partition on the virtual columns.

===========================================================


DEMO         DEMO            DEMO           DEMO           DEMO            DEMO             DEMO
===========================================================

SQL> create table test (
2  item_id number,
3  item_name varchar2(25),
4  item_cost number);

Table created.

SQL> desc test
Name                                                  Null?    Type
----------------------------------------------------- -------- ------------------------------------
ITEM_ID                                                        NUMBER
ITEM_NAME                                                      VARCHAR2(25)
ITEM_COST                                                      NUMBER

SQL> insert into test (item_id, item_name, item_cost) values (301, 'HARDDISK', 3000);

1 row created.

SQL> insert into test (item_id, item_name, item_cost) values (302, 'LAPTOP', 60000);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test;

ITEM_ID ITEM_NAME                  ITEM_COST
---------- ------------------------- ----------
301 HARDDISK                        3000
302 LAPTOP                         60000

SQL> alter table test add grade varchar2(6)
2  generated always as
3  (
4  case
5       when item_cost <= 10000 then 'G1'
6       when item_cost > 10000 and item_cost <= 100000 then 'G2'
7       when item_cost > 100000 and item_cost <= 1000000 then 'G3'
8       else 'G4'
9     end
10  ) virtual ;

Table altered.

---  Adding virtual column to table
SQL> desc test
Name                                                  Null?    Type
----------------------------------------------------- -------- ------------------------------------
ITEM_ID                                                        NUMBER
ITEM_NAME                                                      VARCHAR2(25)
ITEM_COST                                                      NUMBER
GRADE                                                          VARCHAR2(6)

SQL> select * from test;

ITEM_ID ITEM_NAME                  ITEM_COST GR
---------- ------------------------- ---------- --
301 HARDDISK                        3000 G1
302 LAPTOP                         60000 G2
---  Data is already populated to the virtual column.
SQL> insert into test (item_id, item_name, item_cost) values (302, 'LAPTOP', 120000);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test;

ITEM_ID ITEM_NAME                  ITEM_COST GR
---------- ------------------------- ---------- --
301 HARDDISK                        3000 G1
302 LAPTOP                         60000 G2
302 LAPTOP                        120000 G3

SQL> col data_default format a50
SQL> set linesize 100
SQL> select column_name, data_default from   user_tab_columns where  table_name = 'TEST';

COLUMN_NAME               DATA_DEFAULT
------------------------- --------------------------------------------------
ITEM_ID
ITEM_NAME
ITEM_COST
GRADE                     CASE  WHEN "ITEM_COST"<=10000 THEN 'G1' WHEN ("ITEM_COST">10000 AND "ITEM_COST"<

SQL>

SQL> create index ind_grade on test (grade);

Index created.

SQL> select index_type from user_indexes where index_name = 'IND_GRADE';

INDEX_TYPE
---------------------------
FUNCTION-BASED NORMAL
--- The index created is function based index
SQL> select column_expression from user_ind_expressions where index_name = 'IND_GRADE';

COLUMN_EXPRESSION
--------------------------------------------------------------------------------
CASE  WHEN "ITEM_COST"<=10000 THEN 'G1' WHEN ("ITEM_COST">10000 AND "ITEM_COST"<

SQL>  insert into test (item_id, item_name, item_cost , grade) values (302, 'LAPTOP', 120000,'G3');
insert into test (item_id, item_name, item_cost , grade) values (302, 'LAPTOP', 120000,'G3')
*
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns

--We cant insert data to the virtual column.There is no storage for virtual column.

Limitations of Virtual Columns
==========================
1) Virtual columns are not supported for index-organized, external, object, cluster, or temporary tables.
2) It can only refer to columns defined in the same table.
3) You cant perform a delete or insert operation on a virtual column.
4) You cant update a virtual column by using it in the SET clause of an update command.

Ex :
SQL>  insert into test (item_id, item_name, item_cost , grade) values (302, 'LAPTOP', 120000,'G3');
insert into test (item_id, item_name, item_cost , grade) values (302, 'LAPTOP', 120000,'G3')
*
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns