B*Tree index & bitmap index

一. B*Tree index

1. The structure of B*Tree index                                                                 

Root node                                                                         0 B1, 500 B2, 1000 B3                                                               

                                                        _______________________|__________________

                                                      B1                                                B2                                       B3

Branch node                0 L1, 200 L2, 400 L3            500 L4, 700 L5, 800 L6         1000 L7, 1400 L8

                                            _____|_____                             _____|_____                           ___|___

Leaf node                        L1       L2      L3.....                    L4         L5       L6.....                  L7          L8 .....                         Stored value and pointer to row(rowid)            

2. Suitable circumstance

• The cardinality in the column is high, and
  
• The number of rows in the table is high, and
• The column is used in WHERE clauses or JOIN conditions.
  

二. Bitmap index

Suitable circumstance

• The cardinality in the column is low, and
  
• The number of rows in the table is high, and
  
• The column is used in Boolean algebra operations.