Databases Reference
In-Depth Information
C H A P T E R 8
■ ■ ■
Maintaining Indexes
Maintaining indexes is always a big part of an Oracle DBA's workload. There are many aspects to
maintaining indexes, and often there are multiple ways to achieve the same goals. This chapter explains
several key aspects of index maintenance to help you improve the performance of your indexes and to
efficiently manage index space usage.
Collecting optimal index statistics is, of course, a crucial part of index maintenance and so we start
with the collection of index statistics. The database sometimes makes an index unusable, following
certain index maintenance operations or some error conditions. The chapter explains the implications
of unusable indexes and shows how to deal with them. Often Oracle DBAs wonder if index
fragmentation is something that affects performance and space usage, and how to deal with it. The
chapter explains the various techniques Oracle offers—such as the ability to rebuild, coalesce, and
shrink indexes—and when each of them is appropriate.
The chapter briefly explains the various ways in which you can create indexes faster, as well as how
you can conserve index space usage. Finally, you'll learn how to efficiently extract complex index
creation statements from the database when you need to recreate indexes in other environments.
Gathering Statistics for Indexes
Optimizer statistics for indexes include things such as statistics relating to the number of rows, number
of leaf blocks in the index, the number of levels of the B-tree, and the clustering factor of the index. Use
the
DBMS_STATS
package to collect statistics for your indexes. As with tables, the frequency of statistics
collection depends on the amount of changes your data is going through.
The DBMS_STATS Package
You can specify the collection of index statistics by specifying the
CASCADE
option with the
GATHER_DATABASE_STATS
,
GATHER_SCHEMA_STATS
, and
GATHER_TABLE_STATS
procedures, as shown in the
following examples.
When collecting schema statistics:
SQL> execute dbms_stats.gather_schema_stats('HR', cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL>
