Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 









Oracle Indexes

Oracle Database Tips by Donald Burleson

About Oracle Indexes

Indexes require rebuilding when deleted leaf rows appear or when the index has a suboptimal number of block gets per access. While it is tempting to write a script that rebuilds every index in the schema, bear in mind that your schema may contain many thousands of indexes, and a complete rebuild can be very time consuming. Hence, we need to develop a method to identify those indexes that will get improved performance with a rebuild. Let's look at one method for accomplishing this task.

Prior to Oracle9i, the only way for an Oracle DBA to monitor the usage of indexes was to derive the execution plan for all SQL inside their library cache and manually note all indexes that are used.

Recent studies have found that an Oracle database never uses more than a quarter of all indexes available or doesn't use them in the way for which they were intended. Un-used indexes waste space and also slow-down all DML, especially UPDATE and INSERT statements.

Regardless, this tool is quite useful for the Oracle DBA who inherits a database that was built by a beginner who over-indexes the tables without regard to the SQL that accessed the tables.

In sum, the index monitoring feature has very little overhead and it is quite useful for locating and dropping unneeded Oracle indexes.

Find indexes not in the KEEP Pool

A common problem occurs when an Oracle professional recognizes the benefit of placing small, frequently referenced tables in the KEEP pool, but forgets to place the associated indexes in the KEEP pool.

Just as you want frequently referenced tables in the KEEP pool, you also want to cache the indexes to minimize disk I/O.

Compress option - The compress option is used to repress duplication of keys in non-unique indexes. For concatenated indexes (indexes with multiple columns), the compress option can reduce the size of the index by more than half. The compress option allows you to specify the prefix length for multiple column indexes. In this example we have a non-unique index on several low cardinality columns (sex and hair_color), and a high cardinality column (customer_id):

CREATE INDEX cust_dup_idx
ON customer(sex, hair_color, customer_id)

Create Oracle indexes

This exercise will give you an opportunity to see how the SQL optimizer accesses indexes to generate an efficient access plan.

Your assignment involves creating these indexes in your pubs schema and noting how they change the execution plan for your SQL. Because the pubs database is so small, the cost-based optimizer will choose full-table scans because it knows that the tables reside on only a few database blocks. Hence, we will force the use of the indexes with an index hint inside the SQL statement.

For more information on creating Oracle indexes:



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.