DBA Tips Archive for Oracle

  


Virtual Indexes

by Jeff Hunter, Sr. Database Administrator


If you are famaliar with using the Oracle9i Enterprise Manager (OEM) Tuning Pack, you may have used the Virtual Index Wizard. This article provides an introduction to Oracle Virtual Indexes and how to use them in a standalone environment - without the use of OEM.

With OEM, the Virtual Index Wizard functionality allows the user to test a potential new index prior to actually building the new index in the database. It allows the CBO to evaluate the potential new index for a selected SQL statement by building an explain plan that is aware of the potential new index. This allows the user to determine if the optimizer would use the index once implemented.

If you are interested in using this feature without the use of OEM, you can use a special clause to the create index syntax - nosegment. Oracle will create the definition within the data dictionary, but will not actually create the index segment on disk. It is important to keep in mind that nothing is stored, nor accessed.


Old Explain Plan

Let's first take a look at a simple query
SQL> set autotrace traceonly explain

SQL> select name from emp where name = 'Alex';

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=164 Card=1 Bytes=500)
   1    0   TABLE ACCESS (FULL) OF 'EMP' (Cost=164 Card=1 Bytes=500)


Creating Virtual Indexes

SQL> create index emp_vir_idx1 on emp(name) nosegment;

Index created.


New Explain Plan

Now let's take a look at the new explain plan with our new virtual index:
SQL> set autotrace traceonly explain

SQL> select name from emp where name = 'Alex';

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=164 Card=1 Bytes=500)
   1    0   TABLE ACCESS (FULL) OF 'EMP' (Cost=164 Card=1 Bytes=500)


Hey, why isn't Oracle picking up my new index? Well, you need to alter your session using the Oracle hidden parameter "_use_nosegment_indexes".

SQL> alter session set "_use_nosegment_indexes" = true;

Session altered.

SQL> select name from emp where name = 'Alex';

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=500)
   1    0   INDEX (RANGE SCAN) OF 'EMP_VIR_IDX1' (NON-UNIQUE) (Cost=1 Card=1 Bytes=500)


Copyright (c) 1998-2014 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Tuesday, 14-Jan-2003 00:00:00 EST
Page Count: 13724