Re: when to create a virutal ilndex

In the context of "Tales of the Oak Table", the virtual indexes were, indeed, access paths into the x$ fixed tables. They can be seen in the virtual table v$indexed_fixed_columns, which has the structure:

Name                    Null?    Type
----------------------- -------- ------------
TABLE_NAME                       VARCHAR2(30)
INDEX_NUMBER                     NUMBER
COLUMN_NAME                      VARCHAR2(30)
COLUMN_POSITION                  NUMBER

These exist and are predefined.

The Virtual Index Wizard probably relates to the "nosegment" feature, whereby you can do the following:

    create index t1_noseg on t1(small_vc) nosegment;     alter session set "_use_nosegment_indexes"=true;

>From this point on, your session can use EXPLAIN PLAN
to see if the optimizer would use this index if it existed, but you don't have to build it, and no-one else can see it. On the plus side, it gives you some idea about whether a new index is worth building; on the minus - the statistics are inferred from the column statistics, so there is no guarantee that the index would be used if you really created it.

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html Public Appearances - schedule updated 29th Nov 2005

• Original Message ----- From: "Juan Carlos Reyes Pacheco" <juancarlosreyesp_at_gmail.com> To: <oracle-l_at_freelists.org> Sent: Monday, December 12, 2005 12:42 PM Subject: when to create a virutal ilndex

Hi reading oracle insight from oak table I found the existence of virtual indexes, and on metalink I found the existence of a Create Virtual Index Wizard.

Could some one please clarify how it is used?, I supposed in big databases, when some x$ queries becomes slower, they can create virtual indexes, and this should be a very rare practices.

Thank you.

--
Oracle Certified Profesional 9i 10g
Orace Certified Professional Developer 6i

8 years of experience in Oracle 7,8i,9i,10g and developer 6i
--
http://www.freelists.org/webpage/oracle-l



--
http://www.freelists.org/webpage/oracle-l