SQL Virtual Column

Virtual Column

Virtual Column:- Virtual column is a column in a table that never store the data. Also we can never insert values on Virtual column. When we select virtual column in a query then system calculate the value of virtual column at runtime.

Table with Virtual column :-

Example:-(Method I)

CREATE TABLE product (
   id number(5),
   name varchar2(50),
   price number(5,2),
   vat number(5,2),
   price_including_vat as (price + price * vat)
   ,CONSTRAINT product_pk1 PRIMARY KEY (id)
);

(Method II):-

Create table Product_details
(
     Product_Id Number
    ,Product_Name Varchar2(50)
    ,Value Number
    ,Increment1 Number
    ,New_Value Number Generated always as (Value + Increment1) Virtual
);

 Create table product_sales
    (
       sales_id Number,
       Product_id Number,
       sales_amt Number,
       sales_loc_id Number,
       sales_percent
       generated always as
       (
         case When sales_loc_id = 1 Then sales_amt
              When sales_loc_id =2  Then sales_amt * .01
         End
       ) virtual
   );


Create table inventory(
inventory_id number
,inventory_count number
,inventory_status generated always as (
case when inventory_count <= 100 then 'GETTING LOW'
     when inventory_count > 100 then 'OKAY'
end ) virtual);

Inserting data into virtual column table:- When we try to insert data without mention column name, it will return below error


That's why we need to mention column name when issuing insert statement in a table with virtual column

insert into product(id,name,price,vat) values (1, 'book', 20, 0.07);

Select data from Virtual column Table :-
select * from product;

Virtual Column information in Data Dictionary:-You can get the information about virtual column and default value of column by below query.

SELECT column_name , data_type, data_default,virtual_column FROM USER_TAB_COLS
WHERE  table_name = 'PRODUCT';



Virtual column for Function based Index:-When you create a function based index,system automatically create a virtual column.

Example:-
create table student(name varchar2(50), regno number);



select column_name, column_id, internal_column_id, hidden_column, virtual_column
from user_tab_cols where table_name = 'STUDENT';


Currently there is no virtual column in the table , let see what happen when we create function based index in the table

create index idx_name on student(upper(name));



Now need to check again information

select column_name, column_id, internal_column_id, hidden_column, virtual_column
from user_tab_cols where table_name = 'STUDENT';



System automatically created a virtual column when we create a function based index on table.

Adding Virtual Column by Alter Command:-

Alter table product Add (new_value generated always as (Value + Increment1));

Note:- We can not perform any DML Operation on virtual column as this column is a drived column and not stored in actual table

Index and constraint can be created on virtual column and virtual column can be used as a parttion key for partition Table