A data warehouse is primarily used to organize data so that queries for analysis and decision support can be answered quickly. As data warehouses grow in size, it is not uncommon to find a fact table several gigabytes or even terabytes in size. In order to obtain fast query response, it is extremely important for the database to retrieve and process such large amounts of data efficiently. Performance optimizations for update-centric OLTP systems are well understood; however, data warehouses, being query-centric, have vastly different requirements. In this chapter, we'll look at several features in the Oracle database specifically designed to improve query performance in a data warehouse.
Good performance in a warehouse is largely dependent on sound physical database design. We will look at several techniques, including partitioning, data segment compression, and bitmap indexing, that are suited for data warehouses. We will also discuss query optimizations, such as partition pruning, partition-wise join, and star transformation.
Decision-support applications often need to perform computations such as period-over-period comparisons and cumulative aggregations. These queries traditionally required extremely complex and lengthy SQL. Oracle and several other database vendors have started an effort to provide standardized extensions to the SQL language to allow users to express such queries simply and process them efficiently. We will look at some of these new SQL functions.