DATA WAREHOUSING FUNDAMENTALS: A Comprehensive Guide for IT Professionals

18.5. INDEXING THE DATA WAREHOUSE

In a query-centric system like the data warehouse environment, the need to process queries faster dominates. There is no surer way of turning your users away from the data warehouse than by unreasonably slow queries. For the user in an analysis session going through a rapid succession of complex queries, you have to match the pace of the query results with the speed of thought. Among the various methods to improve performance, indexing ranks very high.

What types of indexes must you build in your data warehouse? The DBMS vendors offer a variety of choices. The choice is no longer confined to sequential index files. All vendors support B-Tree indexes for efficient data retrieval. Another option is the bitmapped index. As we will see later in this section, this indexing technique is very appropriate for the data warehouse environment. Some vendors are extending the power of indexing to specific requirements. These include indexes on partitioned tables and index-organized tables.

18.5.1. Indexing Overview

Let us consider the technique of indexing from the perspective of the data warehouse. The data tables are "read-only." This feature implies that you almost never update the records or delete records. And records are not inserted into the tables after the loads. When you do adds, updates, or deletes, you incur additional overhead for manipulating the index files. But in a data warehouse this is not the case. So you can create a number of indexes ...