Check

SELECT tablespace_name, index_name, status

  FROM dba_indexes

 WHERE index_name = '&index_name';

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."

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

• You can use rebuild to move an index to another tablespace.

• Requires disk space to build a copy of the index (i.e. it does not rebuild in place).

• Creates new index tree and shrinks heights

Coalesce

• Coalesces leaf blocks that are in the same branch

• Quickly frees index leaf blocks for use

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;

Generate DDL

Bibliography