Types of Index in oracle?

Question

What are the type of indexes in oracle? How to identify the index need to create?

Answer 1

Oracle Database provides several indexing schemes that provide complementary performance functionality. These are:

• B-tree indexes: the default and the most common
• B-tree cluster indexes: defined specifically for cluster
• Hash cluster indexes: defined specifically for a hash cluster
• Global and local indexes: relate to partitioned tables and indexes
• Reverse key indexes: most useful for Oracle Real Application Clusters applications
• Bitmap indexes: compact; work best for columns with a small set of values
• Function-based indexes: contain the precomputed value of a function/expression Domain indexes: specific to an application or cartridge.

Source: http://docs.oracle.com/cd/B19306_01/server.102/b14231/indexes.htm

Answer 2

There are a number of index types in Oracle, depending on what object you need to index:

For heap tables, there are b-tree indexes and bitmap indexes.

A b-tree index is suitable for most applications; bitmap indexes have their own advantages and disadvantages.

There are also specialised indexes for specific data types, using Oracle cartridges; e.g. Oracle Text indexes, spatial indexes, and 3rd party indexes.

Furthermore, an index might be differentiated by other factors:

• an index on a partitioned table might be global or local
• an index might consist of one column, or multiple columns
• an index might be on a normal column, or on an expression (aka "function-based" index)

For more info, read the documentation.

An excellent place to find out more than you'll ever want to know about Oracle indexes is Richard Foote's blog.