Understand 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.
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.
The B*-tree index structure has several advantages:
The branching structure dramatically reduces the amount of I/O to get to a particular block.
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.
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.
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.