Rebuild or coalesce an existing index
Deallocate unused space or allocate a new extent
Specify parallel execution (or not) and alter the degree of parallelism
Alter storage parameters or physical attributes
Enable or disable key compression
Mark the index unusable
Make the index invisible
Rename the index
Start or stop the monitoring of index usage
You cannot alter index column structure.
More detailed discussions of some of these operations are contained in the following sections:
Oracle Database SQL Language Reference for details on the
Alter the storage parameters of any index, including those created by the database to enforce primary and unique key integrity constraints, using the
ALTER INDEX statement. For example, the following statement alters the
ALTER INDEX emp_ename STORAGE (PCTINCREASE 50);
The storage parameters
MINEXTENTS cannot be altered. All new settings for the other storage parameters affect only extents subsequently allocated for the index.
For indexes that implement integrity constraints, you can adjust storage parameters by issuing an
ALTER TABLE statement that includes the
USING INDEX subclause of the
ENABLE clause. For example, the following statement changes the storage options of the index created on table
emp to enforce the primary key constraint:
ALTER TABLE emp ENABLE PRIMARY KEY USING INDEX;
See Also:Oracle Database SQL Language Reference for syntax and restrictions on the use of the
Before rebuilding an existing index, compare the costs and benefits associated with rebuilding to those associated with coalescing indexes as described in Table 19-1.
When you rebuild an index, you use an existing index as the data source. Creating an index in this manner enables you to change storage characteristics or move to a new tablespace. Rebuilding an index based on an existing data source removes intra-block fragmentation. Compared to dropping the index and using the
CREATE INDEX statement, re-creating an existing index offers better performance.
The following statement rebuilds the existing index
ALTER INDEX emp_name REBUILD;
REBUILD clause must immediately follow the index name, and precede any other options. It cannot be used in conjunction with the
DEALLOCATE UNUSED clause.
You have the option of rebuilding the index online. Rebuilding online enables you to update base tables at the same time that you are rebuilding. The following statement rebuilds the
emp_name index online:
ALTER INDEX emp_name REBUILD ONLINE;
Note:Online index rebuilding has stricter limitations on the maximum key length that can be handled, compared to other methods of rebuilding an index. If an ORA-1450 (maximum key length exceeded) error occurs when rebuilding online, try rebuilding offline, coalescing, or dropping and recreating the index.
ALTER INDEX index INVISIBLE;
To make an invisible index visible, issue this statement:
ALTER INDEX index VISIBLE;
To find out whether an index is visible or invisible, query the dictionary views
DBA_INDEXES. For example, to determine if the index
IND1 is invisible, issue the following query:
SELECT INDEX_NAME, VISIBILITY FROM USER_INDEXES WHERE INDEX_NAME = 'IND1'; INDEX_NAME VISIBILITY ---------- ---------- IND1 VISIBLE
See Also:"Creating an Invisible Index" for a definition of invisible indexes.
To rename an index, issue this statement:
ALTER INDEX index_name RENAME TO new_name;
Oracle Database provides a means of monitoring indexes to determine whether they are being used. If an index is not being used, then it can be dropped, eliminating unnecessary statement overhead.
To start monitoring the usage of an index, issue this statement:
ALTER INDEX index MONITORING USAGE;
Later, issue the following statement to stop the monitoring:
ALTER INDEX index NOMONITORING USAGE;
V$OBJECT_USAGE can be queried for the index being monitored to see if the index has been used. The view contains a
USED column whose value is
NO, depending upon if the index has been used within the time period being monitored. The view also contains the start and stop times of the monitoring period, and a
MONITORING column (
NO) to indicate if usage monitoring is currently active.
Each time that you specify
MONITORING USAGE, the
V$OBJECT_USAGE view is reset for the specified index. The previous usage information is cleared or reset, and a new start time is recorded. When you specify
NOMONITORING USAGE, no further monitoring is performed, and the end time is recorded for the monitoring period. Until the next
ALTER INDEX...MONITORING USAGE statement is issued, the view information is left unchanged.