Legal | Privacy
Using Oracle's Materialized Views Capabilities

Using Oracle's Materialized Views Capabilities

In this tutorial, you will learn how to take advantage of Oracle's powerful materialized views capabilities.

Approximately 2 hours

Topics

This tutorial covers the following topics:

Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: Because this action loads all screenshots simultaneously, response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over each individual icon in the following steps to load and view only the screenshot associated with that step.

Overview

Prior to Oracle8i, organizations using summaries spent a significant amount of time creating summaries manually, identifying which summaries to create, indexing the summaries, updating them, and advising their users on which ones to use. The introduction of summary management in Oracle8i eases the workload of the database administrator and means the end user no longer has to be aware of the summaries that have been defined. The database administrator creates one or more materialized views, which are the equivalent of a summary. The end user queries the tables and views in the database. The query rewrite mechanism in the Oracle server automatically rewrites the SQL query to use the summary tables. This mechanism reduces response time for returning results from the query. Materialized views within the data warehouse are transparent to the end user or to the database application.

Oracle9i and Oracle Database 10g enriched the functionality even more by providing more sophisticated rewrite and refresh mechanism, as well as a comprehensive advisory and tuning framework. This results in even more optimized materialized views for your environment, providing a performance boost with minimal additional space consumption.

Note: This tutorial is not intended as an introduction to materialized views. It assumes a basic understanding of Oracle’s existing materialized views capabilities. If you want more background information about some of the topics, see the Oracle Data Warehousing Guide.

Back to Topic List

You will use the SALES HISTORY (SH) sample schema to create, modify, and analyze materialized views and rewrite capabilities. The workshop relies on some minor modifications on the SH schema on top of the default installation.

Back to Topic List

Before starting this tutorial, you should have:

1.

Completed the Installing Oracle Database 10g on Windows Using Real Application Clusters (RAC) and Automated Storage Management (ASM) tutorial.

OR

Completed the Installing Oracle Database 10g on Linux tutorial.

 

2.

Download and unzip mv.zip into your working directory (i.e./home/oracle/wkdir).

 

3.

Download and unzip setup_dwh.zip into your working directory (i.e./home/oracle/wkdir).

 

Implement Schema Changes for the Sales History Schema

Some changes on the existing Sales History schema objects are necessary, and some additional system privileges must be granted to the user SH. The SQL file for applying those changes is modifySH_10g.sql.This file is provided to you in the setup_dwh.zip file. To utilize the setup files for the Data Warehousing tutorials, perform the following steps:

1.

Start a SQL*Plus session and Run the modifySH_10g.sql script from your SQL*Plus session.

Move your mouse over this icon to see the image

 

Back to Topic List

Enable Query Rewrite

To enable query rewrite, the following conditions must be met:

Individual materialized views must have the ENABLE QUERY REWRITE clause.
The QUERY_REWRITE_ENABLED initialization parameter must be set to TRUE. Alternatively you can set this parameter to FORCE; this will deactivate any costing evaluation of a rewritten plan and will rewrite a query whenever possible.
The rewrite integrity mode and the status of a particular materialized view must match to enable the rewrite with this particular materialized view.

You first need to ensure that you have the basic initialization settings for your database instance. To do this, perform the following step:

1.

From a SQL*Plus session logged on to the SH schema, run set_rewrite_session.sql, or copy the following SQL statement into your SQL*Plus session:

@set_rewrite_session

ALTER SESSION SET query_rewrite_integrity=TRUSTED;
ALTER SESSION SET query_rewrite_enabled=FORCE;
show parameters query

Move your mouse over this icon to see the image

You are enabling query rewrite and are using the so-called "trusted" mode. This is the most commonly used integrity level. In trusted mode, the optimizer trusts that the data in the materialized views is fresh and the relationships declared in dimensions and RELY constraints are correct. In this mode, the optimizer will also use prebuilt materialized views or materialized views based on views, and it will use relationships that are not enforced as well as those that are enforced. In this mode, the optimizer also trusts declared but not ENABLED VALIDATED primary/unique key constraints and data relationships specified using dimensions.

Please consult the Oracle Data Warehousing Guide for any further details regarding the levels of query_rewrite_integrity and query_rewrite_enabled.


Back to Topic List

Analyzing the Materialized View Refresh and Rewrite Capabilities

Before Oracle9i, the analysis of existing or potential materialized views was done manually, using Oracle’s documentation and the experience and knowledge of the developer. Sometimes, this ended in a frustrating or never-ending process to set up the business environment that a customer was looking for. It was not a matter of missing functionality, it was basically a matter of overlooking the last little detail in the setup.

Oracle9i enhances this gap, and delivers procedures with which the developer can analyze existing as well as potential, nonexisting environments. This enables you to fully leverage all of Oracle’s powerful capabilities of materialized views. You will learn about Oracle Database 10g in a few steps.

To analyzing the refresh and rewrite capabilities of a potential materialized view, you perform the following steps:

1.

From a SQL*Plus session logged on to the SH schema, run create_mv1.sql, or copy the following SQL statement into your SQL*Plus session:

@create_mv1.sql

DROP MATERIALIZED VIEW cust_sales_mv ;

CREATE MATERIALIZED VIEW cust_sales_mv
   BUILD IMMEDIATE
   REFRESH FAST ON COMMIT
   ENABLE QUERY REWRITE
   AS
   SELECT c.cust_id,
          SUM(amount_sold) AS dollar_sales
   FROM   sales s, customers c
   WHERE  s.cust_id= c.cust_id
   GROUP  BY c.cust_id;

Move your mouse over this icon to see the image

This statement fails and raises the following error:

ORA-23413: table "SH"."CUSTOMERS" does not have 
           a materialized view log.

Before Oracle9i, you would have tried to orrect the error and tried to create the materialized view again. This was an iterative and time consuming process.


2.

Even if the statement succeeded, you could not tell about the details of its fast refresh capabilities. But using the new dbms_mview.explain_mview package introduced with Oracle9i gives you more insight into the capabilities of the potential materialized view, so that you can address all issues before its creation.

@explain_mv1.sql

truncate table mv_capabilities_table;

exec dbms_mview.explain_mview( -
   'SELECT   c.cust_id, SUM(amount_sold) AS dollar_sales -
    FROM     sales s, customers c -
    WHERE    s.cust_id= c.cust_id -
    GROUP BY c.cust_id');

set serveroutput on

begin
   for crec in ( select capability_name, possible, 
                        related_text, msgtxt
                 from   mv_capabilities_table order by 1) loop
    dbms_output.put_line(crec.capability_name ||': '||crec.possible);
    dbms_output.put_line(crec.related_text||': '||crec.msgtxt);
   end loop;
end;
/

Move your mouse over this icon to see the image

You can see in the output that the system indicates an additional missing materialized view log on the sales table. Rather than playing trial-and-error with the system, it is recommended that you always analyze potential materialized views before their creation, using the dbms_mview.explain_mview package shown above.

Besides the missing materialized view logs on customers and sales, the system also detects that you need to add additional aggregation functions to the materialized view to fully enable fast refresh capabilities for any kind of DML operation.

The aggregation functions that the system recommends are:

  • COUNT(*)
  • COUNT(amount_sold)

The output of the dbms_mview.explain_mview package is shown here. You can see that it not only covers the refresh capabilities of a materialized view, but also the rewrite and Partition-Change-Tracking (PCT) capabilities of the materialized view. These capabilities are discussed later.

Move your mouse over this icon to see the image

 

3.

To correct this, first create the materialized view logs identified above.

@create_mv_logs1.sql

DROP MATERIALIZED VIEW LOG ON sales;

CREATE MATERIALIZED VIEW LOG ON sales
WITH ROWID, SEQUENCE
(prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold)
INCLUDING NEW VALUES ;
DROP MATERIALIZED VIEW LOG ON customers;
CREATE MATERIALIZED VIEW LOG ON customers
WITH ROWID, SEQUENCE
(cust_id,cust_first_name,cust_last_name,cust_gender,cust_year_of_birth
,cust_marital_status,cust_street_address,cust_postal_code,cust_city
,cust_state_province,country_id,cust_main_phone_number,cust_income_level
,cust_credit_limit,cust_email)
INCLUDING NEW VALUES;

Move your mouse over this icon to see the image

This one is used later.

DROP MATERIALIZED VIEW LOG ON products;
CREATE MATERIALIZED VIEW LOG ON products
WITH ROWID, SEQUENCE
(prod_id,prod_name,prod_desc,prod_subcategory,prod_subcategory_desc
,prod_category,prod_category_desc,prod_weight_class,prod_unit_of_measure
,prod_pack_size,supplier_id,prod_status,prod_list_price,prod_min_price)
INCLUDING NEW VALUES;
4.

Check the capabilities of the potential materialized view again.

@explain_mv1a.sql

TRUNCATE TABLE mv_capabilities_table;

EXEC dbms_mview.explain_mview( -
   'SELECT   c.cust_id, SUM(amount_sold) AS dollar_sales, -
             COUNT(amount_sold) AS cnt_dollars, COUNT(*)  -
    FROM     sales s, customers c -
    WHERE    s.cust_id= c.cust_id -
    GROUP BY c.cust_id');

set serveroutput on

BEGIN
   for crec in (select capability_name, possible, 
                       related_text, msgtxt
                from   mv_capabilities_table order by 1) loop
    dbms_output.put_line(crec.capability_name ||': '||crec.possible);
    dbms_output.put_line(crec.related_text||': '||crec.msgtxt);
   end loop;
END;
/

Move your mouse over this icon to see the image

The fast refresh capabilities of this potential materialized view have changed as expected.

 

5.

Now create the materialized view.

@create_mv1b.sql

DROP MATERIALIZED VIEW cust_sales_mv ;

CREATE MATERIALIZED VIEW cust_sales_mv
   BUILD IMMEDIATE
   REFRESH FAST ON COMMIT
   ENABLE QUERY REWRITE
   AS
   SELECT c.cust_id,
          SUM(amount_sold) AS dollar_sales,
          COUNT(amount_sold) AS cnt_dollars,
          COUNT(*) AS cnt
   FROM   sales s, customers c
   WHERE  s.cust_id= c.cust_id
   GROUP BY c.cust_id;

Move your mouse over this icon to see the image

 

6.

The explain_mview procedure also works with existing materialized views.

@explain_mv1b.sql

TRUNCATE TABLE mv_capabilities_table;

EXEC dbms_mview.explain_mview('cust_sales_mv');

set serveroutput on

begin
   for crec in (select capability_name, possible, 
                       related_text, msgtxt
                from   mv_capabilities_table order by 1) loop
     dbms_output.put_line(crec.capability_name ||': '||crec.possible);
     dbms_output.put_line(crec.related_text||': '||crec.msgtxt);
    end loop;
end;
/

Move your mouse over this icon to see the image


Starting with the potential SQL statement for the materialized view, you were able to analyze its capabilities completely without creating it.

 

Back to Topic List

Query Rewrite Capabilities of Oracle

The optimizer uses a number of different methods to rewrite a query. The first, most important step is to determine if all or parts of the results requested by the query can be obtained from the precomputed results stored in a materialized view.

The simplest case occurs when the result stored in a materialized view exactly matches what is requested by a query. The Oracle optimizer makes this type of determination by comparing the text of the query with the text of the materialized view definition. This method is most straightforward but the number of queries eligible for this type of query rewrite will be minimal.

When the text comparison test fails, the Oracle optimizer performs a series of generalized checks based on the joins, selections, grouping, aggregates, and column data fetched. This is accomplished by individually comparing various clauses (SELECT, FROM, WHERE, HAVING, or GROUP BY) of a query with those of a materialized view.

Back to Topic List

Using Partial Text Match Rewrite

The most simple rewrite mechanism is the text match rewrite. In full text match, the entire text of a query is compared against the entire text of a materialized view definition (that is, the entire SELECT expression), ignoring the white space during text comparison. When full text match fails, the optimizer then attempts a partial text match. In this method, the text starting with the FROM clause of a query is compared against the text starting with the FROM clause of a materialized view definition.

1.

From a SQL*Plus session logged on to the SH schema, run explain_rewrite1.sql, or copy the following SQL statement into your SQL*Plus session:

@explain_rewrite1.sql

Rem REWRITE
DELETE FROM plan_table;
COMMIT;

EXPLAIN PLAN FOR
   SELECT c.cust_id,
          SUM(amount_sold) AS dollar_sales
   FROM   sales s, customers c
   WHERE  s.cust_id= c.cust_id
   GROUP BY c.cust_id;

set linesize 132
set pagesize 999
select * from table(dbms_xplan.display);

Move your mouse over this icon to see the image

The plan below shows that the query is rewritten with the cust_sales_mv materialized view, using the partial text match rewrite mechanism; starting with the FROM clause, the SQL statement and the materialized view are identical.

While the query is rewritten, the access plan for a materialized view is investigated like the access of a normal table, so that any existing indexes might be used.


2.

Next execute the query.

@do_rewrite1.sql

set timing on

SELECT c.cust_id,
       SUM(amount_sold) AS dollar_sales
FROM   sales s, customers c
WHERE  s.cust_id= c.cust_id
GROUP BY c.cust_id;

Move your mouse over this icon to see the image

Executing the query delivers a result very quickly, because it only has to access the already joined and aggregated information in cust_sales_mv.

 

3.

The plan for the nonwritten statement can be enforced by using the NOREWRITE hint. This gives you the control down to statement level, whether a query is rewritten or not.

@explain_norewrite.sql

DELETE FROM plan_table;
COMMIT;

EXPLAIN PLAN FOR
   SELECT   /*+ norewrite */
            c.cust_id,
            SUM(amount_sold) AS dollar_sales
   FROM     sales s, customers c
   WHERE    s.cust_id= c.cust_id
   GROUP BY c.cust_id;

set linesize 132
set pagesize 999
select * from table(dbms_xplan.display);

Move your mouse over this icon to see the image

Without Oracle’s query rewrite capabilities, you would have to do the full scan from SALES and the join with customers.

Note: This query will not be run due to time constraints.

 

Back to Topic

Creating a Materialized View on a Prebuilt Table:

It is not uncommon in a data warehouse to have already created summary or aggregation tables, and you might not want to repeat this work by building a new materialized view. Although this solution provides the performance benefits of materialized views, it does not:

Because of these limitations, and because existing materialized views can be extremely large and expensive to rebuild, the Oracle database provides you with the capability to register those already existing summary tables as materialized views, thus circumventing all the disadvantages mentioned above. You can register a user-defined materialized view with the CREATE MATERIALIZED VIEW ... ON PREBUILT TABLE statement. Once registered, the materialized view can be used for query rewrites, maintained by one of the refresh methods, or both. This functionality was available beginning with Oracl8i.

Oracle implemented this capability for its existing customer base to provide a save migration path and as a protection of investment. Migrating an existing data warehousing environment with "hand-made" summary tables and refresh procedures can take advantage of the new rewrite capabilities with a single DDL command, without affecting any existing code.

MyCompany recently migrated from Oracle8.0 to Oracle9i, and does have such manually created aggregation tables, as do more than 90% of all existing data warehousing systems. To register the existing cust_id_sales_aggr table as a materialized view, you perform the following steps:

1.

From a SQL*Plus session logged on to the SH schema, run create_mv2.sql, or copy the following SQL statement into your SQL*Plus session:

@create_mv2.sql

DROP MATERIALIZED VIEW cust_sales_aggr ;

CREATE MATERIALIZED VIEW cust_sales_aggr
ON PREBUILT TABLE
REFRESH FORCE
ENABLE QUERY REWRITE
AS
SELECT c.cust_id,
c.cust_last_name,
c.cust_first_name,
SUM(amount_sold) AS dollar_sales,
COUNT(amount_sold) AS cnt_dollars,
COUNT(*) AS cnt
FROM sales s, customers c
WHERE s.cust_id= c.cust_id
GROUP BY c.cust_id,
c.cust_last_name,
c.cust_first_name;

Move your mouse over this icon to see the image

This statement is fairly fast. It doesn’t touch any data at all; it simply creates the meta information of a materialized view, which tables and columns are involved, which joins, and which aggregations.

Using materialized views on prebuilt tables is not possible with the highest level of data integrity for query rewrite (query_rewrite_integrity=ENFORCED), because the system "trusts" you as the creator with respect to the data integrity. As soon as you are going to leverage Oracle’s refresh capabilities, too, the system knows about the integrity of the data. However, the first refresh will be a complete refresh in any case.

 

Back to Topic

Using a Simple Join Back Rewrite

When the text comparison test fails, the Oracle optimizer performs a series of generalized checks based on the joins, selections, grouping, aggregates, and column data fetched. This is accomplished by individually comparing various clauses (SELECT, FROM, WHERE, HAVING, or GROUP BY) of a query with those of a materialized view. The query does not always have to match exactly for query rewrite to occur. For example, suppose your materialized view is grouped by cust_id but your query groups on cust_last_name. Query rewrite is still possible using what is known as a join back method.

The following is a simple example for a join back rewrite. The cust_sales_mv materialized view stores the cust_id join column for joining with customers the same way that the sales and customers tables determine the value of cust_credit_limit.

1.

From a SQL*Plus session logged on to the SH schema, run explain_rewrite2.sql, or copy the following SQL statement into your SQL*Plus session:

@explain_rewrite2.sql

DELETE FROM plan_table;
COMMIT;
EXPLAIN PLAN FOR
SELECT c.cust_last_name,
c.cust_credit_limit,
SUM(amount_sold) AS dollar_sales
FROM sales s, customers c
WHERE s.cust_id= c.cust_id
GROUP BY c.cust_last_name,
c.cust_credit_limit
ORDER BY 1;
set linesize 140
SELECT * FROM TABLE(dbms_xplan.display);

Move your mouse over this icon to see the image

You can see in the plan that Oracle uses the cust_sales_mv materialized view and joins it back to the customers table using the cust_id column, which is part of the materialized view and represents the primary key-foreign key relationship between the sales and customers tables. Furthermore, the requested attribute cust_last_name in the query is a determined attribute from cust_id, so that the system knows that no additional aggregation on the dimension site must take place and it only has to join back the materialized view to the customers table. The information about hierarchies and determined attributes is part of customers_dim, the Oracle dimension object for the customers dimension. The important part of the dimension definition customers_dim is shown below:

LEVEL customer IS (customers.cust_id)

ATTRIBUTE customer DETERMINES
(cust_first_name, cust_last_name, cust_credit_limit, cust_gender, ...

Note: For more information about DIMENSION objects, see the Oracle Data Warehousing Guide.


2.

Execute the query. Note that we will only count the result and do not actually spool out all
of the returned records.

@do_rewrite2.sql

SELECT COUNT(*) 
FROM (SELECT c.cust_last_name, 
c.cust_credit_limit,
SUM(amount_sold) AS dollar_sales
FROM sales s, customers c
WHERE s.cust_id= c.cust_id
GROUP BY c.cust_last_name,
c.cust_credit_limit
ORDER BY 1;

Move your mouse over this icon to see the image

 

3.

The plan for the non-rewritten query can be shown with the following statement:

@explain_norewrite2.sql

DELETE FROM  plan_table;
COMMIT;
EXPLAIN PLAN FOR
SELECT /*+ NOREWRITE */ c.cust_last_name,
c.cust_credit_limit,
SUM(amount_sold) AS dollar_sales
FROM sales s, customers c
WHERE s.cust_id= c.cust_id
GROUP BY c.cust_last_name,
c.cust_credit_limit
ORDER BY 1;
set linesize 120
select * from table(dbms_xplan.display);

Move your mouse over this icon to see the image

Without query rewrite, you have to process the join between your complete sales fact table and the customers dimension table.

 

Back to Topic

Analyzing the Rewrite Process

Another enhancement in Oracle9i was the dbms_mview.explain_rewrite procedure, which gives you detailed information about Oracle's investigation of finding a possible candidate for query rewrite in the system. To analyze the previously executed query and get more insight into the rewrite process, perform the following step:

1.

From a SQL*Plus session logged on to the SH schema, run analyze_rewrite2.sql, or copy the following SQL statement into your SQL*Plus session: truncate table rewrite_table;

@analyze_rewrite2.sql

TRUNCATE TABLE rewrite_table;

DECLARE
querytxt VARCHAR2(1500) := 'select c.cust_last_name, c.cust_credit_limit,'
||
' SUM(amount_sold) AS dollar_sales ' ||
'FROM sales s, customers c ' ||
'WHERE s.cust_id= c.cust_id ' ||
'GROUP BY c.cust_last_name, c.cust_credit_limit';
BEGIN
dbms_mview.Explain_Rewrite(querytxt, NULL, 'ID1');
END;
/ SELECT message FROM rewrite_table ORDER BY sequence DESC;

Move your mouse over this icon to see the image

Note that the materialized view used above was not the only one possible; the cust_sales_aggr_id materialized view based on the prebuilt table would also have been eligible for rewrite. In such a case, the optimizer makes a cost-based decision.

 

Back to Topic

Rewrite Using Join Back and Rollup

Besides a simple join back of materialized views, requesting the same aggregation level of information, materialized views can also be aggregated to a higher level – the so-called ROLLUP operation. Consider the following query:

1.

From a SQL*Plus session logged on to the SH schema, run explain_rewrite3.sql, or copy the following SQL statement into your SQL*Plus session:

@explain_rewrite3.sql

DELETE FROM plan_table;
COMMIT;

EXPLAIN PLAN FOR
   SELECT   c.cust_state_province,
            SUM(amount_sold) AS dollar_sales
   FROM     sales s, customers c
   WHERE    s.cust_id= c.cust_id
   GROUP BY c.cust_state_province;

set linesize 132
set pagesize 999
select * from table(dbms_xplan.display);

Move your mouse over this icon to see the image

You can see in the plan that Oracle uses the cust_sales_mv materialized view and joins it back to the customers table, using the cust_id column, which is part of the materialized view and represents the primary key-foreign key relationship between the sales and customers tables.

However, the existence of a possible join key column does not necessarily represent all the
information, the query rewrite mechanism needs for guaranteeing data integrity.

Examine the customers_dim dimension definition:

LEVEL customer  IS (customers.cust_id)
LEVEL city      IS (customers.cust_city)
LEVEL state     IS (customers.cust_state_province)
. . .
HIERARCHY geog_rollup (
      customer     CHILD OF
      city         CHILD OF
      state        CHILD OF
. . .

The requested cust_state_province attribute represents the so-called level state, a higher aggregation level than customer, represented by cust_id. Levels and hierarchies represent a declarative way for representing a 1:n relationship inside one table. In this case, it expresses the validity of aggregating all customer information to the level state without violating data integrity. For every distinct customer value, you will get one and only one state value.


2.

Now issue the query. It will run fairly fast.

@do_rewrite3.sql

SELECT COUNT(*) 
FROM (SELECT c.cust_state_province, SUM(amount_sold) AS dollar_sales FROM sales s, customers c WHERE s.cust_id= c.cust_id GROUP BY c.cust_state_province);

Move your mouse over this icon to see the image

 

Back to Topic

Rewrite Using a Complex Join Back and Rollup

The following example demonstrates the power and flexibility of Oracle's query rewrite capabilities. The following example not only does a join back, it uses the information in the customers_dim dimension to join back over two tables in a snowflake schema. To do this, you perform the following steps:

1.

From a SQL*Plus session logged on to the SH schema, run explain_rewrite4.sql, or copy the following SQL statement into your SQL*Plus session:

@explain_rewrite4.sql

DELETE FROM plan_table;
COMMIT;

EXPLAIN PLAN FOR
   SELECT   co.country_name, 
            c.cust_state_province, SUM(amount_sold) AS dollar_sales 
   FROM     sales s, customers c, countries co
   WHERE    s.cust_id= c.cust_id 
     AND    c.country_id = co.country_id 
   GROUP BY co.country_name, c.cust_state_province 
   ORDER BY 1,2;

set linesize 132
set pagesize 999
select * from table(dbms_xplan.display);

Move your mouse over this icon to see the image

The optimizer rewrites the query to take advantage of the cust_sales_mv materialized view and joins it back to customers and customers to countries to satisfy the query. In the same way that you enforced a query on the statement level not to be rewritten you can enforce the use of a specific materialized view.

Various data integrity checks must take place to guarantee the validity of using this materialized view. Besides the check for losslessness and nonduplicating joins, the evaluation of the dimension information plays an important role for the rewrite process.

Examine what the optimizer uses to rewrite this query. The following is an excerpt of the dimension definition of customers_dim. It shows the important parts for this query:

LEVEL customer    IS (customers.cust_id)
LEVEL . . .
LEVEL state       IS (customers.cust_state_province)
LEVEL country     IS (countries.country_id)
. . .
HIERARCHY geog_rollup (
       customer      CHILD OF
       . . .
       state         CHILD OF
       country       CHILD OF
. . .
JOIN KEY (customers.country_id) REFERENCES country
)
. . .
ATTRIBUTE country DETERMINES (countries.country_name)

The Oracle database has to determine whether or not it can derive all requested attributes based on the information that is stored in the materialized view. You are requesting countries.country_name and customers.cust_state_province in your query; the materialized view contains only cust_id as information.

Based on the information in the customers_dim dimension, the Oracle database determines the following:

  • customers.cust_state_province can be determined by cust_id in the materialized view. It represents a higher aggregation level in the dimension than level customers.
  • countries.country_id can also be determined by cust_id in the materialized view. countries.country_id describes a higher aggregation level than customers.
  • countries.country_name is a determined attribute of the hierarchical level country and can therefore be determined based on countries.country_id.
  • The customers_dim dimension describes a hierarchical dependency across two tables. The join condition is part of the dimension information.

The Oracle database uses all of this information to join the materialized view not only with customers, but customers also with countries to get the result for the query and to guarantee that the query result is correct.


2.

The rewritten SQL statement looks like the following:

@rewrite_sel.sql

SELECT COUNT(*) 
FROM  (SELECT co.country_name, 
c.cust_state_province,
SUM(mv.dollar_sales) AS dollar_sales
FROM cust_sales_mv mv,
(SELECT DISTINCT cust_id, cust_state_province, country_id FROM customers) c,
countries co
WHERE mv.cust_id = c.cust_id
AND c.country_id = co.country_id
GROUP BY co.country_name, c.cust_state_province
ORDER BY 1,2);
3.

Now execute the query.

@do_rewrite4.sql

SELECT   c.cust_state_province,
         SUM(amount_sold) AS dollar_sales
FROM     sales s, customers c
WHERE    s.cust_id= c.cust_id
GROUP BY c.cust_state_province;

Move your mouse over this icon to see the image

 

4.

To get the plan without query rewrite, you can run explain_norewrite4.sql or copy the following SQL statement into your SQL*Plus session.

@explain_norewrite4.sql

DELETE FROM plan_table;
COMMIT;

EXPLAIN PLAN FOR
   SELECT /*+ norewrite */
          co.country_name,
          c.cust_state_province,
          SUM(amount_sold) AS dollar_sales
   FROM   sales s, customers c, countries co
   WHERE  s.cust_id= c.cust_id
   AND    c.country_id = co.country_id
   GROUP BY co.country_name, c.cust_state_province;

set linesize 132
set pagesize 999
select * from table(dbms_xplan.display);

Move your mouse over this icon to see the image

 

Back to Topic

Creating a Materialized View on a Subset of Data

Very often only a subset of the information in a large fact table might be considered for more analysis. To take advantage of materialized views in such a situation, you had to create a materialized view containing all the information for the fact table. Choosing to incorporate a predicate condition in the materialized view definition allowed TEXTMATCH only rewrite capabilities.

To create a materialized view on a subset of data and compare its creation time and its size with a materialized view containing the same joins and aggregations and the complete data set without any predicates, you perform the following steps:

1.

From a SQL*Plus session logged on to the SH schema, run create_mv3.sql, or copy the following SQL statement into your SQL*Plus session:

@create_mv3.sql

DROP MATERIALIZED VIEW some_cust_sales_mv;

CREATE MATERIALIZED VIEW some_cust_sales_mv
   BUILD IMMEDIATE
   REFRESH COMPLETE
   ENABLE QUERY REWRITE
   AS
   SELECT   c.cust_id, sum(s.amount_sold) AS dollars, p.prod_id,
            sum(s.quantity_sold) as quantity
   FROM     sales s , customers c, products p
   WHERE    c.cust_id = s.cust_id
   AND      s.prod_id = p.prod_id
   AND      c.cust_state_province IN 
               ('Dublin','Galway','Hamburg','Istanbul')
   GROUP BY c.cust_id, p.prod_id;

Move your mouse over this icon to see the image

Make note of how long it takes to create the materialized view.

 

2.

Now create the same materialized view without a predicate to restrict the result set.

@create_mv3b.sql

DROP MATERIALIZED VIEW all_cust_sales_mv;

CREATE MATERIALIZED VIEW all_cust_sales_mv
   BUILD IMMEDIATE
   REFRESH COMPLETE  
   ENABLE QUERY REWRITE
   AS
   SELECT c.cust_id, sum(s.amount_sold) AS dollars, p.prod_id,
          sum(s.quantity_sold) as quantity
   FROM   sales s , customers c, products p
   WHERE  c.cust_id = s.cust_id
   AND    s.prod_id = p.prod_id
   GROUP BY c.cust_id, p.prod_id;

Move your mouse over this icon to see the image

It takes longer to create the materialized view, because all data must be touched, joined, and aggregated.

 

Back to Topic

Estimating the Size of Materialized Views

Besides the improvement in creation time, you will also use less space in the database for storing the materialized views.

You can query the data dictionary to get information about the size of the materialized views. Unfortunately, this can be done only when a materialized view is already created. Ideally, you want this informatio before the creation of a materialized view, especially in very large environments. With the dbms_olap.estimate_summary_size package, you can get this information without the necessity to create the materialized view itself.

To use the Oracle database to estimate the size of the two materialized views already created and compare it with their real size, perform the following steps:

1.

From a SQL*Plus session logged on to the SH schema, run estimate_mv_size1.sql, or copy the following SQL statement into your SQL*Plus session. This will give you a size estimate for the materialized view containing all data.

@estimate_mv_size1.sql

set serveroutput on;

DECLARE
no_of_rows NUMBER;
mv_size NUMBER;
BEGIN
no_of_rows :=555;
mv_size :=5555;
dbms_olap.estimate_summary_size ('MV 1',
'SELECT c.cust_id, sum(s.amount_sold) AS dollars, p.prod_id,
sum(s.quantity_sold) as quantity
FROM sales s , customers c, products p
WHERE c.cust_id = s.cust_id
AND s.prod_id = p.prod_id
GROUP BY c.cust_id, p.prod_id' , no_of_rows, mv_size );
DBMS_OUTPUT.put_line ( '');
DBMS_OUTPUT.put_line ( 'Complete MV');
DBMS_OUTPUT.put_line ( 'No of Rows: ' || no_of_rows );
DBMS_OUTPUT.put_line ( 'Size of Materialized view (MB): ' ||
round(mv_size/(1024*1024),2) );
DBMS_OUTPUT.put_line ( '');
END;
/

Move your mouse over this icon to see the image

Make note of how long it takes to create the materialized view.

 

2.

Determine the size of the materialized view containing the subset of data:

@estimate_mv_size2.sql

DECLARE
no_of_rows NUMBER;
mv_size NUMBER;
BEGIN
no_of_rows :=555;
mv_size :=5555;
dbms_olap.estimate_summary_size ('MV 2',
'SELECT c.cust_id, sum(s.amount_sold) AS dollars, p.prod_id,
sum(s.quantity_sold) as quantity
FROM sales s , customers c, products p
WHERE c.cust_id = s.cust_id
AND s.prod_id = p.prod_id
AND c.cust_state_province IN (''Dublin'',''Galway'',''Hamburg'',''Istanbul'')
GROUP BY c.cust_id, p.prod_id' , no_of_rows, mv_size );
DBMS_OUTPUT.put_line ( 'Partial MV');
DBMS_OUTPUT.put_line ( 'No of Rows: ' || no_of_rows );
DBMS_OUTPUT.put_line ( 'Size of Materialized view (MB): ' ||
round(mv_size/(1024*1024),2) );
DBMS_OUTPUT.put_line ( '');
END;
/

Move your mouse over this icon to see the image

 

3.

Now look in the data dictionary to get the actual sizes of the two new materialized views.

@comp_mv_size.sql

COLUMN "MV name" format a20

SELECT   substr(segment_name,1,30) "MV name", bytes/1024*1024 MB
FROM     user_segments 
WHERE    segment_name in ('SOME_CUST_SALES_MV','ALL_CUST_SALES_MV')
ORDER BY segment_name ;

Move your mouse over this icon to see the image

The materialized view containing only the subset of data is about 9 times smaller than the view containing all data. Especially in very large environments, this provides a tremendous benefit and simplifies the use of materialized views for "special analysis purposes," touching only parts of the information in your data warehouse.

 

Back to Topic

Analyzing the Rewrite Process

To examine the decision process of the query rewrite mechanism for a query that could be satisfied by the materialized view containing the subset of data only, perform the following step:

1.

From a SQL*Plus session logged on to the SH schema, run analyze_subset_rewrite.sql, or copy the following SQL statement into your SQL*Plus session:

@analyze_subset_rewrite.sql

DELETE FROM plan_table;
COMMIT;

DECLARE
   querytxt VARCHAR2(1500) := -
             'SELECT c.cust_id, ' ||
                    'sum(s.amount_sold) AS dollars,p.prod_id, ' ||
                    'sum(s.quantity_sold) as quantity ' ||
             'FROM   sales s , customers c, products p ' ||
             'WHERE  c.cust_id = s.cust_id ' ||
             'AND    s.prod_id = p.prod_id ' ||
             'AND    c.cust_state_province IN '|| 
                      ' (''Dublin'',''Galway'',''Hamburg'') ' ||
             'GROUP BY c.cust_id, p.prod_id';
BEGIN
   dbms_mview.Explain_Rewrite(querytxt, NULL, 'ID1');
END;
/

SELECT message 
FROM   rewrite_table 
ORDER BY sequence desc;

Move your mouse over this icon to see the image

You can see that the subset materialized view is chosen over the one containing all the data, because of its lower cost.

 

Back to Topic

Rewriting with Join Back and Aggregation to All

The materialized view containing the subset of data can be used for query rewrite like any other materialized view. Those materialized view only have to pass the data containment check for being eligible for rewrite.

The following is an example using the subset materialized view with a query, where a join back and an aggregation to all becomes necessary.

1.

From a SQL*Plus session logged on to the SH schema, run explain_subset_rewrite2.sql, or copy the following SQL statement into your SQL*Plus session:

@explain_subset_rewrite2.sql

DELETE FROM plan_table;
COMMIT;

EXPLAIN PLAN FOR
   SELECT   c.cust_last_name, sum(s.amount_sold) AS dollars,
            sum(s.quantity_sold) as quantity
   FROM     sales s , customers c, products p
   WHERE    c.cust_id = s.cust_id
     AND    s.prod_id = p.prod_id
     AND    c.cust_state_province IN ('Dublin','Galway')
   GROUP BY c.cust_last_name;

set linesize 132
set pagesize 999

SELECT * FROM TABLE(dbms_xplan.display);

Move your mouse over this icon to see the image

 

2.

Run the query also:

@run_subset_rewrite2.sql

SELECT COUNT(*) FROM (SELECT   c.cust_last_name, sum(s.amount_sold) AS dollars,
                        sum(s.quantity_sold) as quantity
                      FROM     sales s , customers c, products p
                      WHERE    c.cust_id = s.cust_id
                        AND    s.prod_id = p.prod_id
                        AND    c.cust_state_province IN ('Dublin','Galway')
                      GROUP BY c.cust_last_name);

Move your mouse over this icon to see the image

 

Back to Topic

Back to Topic List

Enhanced Rewrite Capabilities with the GROUP BY Extensions

Oracle9i introduced extensions to the GROUP BY clause in the form of GROUPING SETS, ROLLUP, and their concatenation. These extensions enable you to selectively specify the groupings of interest in the GROUP BY clause of the query.

In order for a materialized view with an extended GROUP BY to be used for rewrite, it must satisfy two additional conditions:

A materialized view with an extended GROUP BY contains multiple groupings. Oracle finds the grouping with the lowest cost from which the query can be computed and uses that for rewrite.

1.

Create a materialized view containing an extended GROUP BY expressions:

From a SQL*Plus session logged on to the SH schema, run the following SQL statement:

@create_gby_mv.sql

DROP MATERIALIZED VIEW sales_cube_mv;

CREATE MATERIALIZED VIEW sales_cube_mv
   ENABLE QUERY REWRITE
   AS
   SELECT calendar_year year, calendar_quarter_desc quarter, 
          calendar_month_desc month, cust_state_province state, 
          cust_city city,
          GROUPING_ID (calendar_year,calendar_quarter_desc,
                       calendar_month_desc,
                       cust_state_province,cust_city) gid,
          GROUPING(calendar_year) grp_y,
          GROUPING(calendar_quarter_desc) grp_q,
          GROUPING(calendar_month_desc) grp_m,
          GROUPING(cust_state_province) grp_s,
          GROUPING(cust_city) grp_c,
          SUM(amount_sold) sum_sales
   FROM sales s, times t, customers c
   WHERE s.time_id=t.time_id AND s.cust_id=c.cust_id
   GROUP BY GROUPING SETS ((calendar_year, cust_city),
                           (calendar_year, cust_city, 
                            cust_state_province),
                           (calendar_year, calendar_quarter_desc, 
                            calendar_month_desc,cust_city));

exec dbms_stats.gather_table_stats('SH','sales_cube_mv');

DROP MATERIALIZED VIEW sales_gby_mv;

CREATE MATERIALIZED VIEW sales_gby_mv
   ENABLE QUERY REWRITE
   AS
   SELECT   calendar_year year, cust_state_province state,
            SUM(amount_sold) sum_sales
   FROM     sales s, times t, customers c
   WHERE    s.time_id=t.time_id AND s.cust_id=c.cust_id
   GROUP BY (calendar_year, cust_state_province);

exec dbms_stats.gather_table_stats('SH','sales_gby_mv');

Move your mouse over this icon to see the image

The following query contains exactly the same GROUPING SETS than the created materialized view. You see that the query is rewritten as-is against the materialized view.

 

2.

From a SQL*Plus session logged on to the SH schema run the following SQL statement:

@rewrite_gby1.sql

DELETE FROM plan_table;
COMMIT;

EXPLAIN PLAN FOR
  SELECT calendar_year year, calendar_quarter_desc quarter,
         calendar_month_desc month,
         cust_state_province state,
         SUM(amount_sold) sum_sales
  FROM   sales s, times t, customers c
  WHERE  s.time_id=t.time_id
    AND  s.cust_id=c.cust_id
  GROUP BY GROUPING SETS ((calendar_year, cust_city),
           (calendar_year, cust_city, cust_state_province),
           (calendar_year, calendar_quarter_desc,
            calendar_month_desc, cust_city));

PROMPT new output, using table function
set linesize 132
set pagesize 999
SELECT * FROM TABLE(dbms_xplan.display);

Move your mouse over this icon to see the image

When both materialized view and the query contain GROUP BY extensions, Oracle uses two strategies for rewrite: grouping match and UNION ALL rewrite. First, Oracle tries grouping match. The groupings in the query are matched against groupings in the materialized view and if all are matched with no rollup, Oracle selects them from the materialized view. The grouping match takes place in this example; a full table scan of the materialized view, without any filter conditions, satisfies our query.The following query contains different GROUPING SETS than the materialized view definition. Furthermore, it select a column from table customers, country_id, that is not part of the materialized view.

 

3.

From a SQL*Plus session logged on to the SH schema run the following SQL statement:

@rewrite_gby2.sql

PROMPT full access AND joinback to dimension customers
PROMPT decompose of grouping levels into UNION ALL

DELETE FROM plan_table;
COMMIT;

EXPLAIN PLAN FOR
  SELECT   calendar_year year, calendar_quarter_desc quarter,
           cust_state_province state, country_id,
           SUM(amount_sold) sum_sales
  FROM     sales s, times t, customers c
  WHERE    s.time_id=t.time_id
    AND    s.cust_id=c.cust_id
  GROUP BY GROUPING SETS ((calendar_year, country_id),
                          (calendar_year, cust_state_province),
                          (calendar_year, calendar_quarter_desc,
                           cust_state_province));

PROMPT new output, using table function
set linesize 132
set pagesize 999
SELECT * FROM TABLE(dbms_xplan.display);

Move your mouse over this icon to see the image

In this case the grouping match fails. Oracle tries a general rewrite mechanism called UNION ALL rewrite. Oracle first represents the query with the extended GROUP BY clause as an equivalent UNION ALL query. Every grouping of the original query is placed in a separate UNION ALL branch. The branch will have a simple GROUP BY clause.

To satisfy the above shown query with the existing materialized view, Oracle has rewrite the GROUPING SETS into three simple GROUP BY expressions, combined with a UNION ALL operator. It then investigates the rewrite capabilities for each of the UNION ALL branches independently. Each of the branches might be rewritten with a materialized view containing a simple or an extended GROUP BY condition.

All basic rewrite mechanisms, such as JOIN BACK are being used.

The following query contains different GROUPING SETS than the materialized view definition; one of the GROUPING SETS cannot be resolved with any existing materialized view, so that Oracle has to join back to the detail tables to satisfy this query.

 

4.

From a SQL*Plus session logged on to the SH schema run the following SQL statement:

@rewrite_gby3.sql

PROMPT full access of MV and usage of SALES fact ofr missing level
PROMPT decompose of grouping levels into UNION ALL

DELETE FROM plan_table;
COMMIT;

EXPLAIN PLAN FOR
  SELECT   calendar_year year, calendar_quarter_desc quarter,
           week_ending_day,cust_state_province state,
           SUM(amount_sold) sum_sales
  FROM     sales s, times t, customers c
  WHERE    s.time_id=t.time_id
    AND    s.cust_id=c.cust_id
  GROUP BY GROUPING SETS ((calendar_year),
                          (calendar_year, week_ending_day),
                          (calendar_year, cust_state_province),
                          (calendar_year, calendar_quarter_desc,
                           cust_state_province));

PROMPT new output, using table function
set linesize 132
set pagesize 999
SELECT * FROM TABLE (dbms_xplan.display);

Move your mouse over this icon to see the image

 

Back to Topic List

Using the Success or Rewrite to Control the Execution of a Statement

There may be situations where you want to stop the query from executing if it did not rewrite. One such situation can be when you expect the un-rewritten query to take an unacceptably long time to execute. To support this requirement, Oracle Database 10g provides a new hint called REWRITE_OR_ERROR. This is a query block-level hint. For example, if the SELECT statement is not rewritten, the error displayed in the message is Thrown. The REWRITE_OR_ERROR hint allows you to run DBMS_MVIEW.EXPLAIN_REWRITE() on the query, resolve the problems that caused rewrite to fail, and run the query again.

1.

Prepare the environment to ensure that a potential query will not be rewritten.

@prep4_roe.sql

ALTER MATERIALIZED VIEW cust_sales_aggr disable query rewrite;
ALTER MATERIALIZED VIEW cust_sales_mv disable query rewrite;
ALTER MATERIALIZED VIEW sales_cube_mv disable query rewrite;
ALTER MATERIALIZED VIEW sales_gby_mv disable query rewrite;

Move your mouse over this icon to see the image

 

2.

The following statement will not be rewritten with any materialized view, as the plan output shows.

@xplan4_roe.sql 
			
EXPLAIN PLAN FOR
SELECT c.cust_last_name,
c.cust_credit_limit,
SUM(amount_sold) AS dollar_sales
FROM sales s, customers c
WHERE s.cust_id= c.cust_id
GROUP BY c.cust_last_name,
c.cust_credit_limit
ORDER BY 1;
set pagesize 50
set linesize 130
SELECT * FROM TABLE(dbms_xplan.display);

Move your mouse over this icon to see the image

 

3.

Consequently, the query will fail when you leverage the new REWRITE_OR_ERROR capabilities.

@run_roe.sql


Rem ORA-30393: a query block in the statement did not rewrite
SELECT /*+ REWRITE_OR_ERROR */ c.cust_last_name,
  c.cust_credit_limit,
  SUM(amount_sold) AS dollar_sales
FROM sales s, customers c
WHERE s.cust_id= c.cust_id
GROUP BY c.cust_last_name,
  c.cust_credit_limit
ORDER BY 1;

Move your mouse over this icon to see the image

 

Back to Topic List

Having a fact table that is partitioned offers two additional benefits for materialized views. The fact that only some partitions have changed, due to a DML or a partition maintenance operation, is useful for:

Partitioning and Query Rewrite

When a certain partition of the detail table is updated, only specific sections of the materialized view are marked stale. The materialized view must have information that can identify the partition of the table corresponding to a particular row or group of the materialized view. The simplest scenario is when the partitioning key of the table is available in the SELECT list of the materialized view, because this is the easiest way to map a row to a stale partition. The key points when using partially stale materialized views are:

Partitioning and Refresh

In a data warehouse, changes to the detail tables can often entail partition maintenance operations, such as DROP, EXCHANGE, MERGE, and ADD PARTITION. To maintain the materialized view after such operations in Oracle8i required the use of manual maintenance (see also CONSIDER FRESH) or complete refresh. Beginning with Oracle9i, an addition to fast refresh known as Partition Change Tracking (PCT) refresh, was added.

For PCT to be available, the detail tables must be partitioned. The partitioning of the materialized view itself has no bearing on this feature. If PCT refresh is possible, it will occur automatically and no user intervention is required in order for it to occur.

The following examples use the second fact table costs.

1.

Ensure a Clean Environment

2.

Creating a Materialize View Containing the Partitioning Key

3.

Analyzing the Materialized View Containing the Partitioning Key

4. Performing a Partition Maintenance Operation on the costs Table and Checking the Status of the Materialized View
5. Adding Data and Performing a Fast Refresh with Partitioning
6. Performing Another Partition Maintenance Operation

Back to Topic List

1. Ensure a Clean Environment

First prepare the environment for the following tests:

1.

From a SQL*Plus session logged on to the SH schema, run cleanup_for_pmop1.sql, or copy the following SQL statement into your SQL*Plus session:

@cleanup_for_pmop1.sql

ALTER TABLE costs DROP PARTITION costs_q1_2002;
ALTER TABLE costs DROP PARTITION costs_q2_2002;
ALTER TABLE costs DROP PARTITION costs_1_2002;

We need the materialized view log for fast refresh capabilities of our next materialized view.

DROP MATERIALIZED VIEW LOG ON costs;

CREATE MATERIALIZED VIEW LOG ON costs
   WITH ROWID, SEQUENCE
   (prod_id, time_id, unit_cost, unit_price )
   INCLUDING NEW VALUES ;

Move your mouse over this icon to see the image

You can ignore any ORA-2149 and ORA-12002 SQL errors.


Back to Topic

2. Create a Materialize View Containing the Partitioning Key

The simplest way to take advantage of Oracle’s enhancements for materialized views based on partitioned tables is to incorporate the partitioning key into the materialized view definition.

1.

From a SQL*Plus session logged on to the SH schema, run create_pkey_mv.sql, or copy the following SQL statement into your SQL*Plus session:

@create_pkey_mv.sql

DROP MATERIALIZED VIEW costs_mv;

CREATE MATERIALIZED VIEW costs_mv
   BUILD IMMEDIATE
   REFRESH FAST ON DEMAND
   ENABLE QUERY REWRITE 
   AS
   SELECT time_id, prod_name, SUM( unit_cost) AS sum_units,
          COUNT(unit_cost) AS count_units, COUNT(*) AS cnt
   FROM   costs c, products p
   WHERE  c.prod_id = p.prod_id
   GROUP BY time_id, prod_name;

Move your mouse over this icon to see the image

Note that you define the materialized view as FAST REFRESHABLE ON DEMAND. The materialized view can become stale.

 

2.

The materialized view is FRESH.

@show_status_of_pkey_mv.sql

ALTER MATERIALIZED VIEW costs_mv COMPILE;

SELECT mview_name, refresh_mode, refresh_method, staleness 
FROM   user_mviews

Move your mouse over this icon to see the image

You can ignore any ORA-2149 and ORA-12002 SQL errors.


Back to Topic

3. Analyze the Materialized View Containing the Partitioning Key

1.

Use the dbms_mview.explain_mview procedure you already know.

@analyze_pkey_mv.sql

TRUNCATE TABLE mv_capabilities_table;

EXEC DBMS_MVIEW.EXPLAIN_MVIEW('costs_mv');

SET SERVEROUTPUT ON

BEGIN
   FOR crec IN (SELECT capability_name, possible, related_text, msgtxt 
                FROM mv_capabilities_table ORDER BY 1) LOOP
      DBMS_OUTPUT.PUT_LINE(crec.capability_name ||': '||crec.possible);
      DBMS_OUTPUT.PUT_LINE(crec.related_text||': '||crec.msgtxt);
   END LOOP;
END;
/

Move your mouse over this icon to see the image

You can see that Partition Change Tracking (PCT) is enabled for the COSTS table and for query rewrite.


Back to Topic

4. Perform a Partition Maintenance Operation on the costs Table and Checking the Status of the Materialized View

To add some empty partitions to the costs table and see how they affect the staleness of the materialized view, you perform the following steps:

1.

From a SQL*Plus session logged on to the SH schema, run add_part_to_cost.sql, or copy the following SQL statement into your SQL*Plus session:

@add_part_to_cost.sql

ALTER TABLE costs
   ADD PARTITION costs_q1_2002 
   values less than (TO_DATE('01-APR-2002', 'DD-MON-YYYY'));

ALTER TABLE costs 
   ADD PARTITION costs_q2_2002 
   values less than (TO_DATE('01-JUL-2002', 'DD-MON-YYYY'));

Move your mouse over this icon to see the image

 

2.

The materialized view is FRESH.

@show_status_of_pkey_mv_cost.sql

ALTER MATERIALIZED VIEW costs_mv COMPILE;

SELECT mview_name, refresh_mode, refresh_method, staleness 
FROM   user_mviews
WHERE mview_name like 'COST%'

Move your mouse over this icon to see the image


Back to Topic

5. Add Data and Performing a Fast Refresh with Partitioning

To add some data to your second fact table costs, you perform the steps below. Note that you are inserting data only into the partitions you recently added. Other data in the table is not changed.

1.

From a SQL*Plus session logged on to the SH schema, run insert_costs.sql, or copy the following SQL statement into your SQL*Plus session:

@insert_costs.sql

INSERT INTO costs VALUES (20, '02-JAN-02',999, 99, 2.50, 25.00);
INSERT INTO costs VALUES (30, '02-FEB-02',999, 99, 2, 34);
INSERT INTO costs VALUES (30, '03-MAR-02',999, 99, 2, 34);
INSERT INTO costs VALUES (40, '21-APR-02',999, 99, 1, 35);
INSERT INTO costs VALUES (40, '22-MAY-02',999, 99, 3, 36);
INSERT INTO costs VALUES (30, '22-APR-02',999, 99, 4, 37);
INSERT INTO costs VALUES (20, '12-JUN-02',999, 99, 5, 34);
COMMIT;

Move your mouse over this icon to see the image

 

2.

Query the data you inserted.

@show_insert_costs.sql

Rem what's in the new partitions

SELECT * 
FROM   costs PARTITION (costs_q1_2002);

SELECT * 
FROM   costs PARTITION (costs_q2_2002);


Move your mouse over this icon to see the image

 

3.

As expected, the status of the materialized view becomes STALE, because the newly inserted data is currently not reflected in the materialized view.

@show_status_of_pkey_mv_cost.sql

ALTER MATERIALIZED VIEW costs_mv COMPILE;

SELECT mview_name, refresh_mode, refresh_method, staleness 
FROM   user_mviews
WHERE  mview_name like 'COST%';

Move your mouse over this icon to see the image


Back to Topic

6. Perform Another Partition Maintenance Operation

You have seen that the materialized view is generically stale. However, it could be used for rewrite as long as no stale data would be touched. You will do this in a moment. In addition, the partition change tracking enables you to do a fast refresh of the costs_mv materialized view.

1.

To control the process of our refresh, let’s query the SUM of all units sold from our materialized view.

@show_sum_pkey_mv.sql

SELECT SUM(sum_units) 
FROM   costs_mv;

Move your mouse over this icon to see the image

 

2.

Do a fast refresh.

@fast_refresh_pkey_mv.sql

Rem Now do a fast refresh
EXEC dbms_mview.refresh('costs_mv','F');

Move your mouse over this icon to see the image

Compare the refresh time to the creation time of the materialized view. Because you only have to refresh the costs_q1_2002and costs_q2_2002 partitions, the refresh needs a fraction of the time of its initial creation. The initial creation time is the time it would need for a complete refresh.

 

3.

Check the content again:

@show_sum_pkey_mv.sql

SELECT  SUM(sum_units) 
FROM    costs_mv;


Move your mouse over this icon to see the image

 

4.

The materialized view is obviously fresh again.

@show_status_of_pkey_mv_cost.sql

ALTER MATERIALIZED VIEW costs_mv COMPILE;

SELECT mview_name, refresh_mode, refresh_method, staleness 
FROM   user_mviews
WHERE  mview_name like 'COST%';

Move your mouse over this icon to see the image

 

Back to Topic

Back to Topic List

Partition Change Tracking (PCT) requires sufficient information in the materialized view to be able to correlate each materialized view row back to its corresponding detail row in the source partitioned detail table. This can be accomplished by including the detail table partition key columns in the select list and, if GROUP BY is used, in the GROUP BY list, as demonstrated in the previous example.Depending on the desired level of aggregation and the distinct cardinalities of the partition key columns, this has the unfortunate effect of significantly increasing the cardinality of the materialized view. For example, say a popular metric is the revenue generated by a product during a given year. If the sales table were partitioned by time_id, it would be a required field in the SELECT clause and the GROUP BY clause of the materialized view. If there were 1000 different products sold each day, it would substantially increase the number of rows in the materialized view.In many cases, the advantages of PCT is offset by this restriction for highly aggregated materialized views. The DBMS_MVIEW.PMARKER function is designed to significantly reduce the cardinality of the materialized view. The function returns a partition identifier that uniquely identifies the partition for a specified row within a specified partition table. The DBMS_MVIEW.PMARKER function is used instead of the partition key column in the SELECT and GROUP BY clauses.The next example demonstrates the functionality of the PMARKER function.

1.

From a SQL*Plus session logged on to the SH schema, run create_pm_mv.sql, or copy the following SQL statement into your SQL*Plus session. This will create a materialized view with the PMARKER information:

@create_pm_mv.sql

DROP MATERIALIZED VIEW costs_pm_mv ;

CREATE MATERIALIZED VIEW costs_pm_mv
   BUILD IMMEDIATE
   REFRESH FAST ON DEMAND
   ENABLE QUERY REWRITE
   AS
   SELECT DBMS_MVIEW.PMARKER(c.rowid) AS pmarker_costs,
          prod_name, SUM( unit_cost) AS sum_units,
          COUNT(unit_cost) AS count_units, COUNT(*) AS cnt
   FROM   costs c, products p
   WHERE c.prod_id = p.prod_id
   GROUP BY prod_name, DBMS_MVIEW.PMARKER(c.rowid);

Move your mouse over this icon to see the image

 

2.

Unlike storing the partitioning key, you now have only one value for each partition, which reduces the cardinality enormously.

@show_pm.sql

SELECT pmarker_costs, count(*) 
FROM   costs_pm_mv group by pmarker_costs;

Move your mouse over this icon to see the image

 

3.

The PCT capabilities are available for this materialized view.

@analyze_pm_mv.sql

TRUNCATE TABLE mv_capabilities_table;

EXEC DBMS_MVIEW.EXPLAIN_MVIEW('costs_pm_mv');

SET SERVEROUTPUT ON

BEGIN
   FOR crec IN (SELECT capability_name, possible, related_text, msgtxt 
                FROM   mv_capabilities_table ORDER BY 1) LOOP
      DBMS_OUTPUT.PUT_LINE(crec.capability_name ||': '||crec.possible);
      DBMS_OUTPUT.PUT_LINE(crec.related_text||': '||crec.msgtxt);
   END LOOP;
END;
/

Move your mouse over this icon to see the image

 

Back to Topic List

You need to make some DML statements on the costs table, which causes the materialized views to become stale. However, query rewrite will work as long as you are not touching any stale data in the materialized view. To do this, perform the following steps:

1.

Insert data and show status.

2.

Explain the partially stale rewrite.

3.

Refresh and show actual data.

Back to Topic List

1. Insert Data and Show Status

1.

From a SQL*Plus session logged on to the SH schema, run insert_costs.sql, or copy the following SQL statement into your SQL*Plus session:

@insert_costs.sql

INSERT INTO costs VALUES (20, '02-JAN-02',999, 99, 2.50, 25.00);
INSERT INTO costs VALUES (30, '02-FEB-02',999, 99, 2, 34);
INSERT INTO costs VALUES (30, '03-MAR-02',999, 99, 2, 34);
INSERT INTO costs VALUES (40, '21-APR-02',999, 99, 1, 35);
INSERT INTO costs VALUES (40, '22-MAY-02',999, 99, 3, 36);
INSERT INTO costs VALUES (30, '22-APR-02',999, 99, 4, 37);
INSERT INTO costs VALUES (20, '12-JUN-02',999, 99, 5, 34);
COMMIT;

Move your mouse over this icon to see the image

 

2.

The information about your current changes also shows up in the materialized view log of the costs table.

@show_mv_log_costs.sql

SELECT count(*)
FROM   mlog$_costs;

Move your mouse over this icon to see the image

 

3.

As expected, both materialized views based on the costs table are stale.

@show_status_costs_mv.sql

ALTER MATERIALIZED VIEW costs_mv COMPILE;
ALTER MATERIALIZED VIEW costs_pm_mv COMPILE;

SELECT mview_name, refresh_mode, refresh_method, staleness
FROM   user_mviews
WHERE  mview_name like 'COST%';

Move your mouse over this icon to see the image


Back to Topic

2. Explain the Partially Stale Rewrite

1.

Issue a query that does not touch any of the stale data. Before running the actual query itself, analyze its rewrite capabilities by using the dbms_mview.explain_rewrite procedure.

@analyze_part_stale_rewrite.sql

TRUNCATE TABLE rewrite_table;

DECLARE
   querytxt VARCHAR2(1500) := 
   ' SELECT p.prod_name, sum(unit_cost) '||
   ' FROM   costs c, products p ' ||
   ' WHERE  c.prod_id = p.prod_id ' ||
   ' AND    c.time_id >= TO_DATE(''01-JAN-2000'',''DD-MON-YYYY'') ' ||
   ' AND    c.time_id < TO_DATE(''01-JAN-2002'',''DD-MON-YYYY'') ' ||
   ' GROUP BY prod_name';
BEGIN   
   dbms_mview.Explain_Rewrite(querytxt, NULL, 'ID1');
END;
/

Rem show final rewrite decision
SELECT message 
FROM   rewrite_table
ORDER BY sequence desc;

Move your mouse over this icon to see the image

The query will be rewritten with the partially stale costs_pm_mv materialized view.

 

2.

The plan shows the rewritten query.

@explain_part_stale_rewrite.sql

TRUNCATE TABLE plan_table;

EXPLAIN PLAN FOR
   SELECT /*+ rewrite (costs_pm_mv) */ p.prod_name, sum(unit_cost)
   FROM   costs c, products p
   WHERE  c.prod_id = p.prod_id
   AND    c.time_id >= TO_DATE('01-JAN-2000','DD-MON-YYYY')
   AND    c.time_id < TO_DATE('01-JAN-2002','DD-MON-YYYY')
   GROUP BY prod_name;

set linesize 132
set pagesize 999
SELECT * FROM TABLE(dbms_xplan.display);

Move your mouse over this icon to see the image

 

3.

Now issue the query.

@run_part_stale_query.sql

SELECT p.prod_name, sum(unit_cost)
FROM   costs c, products p
WHERE  c.prod_id = p.prod_id
AND    c.time_id >= TO_DATE('01-JAN-2000','DD-MON-YYYY')
AND    c.time_id < TO_DATE('01-JAN-2002','DD-MON-YYYY')
GROUP BY prod_name;

PROMPT **** However, the MV is generically STALE !!
SELECT mview_name, refresh_mode, refresh_method, staleness FROM user_mviews
WHERE mview_name like 'COSTS%';
PROMPT **** but it only works if you exactly hit the range boundaries
EXPLAIN PLAN FOR
SELECT COUNT(*) FROM (SELECT p.prod_name, sum(unit_cost)
FROM costs c, products p
WHERE c.prod_id = p.prod_id
AND c.time_id >= TO_DATE('01-JAN-2000','DD-MON-YYYY')
AND c.time_id < TO_DATE('02-JAN-2002','DD-MON-YYYY') GROUP BY prod_name
);
set linesize 120
SELECT * FROM TABLE(dbms_xplan.display);

Move your mouse over this icon to see the image

Move your mouse over this icon to see the image

Note: Unlike a materialized view containing the partitioning key itself, a partially stale materialized view containing the partition marker information can only be used for rewrite when the predicate condition EXACTLY matches the boundaries of the partitioned table. This is the trade-off for reducing the cardinality from all distinct partitioning key values to one value per partition, which makes a distinction on a per-partitioning key value impossible.

 

Back to Topic

3. Refresh and Show the Actual Data

To refresh and show actual data, you perform the following steps:

1.

From a SQL*Plus session logged on to the SH schema, run check_sum_mvs1.sql, or copy the following SQL statement into your SQL*Plus session. The summary of all information in both materialized views is shown:

@check_sum_mvs1.sql

Rem   However, the MV is generically STALE !!
Rem   check data before 

SELECT 'Value Before Costs: ' "Value Before Costs:", 
       SUM(sum_units) 
FROM   costs_mv;

SELECT 'Value Before Cost with PM: ' "Value Before Cost with PM:", 
       SUM(sum_units) 
FROM   costs_pm_mv;

Move your mouse over this icon to see the image

 

2.

You changed only data in the costs table; therefore, you only want to refresh all materialized views that are dependent on this table.

There is a dedicated refresh procedure to refresh dependent materialized views only:

@refresh_costs_mv.sql

Rem Now do a fast refresh using REFRESH_DEPENDENT upon a table
DECLARE
   failures INTEGER;
BEGIN
   -- set to 555 so that we can see that it changes
   failures:= 555;
   dbms_mview.refresh_dependent ( failures, 'COSTS', 'F', '',
      FALSE,FALSE);
   DBMS_OUTPUT.put_line ( 'No of Failures: ' || failures );
END;
/

Move your mouse over this icon to see the image

 

3.

Check the summary in your materialized views again.

@check_sum_mvs2.sql

SELECT 'Value After Costs: ', SUM(sum_units)
FROM   costs_mv;

SELECT 'Value After Cost with PM: ', SUM(sum_units)
FROM   costs_pm_mv;

Move your mouse over this icon to see the image

4.

The costs_pm_mv and costs_pm materialized views are fresh again

@show_status_costs_mv.sql

ALTER MATERIALIZED VIEW costs_pm_mv COMPILE;
ALTER MATERIALIZED VIEW costs_mv COMPILE;

SELECT mview_name, refresh_mode, refresh_method, staleness 
FROM   user_mviews
WHERE  mview_name like 'COST%';

Move your mouse over this icon to see the image

 

Back to Topic

Back to Topic List

The new TUNE_MVIEW API advises what changes you need to make to a materialized view to make it fast refreshable and eligible for advanced query rewrite techniques.In this section, we will examine:

Generating Materialized View Suggestions With Tune_Mview

To generate materialized view suggestions with Tune_Mview , follow these steps:

1.

Let's now use the new tuning capabilities to get advice for a potential materialized view we plan to create.

From a SQL*Plus session logged on to the SH schema, run the credir_for_advisor.sql script to setup a directory object, then run tune_mview01.sql, or copy the following SQL statements into your SQL*Plus session:

@credir_for_advisor.sql

Rem *** You may need to change your pathing, depending on your environment.
CREATE OR REPLACE DIRECTORY advisor_results AS '/tmp';

@tune_mv01.sql

Rem *** START TUNE_MVIEW section 10g
VARIABLE name_01 varchar2(30)


Rem tunemview01
EXECUTE DBMS_ADVISOR.TUNE_MVIEW -
( :name_01 -
, 'CREATE MATERIALIZED VIEW prod_mv -
REFRESH FAST WITH ROWID -
ENABLE QUERY REWRITE -
AS -
SELECT DISTINCT -
prod_name, prod_category -
FROM products' -
) ;

Move your mouse over this icon to see the image

 

2.

View the results about the materialized view.

@mvtune_result01.sql

Rem mvtune_results01.sql
column statement format a70 word
set long 999
SELECT statement
FROM DBA_TUNE_MVIEW
WHERE task_name = :name_01
ORDER BY script_type, action_id;
Move your mouse over this icon to see the image

Note that the DISTINCT clause is replaced by the GROUP BY construct, and the COUNT(*) is added to the
SELECT clause. This makes the materialized view eligible for fast refresh and usable for general rewrite.

Back to Topic

Using Tune_Mview To Make A Materialized View Fast Refreshable

To use Tune_Mview to make a materialized view fast refreshable, follow these steps:

1.

You need to cleanup intentionally create an environment where you cannot create a fast refreshable materialized view.

@cleanup4tune_mv02.sql

DROP MATERIALIZED VIEW LOG ON customers;
DROP MATERIALIZED VIEW LOG ON countries;
DROP MATERIALIZED VIEW cuco_mv;


CREATE MATERIALIZED VIEW LOG ON customers
WITH SEQUENCE, ROWID INCLUDING NEW VALUES;


CREATE MATERIALIZED VIEW LOG ON countries
WITH SEQUENCE, ROWID INCLUDING NEW VALUES;

Move your mouse over this icon to see the image

 

2.

We will now try to create a fast refreshable materialized view. This will fail.

@cr_fr_mv.sql
Rem fails - not fast refreshable
CREATE MATERIALIZED VIEW cuco_mv
  REFRESH FAST
  ENABLE QUERY REWRITE
  AS
    SELECT cu.cust_last_name
          , co.country_name
     FROM customers cu
          , countries co
    WHERE cu.country_id = co.country_id;


Move your mouse over this icon to see the image

  
3.

Let's now us the tuning capabilities of the SQL Access Advisor to fix this for us . From a SQL*Plus session logged on to the SH schema, run tune_mv02.sql, or copy the following SQL statements into your SQL*Plus session:

@tune_mv02.sql

VARIABLE name_02 varchar2(30)
Rem tunemview02.sql
EXECUTE DBMS_ADVISOR.TUNE_MVIEW -
( :name_02 -
  , 'CREATE MATERIALIZED VIEW cuco_mv -
     REFRESH FAST -
     ENABLE QUERY REWRITE -
     AS -
       SELECT cu.cust_last_name -
             , co.country_name -
       FROM customers cu -
           , countries co -
       WHERE cu.country_id = co.country_id' -
) ;

      

The suggestions of the SQL Access Advisor are stored in the database, as it was for the first example.

column statement format a70 word
set long 999


SELECT script_type as type, statement
FROM   DBA_TUNE_MVIEW
WHERE  task_name = :name_02
ORDER BY script_type, action_id; 

Move your mouse over this icon to see the image


4.

Like for the other SQL Access Advisor functionality, you can generate a ready-to-go SQL script for the necessary operations.

@tunemv_script.sql


 EXECUTE DBMS_ADVISOR.CREATE_FILE -
( dbms_advisor.get_task_script(:name_02) -
, location => 'ADVISOR_RESULTS' -
, filename => 'advisor_tune_mv_script.sql' -
) ;
      

Move your mouse over this icon to see the image

You generated the SQL script in directory /tmp, named advisor_tune_mv_script.sql.
The scripts are set up for a Unix system and assume that the directory object ADVSIOR_RESULTS is existent and points to /tmp.

If this directory does not exist you will get an Oracle error message. Either create the directory object with the appropriate privileges or change the directory path in the code to point to an existing directory on your system.

Move your mouse over this icon to see the image


Back to Topic

Back to Topic List

Resetting the Environment

You made a lot of modifications in the environment, including the deletion of some dimensions. To reset this, so that it won’t affect any other tutorials, you perform the following steps:

1.

From a SQL*Plus session logged on to the SH schema, run cleanup_mod4.sql, or copy the following SQL statement into your SQL*Plus session:

@cleanup_mod4.sql

DROP MATERIALIZED VIEW LOG ON sales;
DROP MATERIALIZED VIEW LOG ON customers;
DROP MATERIALIZED VIEW LOG ON products;
DROP MATERIALIZED VIEW LOG ON costs;
ALTER TABLE costs DROP PARTITION costs_1_2002;
CREATE DIMENSION products_dim
LEVEL product IS (products.prod_id)
LEVEL subcategory IS (products.prod_subcategory)
LEVEL category IS (products.prod_category)
HIERARCHY prod_rollup (
product CHILD OF
subcategory CHILD OF
category
)
ATTRIBUTE product DETERMINES
(products.prod_name, products.prod_desc,
prod_weight_class, prod_unit_of_measure,
prod_pack_size,prod_status, prod_list_price, prod_min_price)
ATTRIBUTE subcategory DETERMINES
(prod_subcategory, prod_subcategory_desc)
ATTRIBUTE category DETERMINES
(prod_category, prod_category_desc)
;
CREATE DIMENSION promotions_dim
LEVEL promo IS (promotions.promo_id)
LEVEL subcategory IS (promotions.promo_subcategory)
LEVEL category IS (promotions.promo_category)
HIERARCHY promo_rollup (
promo CHILD OF
subcategory CHILD OF
category
)
ATTRIBUTE promo DETERMINES
(promo_name, promo_cost,
promo_begin_date, promo_end_date)
ATTRIBUTE subcategory DETERMINES (promo_subcategory)
ATTRIBUTE category DETERMINES (promo_category)
;
CREATE DIMENSION channels_dim
LEVEL channel IS (channels.channel_id)
LEVEL channel_class IS (channels.channel_class)
HIERARCHY channel_rollup (
channel CHILD OF
channel_class
)
ATTRIBUTE channel DETERMINES (channel_desc)
ATTRIBUTE channel_class DETERMINES (channel_class)
;
COMMIT;

 

Back to Topic

Back to Topic List

In this tutorial, you've learned how to:

Enable Query Rewrite
Perform a Materialized View Refresh and Rewrite in a variety of ways
Use Partitioning and Materialized views
Using TUNE_MVIEW to Make a Materialized View Fast Refreshable

Back to Topic List

Back to Topic List

Place the cursor over this icon to hide all screenshots.

 

 

 

E-mail this page
Printer View Printer View
Oracle Is The Information Company About Oracle | Oracle RSS Feeds | Careers | Contact Us | Site Maps | Legal Notices | Terms of Use | Privacy