Home » SQL & PL/SQL » SQL & PL/SQL » virtual columns (Oracle 11g Rel1 , RHEL AS4)
virtual columns [message #400039] Sat, 25 April 2009 06:19 Go to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
Hi All,

I've been reading the 11g documentation for a while and read about Virtual columns and Invisible indexes in 11g . They seem to be a good feature to go on with .

But I'm not clear like what are the advantages associated with them . Will they occupy space at the disk level when rows are inserted in the table.

Kindly help me out .

Regards,
Raj
Re: virtual columns [message #400041 is a reply to message #400039] Sat, 25 April 2009 06:49 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Virtual columns take no space. Used for derived expressions.
Invisible index could be handy to test the presence or absence of an index ( or part of performance tuning exercise )
Re: virtual columns [message #400043 is a reply to message #400041] Sat, 25 April 2009 07:06 Go to previous messageGo to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
Thanks Mahesh. So you mean to say that in earlier cases , we used triggers to derive the output but from 11g we have this via virtual columns.

But virtual indexes will occupy space and there are helpful to test the efficiency of the index .

And can we create indexes , views on this virtual columns.

please correct me if i'm on the same page as you are.

[Updated on: Sat, 25 April 2009 07:51]

Report message to a moderator

Re: virtual columns [message #400044 is a reply to message #400043] Sat, 25 April 2009 07:53 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> So you mean to say that in earlier cases , we used triggers to derive the output
I never said that and have no idea what output you are talking about.
>>But virtual indexes will occupy space and there are helpful to test the efficiency of the index .
Virtual Index used to mean something else (it is been around for a while). We are talking about "invisible index".
There seems to be a little overlap here. I will have to look into this much further.
SQL> create index empnoindx on emp(empno) invisible;

Index created.

-- virtual index.
SQL> create index enameindx on emp(ename) nosegment;

Index created.

SQL> select table_name,index_name,index_type,visibility from user_indexes where table_name='EMP';

TABLE_NAME                     INDEX_NAME                     INDEX_TYPE                  VISIBILIT
------------------------------ ------------------------------ --------------------------- ---------
EMP                            EMPNOINDX                      NORMAL                      INVISIBLE



Virtual Index takes no space. It is a "Fake Index" . Could be used to test the existence of an index (in explain plan etc).
Invisible index is just an alternative for dropping indexes or making it unusable (invisible index will maintain DML and
unusable index will not).


Re: virtual columns [message #400045 is a reply to message #400044] Sat, 25 April 2009 07:58 Go to previous messageGo to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
Mahesh , thanks for that on invisible indexes. And similarly can you help me on virtual columns by giving a small example why exactly we need virtual columns.

we can dervive the run time values using triggers in 10g . Please let me know the advantage of this in 11g.
Re: virtual columns [message #400046 is a reply to message #400045] Sat, 25 April 2009 08:05 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
http://wiki.oracle.com/page/Virtual+Columns?t=anon

first hit from google
Re: virtual columns [message #400048 is a reply to message #400046] Sat, 25 April 2009 08:48 Go to previous message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
Thanks a ton Mahesh for those valueable input on Virtual columns and invisible indexes.

Previous Topic: Pls help me to write an interesting SQL query
Next Topic: null values inserted through 'INSERT ALL' option
Goto Forum:
  


Current Time: Sat Dec 10 04:53:31 CST 2016

Total time taken to generate the page: 0.13697 seconds