RelationalDBDesign RelationalDBDesign

Oracle Indexes   «Prev  Next»
Lesson 3 Types of indexes
ObjectiveUnderstand the types of indexes supported by Oracle.

Oracle Index Types

All indexes serve the same purpose which is to speed the execution of SQL statements. You can declare an index to be either unique, which means that all values in the index occur only once, or non-unique, which allows repeating values. The UNIQUE and PRIMARY KEY constraints use unique indexes to implement the constraint. Oracle supports two basic types of index structures which are the B*-tree index and the bitmapped index.

B*-tree index

The B*-tree index organizes data in an inverted tree structure. Each level in the structure is referred to as a node, and the bottom level in the B*-tree structure is called the leaf block.
Starting at the top node, each block in the node contains a series of comparison values. Depending on where the value for an entry in the index falls in comparison to the values in the node, a user query travels to another node in the next level of the tree. This type of navigation is shown in the MouseOver below.

  1. The topmost node provides an entry point. If the desired value is less than “Jones”, the retrieval proceeds to the left. If the desired value is greater than “Jones”, the retrieval proceeds to the right.
  2. Each node contains comparison values that direct the retrieval to another level of nodes or to the leaf blocks
  3. The leaf blocks contain the actual index values and the ROWIDs for the associated nodes. Each leaf block only contains a few values.
  4. The actual data in the leaf block consists of the value for the index and the ROWID of the row of the data table that contains it

b-tree index
The B*-tree index structure has several advantages:
  1. The branching structure dramatically reduces the amount of I/O to get to a particular block.
  2. Because all the index entries are located in the leaf blocks, and all leaf blocks are the same number of levels below the starting node, retrieval for all rows takes the same amount of overhead, regardless of where the entry falls in the table.

Bitmapped index

A bitmapped index is organized by index values, and each bit in the bitmap points back to a particular ROWID. If the bit has a value of 1, the corresponding row contains that value, as shown in the MouseOver below.

  1. Each value contains a series of bits, each of which point to a particular row.
  2. If a bit is set to 1, it means that the associated row contains that value.
  3. The bit is associated with the ROWID of a row
Each value contains a series of bits, each of which point to a particular row

Oracle bitmapped Index
Bitmapped indexes are not stored in sorted order, but they can be very powerful in data warehouse applications, where there are many selection conditions. Oracle can line up the bitmaps for each condition and quickly select the appropriate rows.

Oracle Index Types - Quiz

Click the quiz link below to answer a few questions about index types.
Index Types - Quiz
The next lesson shows how to create an index.