Administration of Indexes

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 7.3.2.3 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.