Managing Index

What is an Index?

An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns. By default, Oracle creates B-tree indexes.

Major Data Dictionary view to manage Index

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_INDEXES
·  ALL_INDEXES
·  USER_INDEXES

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

·  DBA_IND_COLUMNS
·  ALL_IND_COLUMNS
·  USER_IND_COLUMNS

These views describe the expressions of function-based indexes on tables. DBA_IND_STATISTICS

·  ALL_IND_EXPRESSIONS
·  USER_IND_EXPRESSIONS

These views contain optimizer statistics for indexes.

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.

·  ALL_IND_STATISTICS
·  USER_IND_STATISTICS

Create an Index

Syntax: 
CREATE [UNIQUE] INDEX index_name
  ON table_name (column1, column2, . column_n)
  [ COMPUTE STATISTICS ];

Create a Function-Based Index

In Oracle, you are not restricted to creating indexes on only columns. You can create function-based indexes.

The syntax for creating a function-based index is:

Syntax :
CREATE [UNIQUE] INDEX index_name
  ON table_name (function1, function2, . function_n)
  [ COMPUTE STATISTICS ];

SQL Code: 
CREATE INDEX ename_idx ON emp (UPPER(emane));

Rebuild Index Syntax

Syntax: 
alter index index_name rebuild;

Rebuild Index of multiple users

SQL Code: 
Select  'alter index ' || OWNER ||'.'||INDEX_NAME || ' rebuild;'
from    all_indexes
Where   Owner = 'USER NAME'
    OR  Owner = 'USER NAME'

Collect Statistics on an Index

If you forgot to collect statistics on the index when you first created it or you want to update the statistics, you can always use the ALTER INDEX command to collect statistics at a later date.

The syntax for collecting statistics on an index is:

Syntax :
ALTER INDEX index_name
  REBUILD COMPUTE STATISTICS;

Move Table and Index to Other Table Space

Assume that the name of the table space convention like user name and postfix by idx and ts

Syntax: 
ALTER TABLE schema.table_name MOVE TABLESPACE TABLESPACE_NAME;
 
SQL Code:
select 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' MOVE TABLESPACE ' || OWNER || ’ts’ ||';'
from ALL_TABLES
WHERE OWNER IN ('USER NAME', 'USER NAME', 'USER NAME');

Rebuild and move index to another tablespace. Also it can be used to migrate index to tablespace with diffrent blocksize.

If your table contains LONG column you must export and import data with exp/expdp, imp/impdb utilities.

Syntax: 
ALTER INDEX schema.index_name REBUILD TABLESPACE TABLESPACE_NAME;

SQL Code
select 'ALTER INDEX ' || OWNER || '.' || INDEX_NAME || ' REBUILD TABLESPACE ' || OWNER || 'idx’;'
from ALL_INDEXES
WHERE OWNER IN ('USER NAME', 'USER NAME', 'USER NAME');

ORA-01502 index 'string.string' or partition of such index is in unusable state

Cause: An attempt has been made to access an index or index partition that has been marked unusable by a direct load or by a DDL operation.

Action: DROP the specified index, or REBUILD the specified index, or REBUILD the unusable index partition.

This error code says your index is in an unusable state. It’s easy to find these indexes.

How to identify the index unusable

SQL Code:
select index_name, status, owner
from all_indexes
where STATUS = 'UNUSABLE';

How to rebuild index unusable

SQL Code:
select 'alter index '||owner||'.'||index_name||' rebuild;'
from dba_indexes
where STATUS = 'UNUSABLE';

Drop an Index

Syntax:
DROP INDEX index_name;