ORACLE - Alter Index

How to make large index creation faster ?

1) PARALLE option:

Use number of cpu - 1 for parallel option. If server has 8 CPU then give parallel 7 and check the database perforamce : create index index_name on table_name(column_name) parallel 7;

2) NOLOGGING option: It will avoid to generate a large redo log

create index index_name on table_name(column_name) parallel 7 nologging;

3) Larger block size: Create a index in a tablespace that uses Larger block size.

create tablespace index_tbs  datafile '+jdata' size 1000M BLOCKSIZE 32K;

create index index_name on table_name(column_name) parallel 7 nologging tablespace index_tbs  ;

How to alter an index ?

Note: To alter an index, your schema must contain the index or you must have the ALTER ANY INDEX system privilege.

Example: CREATE INDEX IndexName on TableName(ColumnName);

1) Rebuild/ Rebuild Online/Moving to new tablespace:

alter index index_name REBUILD [ONLINE];

alter index index_name REBUILD  TABLESPACE new_tablespace_name;

2) Coalesce :

alter index index_name COALESCE;

3) Alter storage parameters or physical attributes

alter index SchemaName.IndexName  STORAGE (PCTINCREASE 40);

alter index SchemaName.IndexName ENABLE PRIMARY KEY USING INDEX;

5) Make the index invisible:

alter index SchemaName.IndexName INVISIBLE;

alter index SchemaName.IndexName VISIBLE;

6) Rename the index:

alter index index_name RENAME TO new_index_name;

7) Monitoring of index usage:

alter index IndexName MONITORING USAGE;

select * from v$OBJECT_USAGE where index_name='IndexName';

alter index IndexName NOMONITORING USAGE;

Note: Parallel and nologging option already explained.

Useful View:

DBA_INDEXES

DB_IND_COLUMNS

INDEX_STATS

v$OBJECT_USAGE