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 









Administration of Indexes

Oracle Database Tips by Donald Burleson

Indexes can make or break an application. A nonindexed query that takes tens of minutes can be made to return values within seconds if indexes are done properly. A critical subset of the DBA's tasks involves the placement, creation, sizing, and maintenance of the normal, bitmapped, partitioned, function-based, and subpartitioned indexes available in Oracle12c and beyond.

Oracle8 offered new functionality in the form of partitioned, bitmapped, and reversed key indexes. Oracle8i offered all of these plus the capability to do INDEXTYPE, DOMAIN INDEX, and function-based indexes, as well as more advanced partitioning options for indexes. Partitioned indexes allow the spread of index data automatically by data value range across multiple partitions that can be placed on several disk arrays or platters.

In Oracle, these partitions can be further subdivided into subpartitions. Bitmapped indexes allow for indexing of low-cardinality data, a feature that came about in and continued with Oracle8 and its later releases. Bitmapped indexes map data values as binary integers, allowing low-cardinality data to be quickly accessed with sometimes almost quantum decreases in access speed. For some specialized types of query, a reverse key index can improve data access speeds.

In Oracle9i, two new types of index were offered: the bitmap join index and the skip scan index. A bitmap join index acts as an intersection between two tables, in a sense, prejoining them via a bitmap index. A skip scan index is a specially constructed index that allows Oracle to scan independent B-tree levels instead of the normal top-down scan.

Indexes allow queries to rapidly retrieve data, with proper implementation. Single columns, or groups of columns, can be indexed. A DBA can specify whether or not an index is unique. Remember, for proper table design, each table must have a unique identifier. A unique index is automatically created when a unique or primary key constraint clause is used in a CREATE or ALTER TABLE command.

Indexes speed the search for queries when approximately 2 to 7 percent of the table or less is being retrieved. For larger retrievals, inserts, and updates to index columns, and deletes, indexes slow response. An exception to this is if you use a bitmapped index for low-cardinality data.

How columns are indexed affects their efficiency. Order columns should be specified to reflect the way a select will retrieve them. The column accessed most often should be put first. Remember, the leading portion of the index is used to speed queries. A composite index can be created on up to 16 columns. Columns of type LONG and LONG RAW cannot be indexed.

Creation of Indexes

Oracle8 introduced the concepts of partitioned, bitmapped (available in 7.3.2), and reversed key indexes. These concepts were carried over in Oracle8i, which also added functional indexes and descending indexes, as well as index types. In Oracle8i and Oracle9i, local and global partitioning and subpartitioning are also available for indexes.

When an index is created, you should specify the INITRANS and MAXTRANS parameters, or you will have to drop and re-create the index, or use the index rebuild commands to change them.  If you are loading data, create indexes last, as it is more efficient to load data and then create indexes than to update multiple disks concurrently during the load process. If a table is fairly static, then a large number of indexes may be good; however, if you have a table with a large number of inserts and updates, then multiple indexes will cause a performance hit.

The value for PCTFREE should be set according to how much update activity you expect on the table. The space specified as PCTFREE (a percentage of each block) will never be used unless there is update activity against the columns in an index. Therefore, for primary keys whose values you expect never to be updated, set PCTFREE low. For foreign key or lookup indexes, set PCTFREE higher, to allow for expected update activity.

Always specify FREELISTS if the index will be updated by more than one process. The value for FREELISTS will correspond to the number of concurrently updating processes. This is another parameter that requires a rebuild of the index to alter in Oracle versions earlier than 8.1.6.

When creating an index, always specify the tablespace where it is to be placed. If the location is not specified, it goes in your default tablespace, which is probably the location of the table you are indexing as well! Not specifying the tablespace can result in instant contention for disk resources and poor performance.

I once saw an application improve query speeds by 300 percent just by placing the indexes in their proper location, as opposed to being in with the tables. The DBA before me had dropped and re-created the primary keys for each of the tables simply by using the DISABLE/ENABLE clause of the ALTER TABLE and without specifying the storage clause.

If you use a parallel server to create your index, remember to set your extent sizes to X/N bytes, where X is the calculated size for the index (maximum expected size) and N is the number of query servers to be used. Each parallel query server takes an extent to do its work above the high-water mark; this is true for table creations and loads in parallel as well.

To further speed index builds, specify the UNRECOVERABLE option so that the index doesn't generate any redo. Remember to immediately do a backup since the creation will not be logged. Use this for large index creations, as it does little good for small indexes.

Composite indexes cannot exceed 32 columns, or half the available data space in a block, whichever comes first.

To create a function-based index in your own schema on your own table, in addition to the prerequisites for creating a conventional index, you must have the QUERY REWRITE system privilege. To create the index in another schema or on another schema's table, you must have the GLOBAL QUERY REWRITE privilege. In both cases, the table owner must also have the EXECUTE object privilege on the function(s) used in the function-based index. The functions used in a function-based index must be DETERMINISTIC (i.e., they always return the same answer for the same input) and be created with the DETERMINISTIC keyword. In addition, in order for Oracle to use function-based indexes in queries, the QUERY_REWRITE_ENABED initialization parameter must be set to TRUE, and the QUERY_REWRITE_INTEGRITY initialization parameter must be set to TRUSTED. Following the function-based indexes creation, both the index and table must be analyzed to enable the CBO to recognize the new index and use it.

This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".


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.