What is an Index

Indexes are optional data structures built on tables. Indexes can improve data retrieval performance by providing a direct access method instead of the default full table scan retrieval method. You can build Btree or bitmap indexes on one or more columns in a table.

An index
key is defined as one data value stored in the index. A Btree index sorts the keys into a
binary tree and stores these keys together with the table's ROWIDs. In a bitmap index,
a bitmap is created for each key. There is a bit in each bitmap for every ROWID in the table,
forming the equivalent of a two-dimensional matrix.The bits are set if the corresponding
row in the bitmap exists.

Btree indexes are the default index type, can be unique or non-unique, and are appropriate for medium- to high-cardinality columns—those having many distinct values. Btree indexes support row-level locking and so are appropriate for multi-user, transactional applications. The indexes supporting a PRIMARY KEY or UNIQUE constraints are Btree indexes.

A bitmap index is a special kind of database index that uses bitmaps.

Bitmap indexes have traditionally been considered to work well for data such as gender, which has a small number of distinct values, for example male and female, but many occurrences of those values. This would happen if, for example, you had gender data for each resident in a city. Bitmap indexes have a significant space and performance advantage over other structures for such data. Some researchers argue that Bitmap indexes are also useful for unique valued data which is not updated frequently.^[1] Bitmap indexes use bit arrays (commonly called bitmaps) and answer queries by performing bitwise logical operations on these bitmaps.

Bitmap indexes are also useful in data warehousing applications for joining a large fact table to smaller dimension tables^[2] such as those arranged in a star schema.

B*Tree indexes:
Good choice for most uses as they
-maintain the sort order of the data, making it easy to look up range data
-multicolumn indexes: you can use the leading edge columns to resolve a query, even if that query doesn't reference all columns in the index
-they automatically stay balanced
-performance remains relatively constant
- can also specify reverse and unique

Bitmapped indexes:
-use them to index columns with that contain a relatively small number of distinct values
-they are compact, saving space
-were designed for query intensive databases, so not recommended for OLTP databases
-not good for range scans
-are available only in Enterprise Edition

When to Create an Index

Therefore, you should create indexes only if:

• The column contains a wide range of values

• The column contains a large number of null values

• One or more columns are frequently used together in a WHERE clause or join condition

• The table is large and most queries are expected to retrieve less than 2–4% of the rows

Remember that if you want to enforce uniqueness, you should define a unique constraint in the table definition. Then a unique index is created automatically.

When not to Create an Index

It is usually not worth creating an index if:

• The table is small

• The columns are not often used as a condition in the query

• Most queries are expected to retrieve more than 2 to 4 percent of the rows in the table

• The table is updated frequently

• The indexed columns are referenced as part of an

   Expression

for more details:


http://www.dba-oracle.com/art_9i_indexing.htm
http://www.akadia.com/services/ora_bitmapped_index.html
http://www.oracle.com/technetwork/articles/sharma-indexes-093638.html