Databases Reference
In-Depth Information
The bottom line is that you must collect statistics on all columns, not just the indexed columns, and
specifying the
for all indexed columns size auto
option makes this impossible. Data warehouses use
fewer indexes in general compared to OLTP systems, so specifying the
for all indexed columns size
auto
option in those environments is especially likely to lead to poor execution plans. The default value
for the
METHOD_OPT
parameter starting with Oracle Database 11g is
FOR ALL COLUMNS SIZE AUTO
. Use this
default value wherever possible.
Note
Regardless of the value you assign to the
METHOD_OPT
parameter, if you specify
cascade=>true
, the
database collects statistics on all indexes.
Working with Unusable Indexes
The database may mark an index unusable in various situations, including when an index creation or
rebuild fails midway. For example, when the table data becomes more up-to-date than the indexes on
that table, SQL*Loader leaves the index in an unusable state. A direct path load may leave an index in an
unusable state when any of the following occur:
•
SQL*Loader fails to update the index because the index runs out of space.
•
The instance fails during the building of the index.
•
A unique key has duplicate values.
•
An index isn't in the same order as that specified by a sorted indexes clause.
In addition to these reasons, an index can also acquire a status of
UNUSABLE
following various
maintenance operations. For example, all of the following will result in an index becoming unusable:
Moving a table or a table partition (
alter table move
and
alter table move
partition
).
•
•
Performing an online redefinition of a table.
Truncating a table partition (
alter table truncate partition
).
•
•
Importing a partition.
•
Dropping a table partition.
Splitting a table partition or a subpartition (
alter table split partition
).
•
Maintenance operation on a partitioned index (
alter index split partition
).
•
Any time you move a table or reorganize a table, internally the database uses a different set of
ROWID
s
to point to the rows on disk, and this makes the indexes unusable since they're still pointing to the old
ROWID
s. A
ROWID
is an Oracle pseudo column that uniquely identifies a row in a table. You must make the
