This article deals with Materialized
views and helps you realize the potential of Oracle
8i in Data Warehousing and DSS Solutions. These
views work by pre-calculating the aggregate operations
and the optimizer will transparently rewrite the
query to use these views.
Introduction
Materialized views are stored
summaries of queries containing precomputed results.
Materialized views greatly improve data warehouse
query processing.The existence of a materialized
view is transparent to SQL applications, so a DBA
can create or drop materialized views at any time
without affecting the validity of SQL applications.
Materialized views improve query
performance by precalculating expensive join and
aggregation operations on the database prior to
execution time and storing these results in the
database. The query optimizer can make use of materialized
views by automatically recognizing when an existing
materialized view can and should be used to satisfy
a request. It then transparently rewrites the request
to use the materialized view. Queries are then directed
to the materialized view and not to the underlying
detail tables or views. Rewriting queries to use
materialized views rather than detail relations
results in a significant performance gain.
Materialized views can be refreshed
automatically whenever the data is changed in the
underlying tables. The refresh method can be incremental
(fast refresh) or complete. Incremental method re-populates
only the changed data. Complete method truncates
and rebuilds the view. The refresh process can be
enabled by adding the REFRESH clause while creating
the materialized view. You can suppress the refresh
process for the entire life of the view.
Example 1 :
The following statement creates
and populates a materialized view SALES_BY_MONTH_BY_STATE.
The materialized view will be populated with data
as soon as the statement executes successfully,
and subsequent refreshes will be accomplished by
reexecuting the materialized view's query.
CREATE MATERIALIZED
VIEW sales_by_month_by_state
TABLESPACE my_ts
PARALLEL (10)
ENABLE QUERY REWRITE
BUILD IMMEDIATE
REFRESH COMPLETE AS
SELECT t.month, g.state, SUM(sales) AS sum_sales
FROM fact f, time t, geog g
WHERE f.cur_date = t.cur_date
AND f.city_id = g.city_id
GROUP BY month, state;
The fact table stores the actual data about the
sales by month and state, time table has the day,month,
year dimenstions and geog stores the state names.
This view is built and populated with data immediately.
Lets asssume that you asked oracle
to calculate the same values in a query and if query_rewrite
is allowed on your server, the optimizer will automatically
use the values in the above precomputed view.
NOTES:
1. Set QUERY_REWRITE_ENABLE=TRUE
in init.ora
2. Set JOB_QUEUE_PROCESSES=1 (
1 or more based on your requirements).
RESTRICTIONS:
1. Materialized views consume
storage space. Be aware to provide storage parameters
according to the data it holds.
2. There cannot be any set functions
like UNION, MINUS in the underlying query for materialized
views.
