Databases Reference
In-Depth Information
SQL> sho parameter skip
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
skip_unusable_indexes boolean TRUE
SQL> insert into test values (2222);
SQL> alter index test_idx1 unusable;
Index altered.
SQL>
Even though the
skip_unusable_indexes
parameter is set to
TRUE
, the database issues an error when
you try to insert some data.
SQL> insert into test values (3333);
insert into test values (3333)
*
ERROR at line 1:
ORA-01502: index 'HR.TEST_IDX1' or partition of such index is in unusable state
SQL>
As this example shows, the database will issue an error and terminate any DML statement involving an
unusable index that enforces a unique constraint. The reason for this is simple: allowing
insert
or
update
operations on a table where the unusable index is used to enforce a unique constraint might
violate the constraint.
Managing Index Space Usage
Over time, indexes can potentially experience fragmentation due to large numbers of deletions (as well
as some types of insertions). DBAs often wonder about the correct approach to handling the space usage
by indexes, especially large ones. Oracle provides three ways to handle fragmentation within an index—
rebuilding, coalescing, and shrinking an index—and each of them serves a different purpose. The
following sections discuss three index reorganization techniques and provide some insight into when a
certain approach is appropriate and when it isn't.
Rebuilding to Reduce Fragmentation
Rebuilding an index recreates an existing index. You can rebuild an entire index, a partition, or a
subpartition of a partitioned index without having to recreate the entire index. In order to rebuild an
index, use the
alter index
statement in the following way:
SQL> alter index test_idx1 rebuild;
Index altered.
SQL>
