Home > oracle_11g_new_features > Virtual column in oracle 11g

Virtual column in oracle 11g

Virtual columns  allows users to create columns whose data is not supplied by the user, but it is derived by oracle server implicitly from other columns. Most importantly, their disk space consumption is NULL because their data is not stored in the table.

Key points about Virtual columns:

1)Data can not be inserted into virtual column

2) The virtual column and the columns to be used in the derivation of virtual column data must belong to the same table.

3)It can be used as normal columns without any restriction. It can be constrained, indexed, and can be used in DML or DDL statements. Note that it cannot be updated in UPDATE statement

4)We can partition the table based on virtual column

We can use below query to define virtual columns defined in the users schema.

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, HIDDEN_COLUMN
FROM USER_TAB_COLS
WHERE VIRTUAL_COLUMN = ‘YES’;

5)we can not create virtual columns on Temporary tables, object types, clusters, External tables and Index Organized Tables

I tried to create table using following statment and used sysdate in the virtual column expression and got the following error.

Syntax

COLUMN [DATA TYPE] [GENERATED ALWAYS] AS (EXPRESSION) [VIRTUAL]

create table virtual_column (MEMBER_ID number(10),
MEMBER_NAME varchar2(25),
BIRTH_DATE date,
AGE_IN_MONTHS number(5) AS(SYSDATE-BIRTH_DATE))

ORA-54002: only pure functions can be specified in a virtual column expression

create table virtual_column (MEMBER_ID number(10),
MEMBER_NAME varchar2(25),
MONTHLY_SALARY number(10,2),
ANNUAL_SALARY number(10,2) AS(12*MONTHLY_SALARY))

SQL> insert into virtual_column values(1,’POOJITHA’,5000,10000);
insert into ukatru.virtual_column values(1,’POOJITHA’,5000,10000)
*
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns

insert into virtual_column(MEMBER_ID,MEMBER_NAME,MONTHLY_SALARY) values(1,’POOJITHA’,5000);

SQL> select * from ukatru.virtual_column;

MEMBER_ID MEMBER_NAME               MONTHLY_SALARY ANNUAL_SALARY
———- ————————- ————– ————-
1 POOJITHA                            5000         60000

Create index on virtual columns:

CREATE INDEX IDX_ANUAL_SALARY ON virtual_column (ANNUAL_SALARY);

If you query user_indexes table the index  is created as function based index.

SELECT INDEX_NAME,  INDEX_TYPE, FUNCIDX_STATUS
FROM   USER_INDEXES
WHERE TABLE_NAME = ‘VIRTUAL_COLUMN’;

INDEX_NAME                     INDEX_TYPE                  FUNCIDX_
—————————— ————————— ——–
IDX_ANUAL_SALARY               FUNCTION-BASED NORMAL       ENABLED

we can use alter table command to add virtual column to the table.

alter table virtual_column
add QUARTERLY_SALARY AS (MONTHLY_SALARY*3)

Adding constraint on the virtual column.

ALTER TABLE virtual_column
ADD CONSTRAINT QUARTERLY_SALARY_CHECK CHECK(QUARTERLY_SALARY != 0);

SQL> insert into virtual_column(MEMBER_ID,MEMBER_NAME,MONTHLY_SALARY) values(2,’POOJITHA’,0);
insert into virtual_column(MEMBER_ID,MEMBER_NAME,MONTHLY_SALARY) values(2,’POOJITHA’,0)
*
ERROR at line 1:
ORA-02290: check constraint (QUARTERLY_SALARY_CHECK) violated

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: