ORACLE-BASE - Virtual Columns in Oracle Database 11g
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
0 comments:
Post a Comment