Systems Engineering and RDBMS

Virtual Indexes in Oracle

Posted by decipherinfosys on July 11, 2007

How many times have you wished that in a VLDB (Very Large Database), without actually creating/modifying an index, you had the ability to check whether an index creation or a change to an existing index would help you tune a query better?  Sort of like taking a best guess, trying out a few options w/o actually populating the index and collecting stats on it.  That would save disk space and more so the time involved in tuning.  Oracle has a “un-documented” (un-documented constructs are not supported by Oracle Support so use it at your own risk) nosegment clause in their “Create Index” statement using which you can create an index definition without actually physically creating that index tree.  This is also called as a virtual index or a fake index.  Oracle also has a “Virtual Index Wizard” in their tuning pack using which you can see what kind of a performance increase/decrease in performance you can expect with those changes.  If you trace out those sessions, you can see actually what goes on in the back-ground.

Our recommendation would be to use the Oracle tuning pack if you intend to play with this feature since all this work is well contained in that wizard and you can also enlist help from Oracle support for that work.  If you just execute the SQL Statements directly, then remember that this is an un-supported feature.  If you are using plain SQL, then in order to analyze the impact of these virtual indexes on the execution plans, you would need to use the hidden parameter “_use_nosegment_indexes” to evaluate the changes in the execution plan.  Let’s take a very simple example to cover this feature:

Say, I have a table called USER_MASTER which has a column called USER_FIRST_NAME.  Let’s create the virtual index on it and then look at the execution plan change for this query when we query on that column.

SQL> create index virtual_Index on USER_MASTER (USER_FIRST_NAME) nosegment;

SQL> set autotrace on explain;

SQL> alter session set “_use_nosegment_indexes” = true;

Session altered.

SQL> select * from USER_MASTER where USER_FIRST_NAME = ‘JOE’;

OBJECT     OPERATION
———- —————————————-
SELECT STATEMENT()
NESTED LOOPS()
USER_MASTER        TABLE ACCESS(FULL)
USER_MASTER         TABLE ACCESS(BY INDEX ROWID)
VIRTUAL_INDEX         INDEX(UNIQUE SCAN)

This can prove to be useful when you are working with TB (Tera-Byte) size databases and need to check your index changes quickly.  Play around with it using the tuning pack from Oracle and trace out the sessions to see what it does behind the scenes and if you feel comfortable with it, you can then use it to your advantage to make effective decisions that can save you time and help do your job more effectively.

SQL Server also has an Index Tuning Wizard (DTA in SQL 2005) which makes recommendations on additional indexes and indexed views based on a workload, a query etc. and shows the percentage gains due to those.

One Response to “Virtual Indexes in Oracle”

  1. […] use the 10053 trace to understand why the optimizer did what it did, you can play with the creation virtual indexes to see how that plan is […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: