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