Oracle Indexes

Check

SELECT tablespace_name, index_name, status

  FROM dba_indexes

 WHERE index_name = '&index_name';

A status of 'UNUSABLE' is resolved by rebuilding the index (see later)

To show indexed columns for a table use...

SET LINESIZE 200

SELECT index_owner,

       index_name,

       table_owner,

       table_name,

       SUBSTR(column_name,1,30) AS colname,

       column_position

  FROM dba_ind_columns

 WHERE table_name = '&table_name'

 ORDER BY index_owner,

          index_name,

          table_owner,

          table_name,

          column_position;

CREATE

B*Tree

CREATE INDEX myIndex ON myTable(myCol1,myCol2) TABLESPACE myIndexTablespace COMPUTE STATISTICS; 

Bitmap

TODO

Function-Based

CREATE_INDEX_COST

"This procedure determines the cost of creating an index on an existing table. The input is the DDL statement that will be used to create the index. The procedure will output the storage required to create the index."


https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_space.htm#i1002659

REBUILD

Does an Index need to be rebuilt?

Probably not... almost all indexes, even where inserts/deletes affect random blocks, will find an equilibrium point at about 65%-70% utilisation. But, for the edge cases...

ANALYZE INDEX index_name VALIDATE STRUCTURE; 

Rebuild vs Coalesce

Rebuild

Coalesce

Online vs Offline

You cannot specify REBUILD ONLINE for bitmap indexes or for indexes that enforce referential integrity constraints.  

Examples

ALTER INDEX index_name REBUILD ONLINE;

DROP

DROP INDEX myIndex;

Bibliography