Index type
|
Structure
|
Uses
|
B*-tree index
|
The B*-tree index uses a hierarchy of index nodes. Each index node contains one or more branch blocks, and each branch block contains several values, in sorted order. As a query traverses down the levels of branch blocks,
it is compared to the values in the block and directed to another set of branch blocks that further specify the index value.
The bottom level of a B*-tree index contains leaf blocks, which contain distinct values for the index and pointers to the associated row in the table.
|
The B*-tree index is the standard index used in an Oracle database. |
Bitmapped index
|
In a bitmapped index, each value for an index is associated with a bit in a string of bits. Each row represented in the index has the bit string associated with it, with only one bit in the string set to 1 . When many indexes have to be compared, the Oracle database can simply do a bitwise comparison[2] to determine quickly which rows contain all of a specific combination of values.
|
The bitmapped index is used when there are not a lot of distinct values for an index and when values in many indexes have to be compared. The bitmapped index is primarily used in data warehouses.
|
Reverse index
|
A reverse index uses the same structure as a B*-tree index. The difference is that the values in the reverse index are automatically reversed before being stored, and are returned to their original order when the are retrieved.
|
Reverse indexes are used where the values are steadily increasing and older values are being deleted, which leads to an unbalanced B*-tree in a normal index.
|
Function-based index
|
A function-based index is the same as a B*-tree index except that you can use the result of a function to create the index, thus saving users the overhead of executing the function on every row in the table as part of a query.
|
A function-based index is used when functions will be used for selecting and sorting results.
|