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: , , ,

Comments (4) -

1/7/2012 4:57:40 PM #

Villas for Rent in Italy

Nice Intro!

Villas for Rent in Italy United States

4/26/2012 2:10:32 PM #

Zovi Coupons

I like the blog theme you are using, Which theme is it?

Zovi Coupons United States

6/3/2012 2:30:32 AM #

Directory Submission Service

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.

Directory Submission Service United Kingdom

7/18/2012 6:48:05 PM #

Small Cap Stocks

The script under makes and colonizes an employee table with two levels of expenses. It includes two implicit columns to present the commission-based salary. The first uses the most condensed syntax while the second uses the most long-winded form.

Small Cap Stocks United States

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

Filter by APML

Calendar

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

View posts in large calendar

TextBox