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
    Replies
    1. no, its values are derived rather than being stored on disc

      Delete
  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
    Replies
    1. When your 1 column is dependent on calculation on another column(s)

      Delete