ALTER INDEX emp_name REBUILD ONLINE;
--维护索引是需要知道的:
Monitoring Space Use of Indexes
If key values in an index are inserted, updated, and deleted frequently, the index can lose its acquired space efficiently over time. Monitor index efficiency of space usage at regular intervals by first analyzing the index structure, using the ANALYZE INDEX ... VALIDATE STRUCTURE statement, and then querying the INDEX_STATS view:
SELECT PCT_USED FROM INDEX_STATS WHERE NAME = 'index';
The percentage of index space usage varies according to how often index keys are inserted, updated, or deleted. Develop a history of average efficiency of space usage for an index by performing the following sequence of operations several times:
-
Analyzing statistics
-
Validating the index
-
Checking
PCT_USED -
Dropping and rebuilding (or coalescing) the index
When you find that index space usage drops below its average, you can condense the index space by dropping the index and rebuilding it, or coalescing it.
Viewing Index Information
The following views display information about indexes:
| View | Description |
|---|---|
DBA_INDEXES
|
DBA view describes indexes on all tables in the database. ALL view describes indexes on all tables accessible to the user. USER view is restricted to indexes owned by the user. Some columns in these views contain statistics that are generated by the DBMS_STATS package or ANALYZE statement. |
DBA_IND_COLUMNS
|
These views describe the columns of indexes on tables. Some columns in these views contain statistics that are generated by the DBMS_STATS package or ANALYZE statement. |
DBA_IND_EXPRESSIONS
|
These views describe the expressions of function-based indexes on tables. |
INDEX_STATS |
Stores information from the last ANALYZE INDEX ... VALIDATE STRUCTURE statement. |
INDEX_HISTOGRAM |
Stores information from the last ANALYZE INDEX ... VALIDATE STRUCTURE statement. |
V$OBJECT_USAGE |
Contains index usage information produced by the ALTER INDEX ... MONITORING USAGE functionality. |