Oracle Essentials: Oracle9i, Oracle8i and Oracle8, Second Edition

Managing the Data Warehouse

Once you’ve built your data warehouse topology, you’re likely to deploy multiple Oracle databases to implement the data warehouse and its data marts. Enterprise-wide warehouses are becoming more common on Unix servers, and smaller data marts are common on Windows NT machines.

The Oracle Enterprise Manager (EM) provides a common GUI for managing these multiple instances regardless of the underlying operating system. A new feature in Oracle8i is the ability to use EM from a web browser and a multiuser repository for tracking and managing the Oracle instances. (EM is discussed in much more detail in Chapter 5.) In warehousing, in addition to basic management, ongoing tuning for performance is crucial. The Diagnostics and Tuning packs are often used in tandem to help identify where performance improvements can be made.

Within the largest warehouses and data marts, you may want to manage or maintain availability to some of the data even as other parts of the database are moved offline. Oracle’s Partitioning option enables data partitions based on business value ranges (such as date) for administrative flexibility, while adding enhanced query performance through the cost-based optimizer’s practice of eliminating partition access for nonrelevant partitions. For an example of administrative flexibility, consider the common data warehousing requirement for “rolling window” operations, which add new data and remove old data based on time. A new partition can ...