Systems Engineering and RDBMS

Archive for July 11th, 2007

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’;

———- —————————————-

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.

Posted in Oracle | 1 Comment »