| Lesson 8 | Managing materialized views |
| Objective | Describe the function of the DBMS_OLAP and DBMS_MVIEW packages. |
Managing Materialized Views
DBMS_OLAP, DBMS_MVIEW
Oracle includes two PL/SQL packages that help you to manage materialized views. The two packages are
DBMS_OLAP and DBMS_MVIEW.The DBMS_OLAP package
The
With Oracle Database 10g, the DBMS_OLAP package has been replaced with improved technology. While Oracle recommends you not begin development using DBMS_OLAP, Oracle continues to support DBMS_OLAP, and your existing applications
using DBMS_OLAP will continue to work. DBMS_OLAP package is primarily used for gathering statistics that are used for data warehouses. Two specific procedures in the DBMS_OLAP procedure relate to materialized views:
- RECOMMEND_MV: The
RECOMMEND_MVpackage analyzes the statistics of the uniqueness of your tables and columns and recommends which materialized views should be created. These recommendations are based on a set of all possible queries against the tables. - RECOMMEND_MV_W: The
RECOMMEND_MV_Wprocedure performs the same sort of analysis as theRECOMMEND_MVprocedure, except that this procedure uses the actual workload of your database in its calculations.
If you are developing new or substantially modified applications and had previously used the Summary Advisor in DBMS_OLAP, you should now use the SQL Access Advisor.
Overview
The DBMS_OLAP package, presented here for reasons of backward compatibility, provides a collection of materialized view analysis and advisory functions that are callable from any PL/SQL program. Some of the functions generate output tables.
DBMS_OLAP performs seven major functions, which include materialized view strategy recommendation, materialized view strategy evaluation, reporting and script generation, repository management, workload management, filter management, and dimension validation.
To perform materialized view strategy recommendation and evaluation functions, the workload information can either be provided by the user or synthesized by the Advisor engine. In the former case, cardinality information of all tables and materialized views referenced in the workload are required. In the latter case, dimension objects must be present and cardinality information for all dimension tables, fact tables, and materialized views are required. Cardinality information should be gathered with the DBMS_STATS.GATHER_TABLE_STATS procedure. Once these functions are completed, the analysis results can be presented with the reporting and script generation function.
The workload management function handles three types of workload, which are user-specified workload, SQL cache workload, and Oracle Trace workload. To process the user-specified workload, a user-defined workload table must be present in the user's schema. To process Oracle Trace workload, the Oracle Trace formatter must be run to preprocess collected workload statistics into default V-tables in the user's schema.
DBMS_OLAP performs seven major functions, which include materialized view strategy recommendation, materialized view strategy evaluation, reporting and script generation, repository management, workload management, filter management, and dimension validation.
To perform materialized view strategy recommendation and evaluation functions, the workload information can either be provided by the user or synthesized by the Advisor engine. In the former case, cardinality information of all tables and materialized views referenced in the workload are required. In the latter case, dimension objects must be present and cardinality information for all dimension tables, fact tables, and materialized views are required. Cardinality information should be gathered with the DBMS_STATS.GATHER_TABLE_STATS procedure. Once these functions are completed, the analysis results can be presented with the reporting and script generation function.
The workload management function handles three types of workload, which are user-specified workload, SQL cache workload, and Oracle Trace workload. To process the user-specified workload, a user-defined workload table must be present in the user's schema. To process Oracle Trace workload, the Oracle Trace formatter must be run to preprocess collected workload statistics into default V-tables in the user's schema.
Views
Several views are created when using DBMS_OLAP. All are in the SYSTEM schema. To access these views, you must have a DBA role.SYSTEM.MVIEW_EVALUATIONS
The DBMS_MVIEW package
DBMS_MVIEW is a synonym for the DBMS_SNAPSHOT package. The package contains a number of procedures that mainly apply to the use of snapshots of a database and two that can be useful with materialized views:
- REFRESH_ALL_MVIEWS: The
REFRESH_ALL_MVIEWSprocedure refreshes all the materialized views that are registered. This procedure refreshes materialized views only where the data in the base tables has been changed because the materialized view was refreshed. REFRESH_DEPENDENT: TheREFRESH_DEPENDENTview is used to refresh all materialized views that are dependent on a particular table. The same conditions for refreshing materialized views apply to this procedure as do to theREFRESH_ALL_MVIEWSprocedure.
The following SlideShow demonstrates the use of some of these functions:
Managing Materialized Views
The next lesson is the module conclusion.
The next lesson is the module conclusion.