Oracle has 2 kinds of Index: B-tree index and Bitmap Index.

        B-Tree indexes are the regular type that OLTP systems make much use of, and bitmap indexes are a highly compressed index type that tends to be used primarily for data warehouses.

        An index provides pointers to the rows in a table that contain a given key value. A regular index stores a list of rowids for each key corresponding to the rows with that key value. In a bitmap index, a bitmap for each key value replaces a list of rowids.

        Each bit in the bitmap corresponds to a possible rowid, and if the bit is set, it means that the row with the corresponding rowid contains the key value. A mapping function converts the bit position to an actual rowid, so that the bitmap index provides the same functionality as a regular index. If the number of different key values is small, bitmap indexes save space.

        Bitmap indexes are most effective for queries that contain multiple conditions in the WHERE clause. Rows that satisfy some, but not all, conditions are filtered out before the table itself is accessed. This improves response time, often dramatically.

        B-tree index is commonly used index, which is created usingCREATEINDEXcommand.

ITPUB个人空间�^W&m;ho+Z
K#G

        B-tree index structure:

• Root: Pointer to branch node
• Branch: Pointer to Leaf node. The distinct value of index columns are sorted and divided into different groups. Each branch is mapped to a group of data.
• Leaf: Each Leaf contains the rowid of the records it refers to. Leaf node are linked together in both directions, allowing ascending or descending search. Also leaf nodes can be sorted by ascending or descending.

        Bitmap index is some special index, which is created usingCREATE BITMAP INDEXcommand.

        Bitmap Index structure:

• Root: Pointer to branch node
• Branch: Pointer to Leaf node. Branch contains the start rowid and end rowid of the leaf blocks it refers to. (It is different from B-tree here. Here is start rowid and end rowid.)
• Leaf: Leaf is a bitmap. Each bit in the bitmap corresponds to a row in the table. You can think the bitmap like a table with RowID as Row Header and Values as column header. When the row has the value of the column header. It is marked to 1, else 0.

        To understand more about Oracle Bitmap Index, you may refer to my next post: Features of Oracle Bitmap Index

• Relative Posts
• Global Relative Posts