TruTek

Materialized views are a powerful feature that has been part of the Oracle RDBMS since version 8.1. Based upon the snapshot replication feature, they have evolved considerably into a capable tool for significantly improving performance on queries at higher levels of hierarchical organization structures. When they are effectively implemented across an entire data warehouse, the total number of logical reads can be reduced by well over 90%. Although materialized views are considered to be a data warehouse feature, they can also be employed in other environments, including Operational Data Stores (ODS), data marts, and reporting tables in OLTP environments, where end-users will perform rollup queries on the schema.

Like other mature Oracle RDBMS features that have been designed to satisfy many requirements, there are many ways to implement materialized views. Some of these methods are more effective and efficient than others. In this paper, the materialized views will be designed for a real-world dimensional model. By example, the reader will gain understanding of some of the practical issues associated with designing and implementing materialized views. This paper specifically covers versions 8.1.7 through 9.2.0 of the RDBMS, but most of the concepts can also be applied to version 10.

When asked to "Select a payment method" for your free download, choose "Print and Call". Your download code will be e-mailed to you after we authorize it. International customers should enter TruTek's address as the shipping address and enter their real shipping address in the Special Instructions field on the payment screen.