| |
 |
|
Oracle Tips by Burleson |
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 Remote DBA’s tasks involves the placement, creation, sizing,
and maintenance of the normal, bitmapped, partitioned,
function-based, and subpartitioned indexes available in Oracle8,
Oracle8i and Oracle9i.
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 Oracle8i, 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 are
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 Remote 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.
See Code Depot
www.oracle-script.com |
