数据库应用技术程第四章数据库对象.pptVIP

* * * * * * * * * * * * How Indexes Are Stored Although all the indexes use a B-tree structure, the term B-tree index is usually associated with an index that stores a list of ROWIDS for each key. Structure of a B-Tree Index At the top of the index is the root, which contains entries that point to the next level in the index. At the next level are branch blocks, which in turn point to blocks at the next level in the index. At the lowest level are the leaf nodes, which contain the index entries that point to rows in the table. The leaf blocks are doubly linked to facilitate scanning the index in an ascending as well as descending order of key values. Format of Index Leaf Entries An index entry is made up of the following components: An entry header, which stores number of columns and locking information Key column length-value pairs, which define the size of a column in the key followed by the value for the column (The number of such pairs is a maximum of the number of columns in the index.) ROWID of a row, which contains the key values 8 Bitmap Indexes Bitmap indexes are more advantageous than B-tree indexes in certain situations: When a table has millions of rows and the key columns have low cardinality—that is, there are very few distinct values for the column. For example, bitmap indexes may be preferable to B-tree indexes for the gender and marital status columns of a table containing passport records. When queries often use a combination of multiple WHERE conditions involving the OR operator. When there is read-only or low update activity on the key columns. Structure of a Bitmap Index A bitmap index is also organized as a B-tree, but the leaf node stores a bitmap for each key value instead of 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. As shown in the diagram, the leaf node of a bitmap index contains the following: An entry header,