|
Oracle9i
significantly improves the functionality of materialized
views. Materialized views are an extremely important feature
for enhancing the performance of complex business intelligence
queries. A materialized view contains the results of a pre-defined
query, such as the sum of sales by region over a period of
time. There is no limit to the number of materialized views
that may be defined. When a SQL query is issued, the query
rewrite facility within Oracle will determine whether a suitable
materialized view is available. If it is, Oracle will transparently
rewrite the query to use the materialized view instead. This
results in significantly improved performance since the query
accesses the small set of rows in the materialized view rather
than all the rows in the tables referenced by the query.
Whenever the underlying tables of a materialized
view are updated, Oracle marks the materialized view as stale.
Unless QUERY_REWRITE_INTEGRITY is set to stale_tolerated,
a stale materialized view will not be used by the
query rewrite engine. Oracle provides a mechanism to keep
the materialized view fresh with respect to the base tables
called materialized view refresh.
The following types of refresh methods are
supported by Oracle.
- Complete - build from scratch
- Fast - only apply the data changes
- Force - try a fast refresh, if that is
not possible, do a complete refresh
- Never - never refresh the materialized
view
Each materialized view may have its own refresh
method. The query that defines the materialized view determines
which of the methods is applicable.Hence it may not always
be possible for a materialized view to be fast refreshable.
All materialized views can be refreshed using a complete refresh.
Once defined, a materialized view can be refreshed
to reflect the latest data changes either either ON DEMAND
or ON COMMIT.
The user can control the refresh of materialized
views by choosing to refresh ON DEMAND. Oracle provides the
following procedures to refresh ON DEMAND.
- DBMS_MVIEW.REFRESH - select one or more
materialized views to refresh
- DBMS_MVIEW.REFRESH_DEPENDENT - refresh
those which depend on a table
- DBMS_MVIEW.REFRESH_ALL_MVIEWS - refresh
all materialized views
If the user chooses to refresh ON COMMIT,
Oracle will automatically refresh the materialized view on
the commit of any transaction that updates tables referenced
by the materialized view.
In Oracle9i,
significant improvements have been made to materialized view
refresh:
- Fast refresh is now possible on materialized
views that contain joins and aggregates even when base table
data has changed using SQL DML statements (INSERT, UPDATE,
DELETE). In Oracle8i,
fast refresh was possible on materialized views that contain
joins and aggregates only if base table data was inserted
using SQL*Loader direct path.
- The algorithms used by fast refresh have
changed to significantly improve the performance of this
operation.
- Fast refresh is posssible after partition
maintainence operations such as TRUNCATE PARTITION on tables
referenced in the materialized view.
- A new mechanism called Partition Change
Tracking (PCT) has been introduced. This mechanism keeps
track of the base table partitions that have been updated
since the materialized view was last refreshed. This allows
Oracle to identify fresh data in the materialized view.
- Fast refresh is now possible with subqueries
in the FROM clause and for CUBE, ROLLUP and GROUPING SET
functions.
In order to support the above enhancements,
Oracle9i
requires that materialized view logs always be created. In
Oracle8i, the creation of materialized view logs
was optional.
The materialized view refresh enhancements
in Oracle9i
make materialized view maintenance significantly faster and
easier. They eliminate the need to access all of the data
to perform a refresh in some instances and improve the performance
of refresh in general. Together with the many other materialized
view enhancements in Oracle9i,
the materialized view refresh enhancements extend Oracle's
leadership in business intelligence.
More
Info
Oracle9i
Daily Features
|
Bookmark
