Rebuilding indexes online

Alter index emp_name rebuild online;

-- You need to know how to maintain indexes:

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, usingANALYZE INDEX ... VALIDATE STRUCTUREStatement, and then queryingINDEX_STATSView:

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 grouping the following sequence of operations several times:

• Analyzing statistics

• Validating the index

• CheckingPCT_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 ALL_INDEXES USER_INDEXES	DBAView describes indexes on all tables in the database.ALLView describes indexes on all tables accessible to the user.USERView is restricted to indexes owned by the user. Some columns in these views contain statistics that are generated byDBMS_STATSPackage orANALYZEStatement.
DBA_IND_COLUMNS ALL_IND_COLUMNS USER_IND_COLUMNS	These views describe the columns of indexes on tables. Some columns in these views contain statistics that are generated byDBMS_STATSPackage orANALYZEStatement.
DBA_IND_EXPRESSIONS ALL_IND_EXPRESSIONS USER_IND_EXPRESSIONS	These views describe the expressions of function-based indexes on tables.
INDEX_STATS	Stores information from the lastANALYZE INDEX ... VALIDATE STRUCTUREStatement.
INDEX_HISTOGRAM	Stores information from the lastANALYZE INDEX ... VALIDATE STRUCTUREStatement.
V$OBJECT_USAGE	Contains index usage information produced byALTER INDEX ... MONITORING USAGEFunctionality.