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


13 comments:

  1. can this virtual column be indexed?

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

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

    TABLE EMP
    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;

    ReplyDelete
    Replies
    1. Can we use 2 indexes in single query ?

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

      Delete
  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

    ReplyDelete
  5. Hi Sir,

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

    ReplyDelete