Oracle9iR2 Data Warehousing
This book will show you how to use the Oracle database with tools such as Oracle Discoverer to query the warehouse, generate reports that can be deployed over the web, and gain better insight into your business.
TABLE OF CONTENTS Oracle9iR2 Data Warehousing
4.2: Creating a materialized view
4.2 Creating a materialized view
In Oracle, a summary is created using an object known as a materialized view (MV). A materialized view precomputes and stores the result of a SQL query. Note that a summary is typically an aggregate query; however, materialized views can be created for any query. In this respect, a materialized view is quite similar to a conventional view. The difference is that by materializing the view, you save the results in the database. In the remainder of this chapter, we will use the term materialized view instead of summary.
| Hint: | Materialized views can also be used for replication of data. Before Oracle 8 i, such materialized views were called snapshots. Materialized views include all the features that were available with Snapshots and more. In this book, we will only focus on the application of materialized views to data warehousing. |
A materialized view is created using the CREATE MATERIALIZED VIEW statement. The following example creates a materialized view named MONTHLY_SALES_MV, which contains the total sales of each product for each month.
CREATE MATERIALIZED VIEW MONTHLY_SALES_MVPCTFREE 0 TABLESPACE mview <- storage parametersSTORAGE (initial 64k next 64k pctincrease 0)BUILD IMMEDIATE <- when to populate itREFRESH FORCE ...
Copyright Elsevier Science (USA) 2003 under license agreement with Books24x7
