Using B-Tree Indexes in Data Warehouses

A B-tree index is organized like an upside-down tree. The bottom level of the index
holds the actual data values and pointers to the corresponding rows, much as the
index in a book has a page number associated with each index entry.
In general, use B-tree indexes when you know that your typical query refers to the
indexed column and retrieves a few rows. In these queries, it is faster to find the rows by looking at the index. However, using the book index analogy, if you plan to look atevery single topic in a book, you might not want to look in the index for the topic andthen look up the page. It might be faster to read through every chapter in the book.

Similarly, if you are retrieving most of the rows in a table, it might not make sense to look up the index to find the table rows. Instead, you might want to read or scan the table. B-tree indexes are most commonly used in a data warehouse to enforce unique keysIn many cases, it may not even be necessary to index these columns in a data warehouse, because the uniqueness was enforced as part of the preceding ETL
proces sing, and because typical data warehouse queries may not work better with
such indexes. B-tree indexes are more common in environments using third normal
form schemas. In general, bitmap indexes should be more common than B-tree indexes
in most data warehouse environments.

Posted in: Index