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
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
can this virtual column be indexed?
ReplyDeleteyes :)
ReplyDeleteBut does it have any affect on table size???
ReplyDeleteno, its values are derived rather than being stored on disc
DeleteK..Thanks...
DeleteQuestion: Can we use 2 indexes in single select statement ?
ReplyDeleteTABLE 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;
INDX1 should be used.
DeleteCan we use 2 indexes in single query ?
DeleteIf all the info required in sql can be retrieved from two or more indexes, oracle can use multiple indexes (index-merge)
DeleteHi ,
ReplyDeleteCan 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
Hi Sir,
ReplyDeletePlease tell me that which cases need to require virtual column...........
When your 1 column is dependent on calculation on another column(s)
DeleteThanks so much..
Delete