Virtual Columns

Virtual Columns are similar to normal table’s columns but with the following differences:

They are defined by an expression. The result of evaluation of this expression becomes the value of the column.

The values of the virtual column are not stored in the database. Rather, it’s computed at run-time when you query the data.

You can’t update (in SET clause of update statement) the values of virtual column. These are read only values, that are computed dynamically and any attempt to modify them will result into oracle error.

Assume a database contains a table called SALES. The table has the following structure:

SALES_ID NUMBER

CUST_ID NUMBER

SALES_AMT NUMBER

Some users want to add a column called SALE_CATEGORY, which identifies the type of the sale: LOW, MEDIUM, HIGH and ULTRA, depending on the amount of sale and the customer in question.

This column will help them identify the records for appropriate action and routing to the concerned employee for handling. Here is the logic for values in the column:

 

 

Although this column is a crucial business requirement, the development team does not want to change the code to create the necessary logic.

Of course, you could add a new column in the table called sale_category, and write a trigger to populate the column using the logic shown above—a fairly trivial exercise. But performance issues would arise due to context switching from and into the trigger code.

In Oracle Database 11g, you do not need to write a single line of code in any trigger. All you have to do instead is add a virtual column.

Virtual columns offer

The flexibility to add columns that convey business sense without adding any complexity or performance impact.

create table sales

(

sales_id number,
cust_id number,
sales_amt number
)
/

insert into sales values(101,1,12000);

insert into sales values(102,3,120000);
insert into sales values(103,1,150000);

commit;

select * from sales;

 

Now let us add a virtual column SALE_CATEGORY for above SALES table (you can create a new table with SALE_CATEGORY virtual column)

alter table sales
add sale_category varchar2(6)
generated always as
(
case
when sales_amt <= 10000 then 'LOW'
when sales_amt > 10000 and sales_amt <= 100000 then 'MEDIUM'
when sales_amt > 100000 and sales_amt <= 1000000 then 'HIGH'
else 'ULTRA'
end
) virtual;

The column is specified as "generated always as", meaning the column values are generated at runtime, not stored as part of the table. That clause is followed by how the value is calculated in the elaborate CASE statement. Finally, "virtual" is specified to reinforce the fact that this is a virtual column.

Now you can observe the output

select * from sales;

 

The virtual column values are all populated as usual. Even though this column is not stored, you can refer to it as any other column in the table. You can even create indexes on it.

But you cannot insert a value directly into this column. When you update SALES_AMT column, changes will be reflected in virtual column Let us analyze execution plan

set autotrace traceonly explain

select * from sales where sale_category='HIGH';

 

 

Now I would create index and analyze the output
create index in_sales_cat on sales (sale_category);

exec dbms_stats.gather_table_stats(ownname=>USER,-tabname=>'SALES', cascade=>true);

select * from sales where sale_category='HIGH';

 

To view INDEX details

set autotrace off

select index_type

from user_indexes

where index_name = 'IN_SALES_CAT';

FUNCTION-BASED NORMAL -- it is function based indexes

set long 5000

select column_expression

from user_ind_expressions

where index_name = 'IN_SALES_CAT';

COLUMN_EXPRESSION

--------------------------------------------------------------------------------
CASE WHEN "SALES_AMT"<=10000 THEN 'LOW' WHEN ("SALES_AMT">10000
AND "SALES_AMT"

<=100000) THEN 'MEDIUM' WHEN ("SALES_AMT">100000 AND

"SALES_AMT"<=1000000) THEN
'HIGH' ELSE 'ULTRA' END

To view segment details

COLUMN SEGMENT_NAME FORMAT A15

SELECT SEGMENT_NAME,SEGMENT_TYPE

FROM DBA_SEGMENTS
WHERE SEGMENT_NAME='IN_SALES_CAT';

IN_SALES_CAT INDEX

We can also make use of deterministic function for virtual columns

{The deterministic functions indicate that the function will always return the same output return value for any given input arguments. Source: http://myracle.wordpress.com/2007/08/24/deterministic-functions/}

ALTER TABLE SALES

DROP COLUMN SALE_CATEGORY;

create or replace function sale_category_fun(

p_sales_amt IN sales.sales_amt%type)
RETURN varchar2 DETERMINISTIC --it must be deterministic
IS
v_category varchar2(10);
BEGIN
case
when p_sales_amt <= 10000 then
v_category := 'LOW';
when p_sales_amt > 10000 and p_sales_amt <= 100000 then
v_category :='MEDIUM';
when p_sales_amt > 100000 and p_sales_amt <= 1000000 then
v_category :='HIGH';
else v_category :='ULTRA';
end case;
RETURN v_category;
END;
/
 
ALTER TABLE SALES
ADD SALE_CATEGORY AS (sale_category_fun(sales_amt)) virtual;
SET AUTOTRACE TRACEONLY EXPLAIN
SELECT * FROM SALES WHERE SALE_CATEGORY='HIGH';

 

CREATE INDEX sales_category_idx ON SALES(sale_category);

SELECT * FROM SALES WHERE SALE_CATEGORY='HIGH';

 

set autotrace off

Data Dictionary information

COLUMN COLUMN_NAME FORMAT A15

COLUMN DATA_LENGTH FORMAT 99999

COLUMN DATA_DEFAULT FORMAT A50

COLUMN DATA_TYPE FORMAT A15

SELECT column_name, data_type, data_length, data_default, virtual_column

FROM user_tab_cols
WHERE table_name = 'SALES';

DROP FUNCTION sale_category_fun;

Table will be inaccessible since function is dropped:

SELECT * FROM SALES;

SELECT SALES_ID,CUST_ID,SALES_AMT ROM SALES;

You can also add a virtual column as shown below alter table emp add (ann_salary as (sal*12+nvl(comm,0)));

source: my Oracle 11g notes.