OCP Blog
Super DBA
virtual column in oracle 11g database
by Vazha Mantua Monday, December 26, 2011 9:04 PM

Good Day All,

In this article we show you example of virtual indexes in oracle 11g database. When queried table virtual columns appear to be normal table columns, but their values are not stored in disk, They are generated at runtime. but of course, any indexes that create on virtual columns will require storage space.

Let see example

create table vm_table_with_vc
(id number,
person_name varchar2(30),
age number,
age_virtual NUMBER GENERATED ALWAYS AS (age*2) virtual
);

 

As you see column age_virtual is virtual column, which multiply value of “age” column in 2 times.

Now we can insert into table and see results:

insert into vm_table_with_vc(id,person_name,age) values (1,'Lionel Messi',24);
commit;

select * from vm_table_with_vc and result is:

ID PERSON_NAME AGE AGE_VIRTUAL
1 Lionel Messi 24 48

As you see in column “age_virtual” is automatically shown value=24*2=48

We can simple create index on virtual columns and this index will be like as function-based indexes.

create index vm_table_with_vc_I1 on vm_table_with_vc(age_virtual);

If we select dba_segments view ,we will check that index is stored in database.

 

Tags: , , ,

Filter by APML

Calendar

<<  November 2017  >>
MoTuWeThFrSaSu
303112345
6789101112
13141516171819
20212223242526
27282930123
45678910

View posts in large calendar

TextBox