Re: materialized views

Here is some info from a talk I had:

Oracle8i: Materialized Views and Query rewrite The combination of Materialized Views and Query Rewrite are power tools for the Oracle data warehouse in Oracle8i. Materialized views can be used to create and automatically refresh summary fact tables (the central table in a data warehouse). Query Rewrite allows the Oracle optimizer to modify queries against the larger detail tables that can be completely satisfied by a smaller summary table. Oracle uses the summary table instead of going to the larger detail table which can improve performance substantially. In the example below, the detail table contains a count of households at a zipcode and zip+4 level. The matieriaized view, ZIP, summarizes the household count at a zipcode level. As the explain plans show, Oracle will access the ZIP materialized view rather then the ZIP4_COUNT table for the following query: Create the larger ZIP4_COUNT table:
CREATE TABLE ZIP4_COUNT
AS
SELECT ZIP, ZIP4, SUM(HH_CNT) HH_CNT
FROM TEST2
GROUP BY ZIP, ZIP4;
Create the smaller ZIP materialized view: CREATE MATERIALIZED VIEW ZIP
   BUILD IMMEDIATE
   ENABLE QUERY REWRITE
   AS
   SELECT ZIP, SUM(HH_CNT)

   FROM   	   ZIP4_COUNT
   GROUP     BY ZIP;

In the preceding query, we have created a materialized view called zip. This materialized view is a summary of the ZIP4_COUNT table. We have also enabled Oracle to rewrite a query (unless overriden with a NOREWRITE hint) that can take advantage of this view. In the following two queries, we will query the table using the NOREWRITE and REWRITE hints. Query the ZIP4_COUNT table disallowing rewrites of the query: SELECT /*+ NOREWRITE */ ZIP, SUM(HH_CNT) FROM ZIP4_COUNT
GROUP BY ZIP;
SELECT STATEMENT Optimizer=CHOOSE
  TABLE ACCESS (FULL) OF 'ZIP4_COUNT'
Elapsed Time: 0.28 seconds

In the query above, we disallow Oracle's ability to rewrite the query. Hence, the ZIP4_COUNT (the larger non-summarized) table is accessed.

SELECT /*+ REWRITE */ ZIP, SUM(HH_CNT) FROM ZIP4_COUNT
GROUP BY ZIP;
SELECT STATEMENT Optimizer=CHOOSE
  TABLE ACCESS (FULL) OF 'ZIP' Elapsed Time: 0.03 seconds

In the preceding example, Oracle rewrites the query to go to the smaller ZIP materialized view which improves the performance of query substantially.

As the example above shows, Query Rewite can improve performance by several orders of magnitude. If your database makes use of summary tables, building Materialized Views to take advantage of Oracle's Query Rewrite capability is a feature you will want to investigate when you upgrade to the Oracle8i database engine.
The following init.ora parameters muct be set to use materialized views.

query_rewrite_enable = true
query_rewrite_integrity = trusted

Rich Niemiec
Oracle Performance Tips and Techniques
Oracle Press
ISBN: 0078824346 Received on Mon Jun 07 1999 - 14:34:11 CDT