Index Enhancements   «Prev  Next»

Lesson 2 Review of index types
Objective Describe index types that can be created in Oracle9i.

Review of Index Types

Describe the types of indexes that can be created in Oracle.
Four basic types of indexes are available in Oracle:
  1. standard, or B* tree[1] indexes;
  2. bitmapped indexes;
  3. reverse indexes; and
  4. function-based indexes.
The following table describes each type of index, its structure, and its normal uses:
The lowest leaf node points to a page of index values and the ROWIDs of the rows associated with them.

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.

Oracle 12c Performance Tuning

Types of Index in Oracle

Oracle Database provides several indexing schemes, which provide complementary performance functionality. The indexes can be categorized as follows:
  1. B-tree indexes These indexes are the standard index type. They are excellent for primary key and highly-selective indexes. Used as concatenated indexes, B-tree indexes can retrieve data sorted by the indexed columns. B-tree indexes have the following subtypes:
    1. Index-organized tables: An index-organized table differs from a heap-organized because the data is itself the index.
    2. Reverse key indexes: In this type of index, the bytes of the index key are reversed, for example, 103 is stored as 301. The reversal of bytes spreads out inserts into the index over many blocks.
    3. Descending indexes: This type of index stores data on a particular column or columns in descending order.
    4. B-tree cluster indexes: This type of index is used to index a table cluster key. Instead of pointing to a row, the key points to the block that contains rows related to the cluster key.
  2. Bitmap and bitmap join indexes: In a bitmap index, an index entry uses a bitmap to point to multiple rows. In contrast, a B-tree index entry points to a single row. A bitmap join index is a bitmap index for the join of two or more tables.
  3. Function-based indexes: This type of index includes columns that are either transformed by a function, such as the UPPER function, or included in an expression. B-tree or bitmap indexes can be function-based.
  4. Application domain indexes: This type of index is created by a user for data in an application-specific domain. The physical index need not use a traditional index structure and can be stored either in the Oracle database as tables or externally as a file.
In the next lesson, you will learn about descending indexes.
[1]B* tree: An index made up of a number of levels of leaf nodes. Each leaf node contains a range of values and pointers to a more detailed leaf node.
[2]Bitwise comparison: A situation where the Oracle9i database compares two values bit by bit, instead of character by character (also known as byte by byte).