B-tree and Bitmap Index

tolywang發表於2007-07-04
Index
Oracle has 2 kinds of Index: B-tree index and Bitmap Index.
B-tree index is commonly used index, which is created using CREATE INDEX command.

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 using CREATE BITMAP INDEX command.
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.
    • The purpose of creating index is to create a map between records and the key value to speed up query. Different Index structure determines different suitable scenario of the 2 kinds of index:
    1) When one column has many different distinct values, the bitmap will be very big and constructing the bitmap may cost more resource. So bitmap index is not suitable for colum with many distinct values.
    2) For B-tree index, when you update the key column value of a table, instead of update operation on table, Orace deletes the index entry and insert a new entry to the index. For bitmap index, when the key column value is updated, the whole bitmap at leaf level will be recreated. Comparing with B-tree index, it is time-comsuming and resource intensive. So bitmap index is suitable for those static colums with less update.
    3) PCTFREE parameters is different for table and index, as storage parameters. PCTFREE for index means the spaces kept for new entries. There is no PCTUSED parameter for Index. For table that is updated incrementally, PCTFREE should be set to a small value to full use disk space and increase query performance. For table that is update frequently, PCTFREE should be set to a high value to keep enough space for update.
    4) Logging or large table should be disabled for performance.
    5) Index should be put into separated tablespace for parallel process and better performance.
    6) Setting higher value for INITRANS on index than corresponding table for better performance, because index can be used by more process than table.

    來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/35489/viewspace-84734/，如需轉載，請註明出處，否則將追究法律責任。

    相關文章