Oracle 11g: Virtual Column

Oracle 11g has introduced a new feature that allows you to create a "virtual column",
an empty column that contains a function upon other table columns
(the function itself is stored in the data dictionary).

SQL> create table mytable (
  2   fname varchar2(25),
  3   lname varchar2(25)
  4  );

Table created
SQL> insert into mytable values('Nimish','Garg');

1 row inserted
SQL> alter table mytable add (fullname varchar2(60) generated always as (fname||' '||lname) virtual);

Table altered
SQL> select * from mytable ;

FNAME                      LNAME                      FULLNAME
------------------------- ------------------------- -------------------------
Nimish                         Garg                         Nimish Garg

Here is another example of this
create table employee (
 empid number,
 ename varchar2(10),
 dname varchar2(10),
 salary number(9,2),
 comm number generated always as (round(salary*10/100),2) virtual

Related Posts:
- Oracle - DDL, DML and DCL commands
- ORA-01439: column to be modified must be empty to change datatype
- ORA-02438: Column check constraint cannot reference other columns
- Oracle: Delete duplicate rows from table


  1. can this virtual column be indexed?

  2. But does it have any affect on table size???

    1. no, its values are derived rather than being stored on disc

  3. Question: Can we use 2 indexes in single select statement ?

    emp_id emp_name Gender salary
    1 ABC M 2000
    2 DBC F 2050
    3 ABC M 2300
    4 SSD F 2500
    5 SSS F 3000

    IDX1 : emp_id
    IDX2 : emp_id,emp_name
    IDX3 : emp_name,Gender
    IND3 : emp_id,name,salary

    which INX will be used in below statement ?
    Select * from emp where emp_id=1 and gender=M;

    1. Can we use 2 indexes in single query ?

    2. If all the info required in sql can be retrieved from two or more indexes, oracle can use multiple indexes (index-merge)

  4. Hi ,

    Can please guide me how and form where to start learning performance tuning ?

    I am good in other parts of DB (RAC ADMIN/install/patch/upgrade,exp/imp,RMAN etc)

    I am oracle DBA with 5.8 year of exp and want to learn PT

  5. Hi Sir,

    Please tell me that which cases need to require virtual column...........

    1. When your 1 column is dependent on calculation on another column(s)