Types of Indexes

• A BTree or binary tree index stores table ROWID pointers and indexed column values in a binary tree type structure.
• A bitmap index stores indexed column values relative to bitmap 1's and 0's.
• A function based index stores the result of a function or expression.
• An index organized table sorts both indexed and non-indexed column (data column values) in the indexed structure as a BTree.
• A cluster merges part or all of the columns from one or more tables for fast read access.
• A bitmap join combines a bitmap index and a non-bitmap index for fast access purposes.
• Domain indexes are Oracle Applications specific such as Oracle Spatial.

BTree (Binary Tree) Index

Bitmap Index

Function Based Index

Index Attributes

Indexes can have various different attributes, applicable to all or some index types.

• Ascending or descending indexes are sorted in the applicable order.
• Uniqueness requires that an index value must be unique in relation to all other rows in the same index.
• A composite index is an index with more than one column.
• Compression is where duplicate prefix column values in composite indexes are not stored.
• A reverse key index stores values in the index columns in reverse order in order to help ensure index values are inserted into different blocks.
• The NOSORT option applies when an index is not required to be physically sorted.

Ascending or Descending

Composites

Compression

Reverse Key Indexes

Index Syntax

CREATE INDEX Command

CREATE [ UNIQUE | BITMAP ] INDEX [ schema.] index ON
{
	[ schema.]table [ alias ] ( column [, column … ] | expression )
	{
		[ [ NO ] COMPRESS [ n ] ] [ NOSORT | REVERSE ]
		[ ONLINE ] [ COMPUTE STATISTICS ] [ TABLESPACE tablespace ]
		[ [ NO ] LOGGING ]
		[ physical properties ] [ partitioning properties ] [ parallel properties ]
	]
}
| CLUSTER [ schema.]cluster { cluster properties } | bitmap join index clause;

ALTER INDEX Command

ALTER [ UNIQUE | BITMAP ] INDEX [ schema.]index
[
	[ ENABLE | DISABLE ] [ UNUSABLE ] [ COALESCE ]
	[ RENAME TO index ] [ [ NO ] LOGGING ] [ [ NO ] MONITORING USAGE ]
	[ REBUILD [ [ NO ] COMPRESS [ n ]  ] [ [ NO ] REVERSE ]
		[ ONLINE ] [ COMPUTE STATISTICS ] [ TABLESPACE tablespace ] 			[ [ NO ] LOGGING ]
	]
	[ physical properties ] [ partitioning properties ] [ parallel properties ]
	[ deallocate unused clause ] [ allocate extent clause ]
]
| CLUSTER [ schema.]cluster { cluster properties };

DROP INDEX [schema.]index;