Oracle Database - Methodology for Designing and Building Materialized Views

> Database > Oracle Database

1 - About

There are two methods that can be used to define our materialized views:

Advertising

3 - Methods

3.1 - Manually

3.1.1 - Understand the Queries

The starting point for creating materialized views is to understand the types of joins and aggregations that are used in the users queries which can then be analyzed to identify the set of materialized views necessary to support them. The goal for the database designer is to create as few materialized views as possible that support the widest range of queries.

3.1.2 - Create the Materialized View

Let’s take the example of a query about customer spend by geographic area and month. This query will benefit from a materialized view that pre-computes the join between the SALES_FACT fact table and the TIME_DIM and Comparing Materialized Views and Analytic Workspaces in Oracle Database 11g Page 7 CUSTOMERS_DIM dimension tables and performs the aggregation functions. The SQL for creating the materialized view is shown below:

CREATE MATERIALIZED VIEW state_sales_mv
BUILD IMMEDIATE REFRESH FAST ENABLE QUERY REWRITE AS
SELECT c.cust_state_province, c.country_iso_code, m.month_desc,
MIN(amount_sold) min_amount, MAX(amount_sold) max_amount,
avg(amount_sold) avg_amount, COUNT(amount_sold) cnt_amount,
COUNT(*) count_all
FROM sales_fact s, time_dim m, customers_dim c
WHERE s.month_id = m.month_id AND s.cust_id = c.cust_id
GROUP BY c.cust_state_province,c.country_iso_code, m.month_desc

For large materialized views, a partitioning clause can be added to the statement above, which brings the same benefits and advantages to materialized views as partitioning brings to large tables, namely, improvements to scalability, maintenance and query performance. In addition, using database partitioning also introduces better refresh possibilities using parallel DML.

3.1.3 - Define Dimensions

Some queries can benefit from the definition of SQL dimensions because they will allow more types of query rewrite to occur. SQL Dimensions are Oracle objects that define the hierarchical parent/child relationships and are highly recommended because they provide additional information to query rewrite to enable better rewrite decisions to be made The CREATE DIMENSION statement for our TIME dimension is shown below and illustrates the use of a single hierarchy (CALENDAR) that has three levels and how data value attributes are associated with each of these levels.

CREATE DIMENSION SH.MONTH_DIM
LEVEL MONTH IS (MONTH_STAR.MONTH_ID )
LEVEL QUARTER IS (MONTH_STAR.QUARTER_ID)
LEVEL YEAR IS (MONTH_STAR.YEAR_ID)
HIERARCHY CALENDAR (MONTH CHILD OF QUARTER CHILD OF YEAR)
ATTRIBUTE MONTH DETERMINES (MONTH_STAR.MONTH_DESC,
MONTH_STAR.MONTH_ENDDATE, MONTH_STAR.MONTH_TIMESPAN)
ATTRIBUTE QUARTER DETERMINES (MONTH_STAR.QUARTER_DESC,
MONTH_STAR.QUARTER_ENDDATE, MONTH_STAR.QUARTER_TIMESPAN)
ATTRIBUTE YEAR DETERMINES (MONTH_STAR.YEAR_DESC,
MONTH_STAR.YEAR_ENDDATE, MONTH_STAR.YEAR_TIMESPAN) 

3.1.4 - Build Indexes on the Materialized View

Just as indexes on tables can benefit queries against those tables, so can indexes on materialized views be used to improve the performance of queries that are re-written to utilise the materialized view. The same rules for choosing indexes on tables should be followed when deploying indexes against materialized views:

  • Index columns in the materialized view which are the primary key columns of the base tables (because the materialized view can be joined to the base table for certain types of query rewrite), and,
  • Index columns which are used in the WHERE clauses of user queries.

Remember that the indexes will be maintained as part of the refresh of the materialized view so it is a good idea to constrain the total number of indexes deployed so that the refresh time is not excessive.

Advertising

3.1.5 - Populate the Materialized View

Materialized views provide mechanisms and control over when and how they are populated and refreshed. In our STATE_SALES_MV created in step 2, we have specified the BUILD IMMEDIATE clause which means that the materialized view is populated when the create statement is issued. Alternatively by using BUILD DEFERRED, populating the materialized view can be delayed until an appropriate point during the next warehouse refresh operation. Optimizer statistics will need to be gathered on the materialized view.

3.2 - SQL Access Advisor

On a large and complex database, manually determining the optimal set of materialized views and their indexes that support the users queries can be a time consuming task. The Oracle Database - SQL Access Advisor (Summary Advisor) (in Entreprise Manager), which is part of the Tuning Pack, and has been available since Oracle Database 10g, is available to make this task considerably easier and it is an invaluable tool for this purpose. The Oracle Database - SQL Access Advisor (Summary Advisor) can be found in Advisor Central in Oracle Enterprise Manager or can be invoked from the command line using SQL*Plus by calling one of the procedures in the DBMS_ADVISOR package. Using as input a workload of SQL statements, the advisor takes you step-by-step through the process to recommend the materialized views, their indexes and the materialized view logs and how to implement them. The results of this process are provided as a set of recommendations, which can be implemented either by the Oracle Database - SQL Access Advisor (Summary Advisor) or manually.

Figure below shows the Summary screen resulting from running the Oracle Database - SQL Access Advisor (Summary Advisor) with a user workload containing our eight scenario queries. We can see that the advisor has made some recommendations that it predicts can result in significant performance improvements. From here it is possible to navigate to other screens to examine and modify the generated scripts, for example to change the materialized view names and tablespaces, and then a task can be scheduled for their deployment in the warehouse database.

4 - Reference

db/oracle/methodology_for_designing_and_building_the_materialized_views.txt · Last modified: 2017/09/06 19:29 by gerardnico