Analyzing the Materialized View Refresh and Rewrite Capabilities
In this tutorial, you learn how to take advantage of powerful materialized views and query rewrite capabilities.
Approximately 2 hours
This tutorial covers the following topics:
Place the cursor over this icon to load and view all the
screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously,
so response time may be slow depending on your Internet connection.)
Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.
By using the summary management feature, you can ease the workload of the database administrator because you no longer need to spend time creating summaries manually and the end user no longer has to be aware of the summaries that have been defined. Once you create one or more materialized views, which are the equivalent of summaries, the end user can query 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 the 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.
The Oracle database provides you with enriched functionality for a 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 materialized views capabilities. If you want more background information about some of the topics, see the Oracle Data Warehousing Guide.
You 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.
Before starting this tutorial, you should:
| 1. |
Perform the Installing Oracle Database 10g on Windows tutorial. |
| 2. |
Download and unzip mv.zip into your working directory(c:\wkdir). |
Before you start on the materialized views capabilities, some changes are needed to the existing Sales History schema objects and some additional system privileges must be granted to the user SH. Use the SQL file modifySH_10gR2.sql for applying those changes.
| 1. |
Start a SQL *Plus session. Select Start > Programs > Oracle-OraDB10g_home > Application Development > SQL Plus. (Note: This tutorial assumes you have an c:\wkdir folder. If you do not, you will need to create one and unzip the contents of mv.zip into this folder. While executing the scripts, paths are specified)
|
| 2. |
Log in as the SH user. Enter SH as the User Name and SH as the Password. Then click OK.
|
| 3. |
Run the modifySH_10gR2.sql script from your SQL*Plus session. @c:\wkdir\modifySH_10gR2.sql The bottom of your output should match the image below. |
| 4. |
Execute the script xrwutl.sql. @c:\wkdir\xrwutl.sql The bottom of your output should match the image below.
|
To enable query rewrite, you need to meet the following conditions:
|
Individual materialized views must have the ENABLE QUERY REWRITE clause. | |
|
The QUERY_REWRITE_ENABLED initialization parameter must be set to TRUE (the default in 10g). Alternatively you can set this parameter to FORCE; which 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. | |
In this section, you enable query rewrite. 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: @c:\wkdir\set_rewrite_session ALTER SESSION SET query_rewrite_integrity=TRUSTED; ALTER SESSION SET query_rewrite_enabled=FORCE; show parameters query
You are enabling query rewrite and are using the "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. Consult the Oracle Data Warehousing Guide for any further details regarding the levels of query_rewrite_integrity and query_rewrite_enabled.
|
Oracle Database 10g provides procedures that you can use to analyze existing as well as potential materialized views. This enables you to fully leverage all powerful capabilities of materialized views.
To analyze 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: @c:\wkdir\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;
This statement fails and raises the following error: ORA-23413: table "SH"."CUSTOMERS" does not have
a materialized view log.
You could try to correct the error manually and try to create the materialized view again. However, this is an iterative and time consuming process. Instead, using Oracle Database 10g functionality with dbms_mview package, you can easily fix the error. You see this shortly.
|
| 2. |
Even if the statement succeeded, you can not tell the details of its fast refresh capabilities. By using the dbms_mview.explain_mview package, you obtain more insight into the capabilities of the potential materialized view, so that you can address all issues before its creation. @c:\wkdir\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;
/
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:
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.
|
| 3. |
To correct this, first create the materialized view logs identified above. @c:\wkdir\create_mv_logs1.sql DROP MATERIALIZED VIEW LOG ON sales;
This one is used later. DROP MATERIALIZED VIEW LOG ON products; CREATE MATERIALIZED VIEW LOG ON products |
| 4. |
Check the capabilities of the potential materialized view again. @c:\wkdir\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;
/
The fast refresh capabilities of this potential materialized view have changed as expected.
|
| 5. |
Now create the materialized view. @c:\wkdir\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;
|
| 6. |
The explain_mview procedure also works with existing materialized views. @c:\wkdir\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;
/
|
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(such as, SELECT, FROM, WHERE, HAVING, or GROUP BY) of a query with those of a materialized view.
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: @c:\wkdir\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);
The plan 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 in the same manner as the access of a normal table, so that any existing indexes may be used.
|
| 2. | Execute the query. @c:\wkdir\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;
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 control down to the statement level on whether a query is rewritten or not. @c:\wkdir\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);
Without 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.
|
In a data warehouse environment, it is common to have already created summary or aggregation tables. You do not need to repeat this work by building a new materialized views. This solution will provide the performance benefits of materialized views, however, it does not:
|
Provide transparent query rewrite to all SQL applications | |
|
Enable materialized views defined in one application to be transparently accessed in another application | |
|
Generally support fast parallel or fast materialized view refresh | |
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.
Oracle implemented this capability for its existing customer base to provide a safe 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 rewrite capabilities with a single DDL command, without affecting any existing code.
For example, MyCompany recently migrated from Oracle9i to Oracle10g, 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: @c:\wkdir\create_mv2.sql DROP MATERIALIZED VIEW cust_sales_aggr ;
This statement is fairly fast. It does not 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, the system knows about the integrity of the data. However, the first refresh will be a complete refresh in any case.
|
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 the 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 in 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: @c:\wkdir\explain_rewrite2.sql DELETE FROM plan_table;
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. 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) Note: For more information about dimension objects, see the Oracle Data Warehousing Guide.
|
| 2. |
Execute the query. Note that only the result is counted and not actually spool out all of the returned records. @c:\wkdir\do_rewrite2.sql SELECT COUNT(*) FROM (SELECT c.cust_last_name,
|
| 3. |
The plan for the non-rewritten query can be shown with the following statement: @c:\wkdir\explain_norewrite2.sql DELETE FROM plan_table;
Without query rewrite, you would need to process the join between your complete sales fact table and the customers dimension table.
|
You can use the dbms_mview.explain_rewrite procedure, to find detailed information about 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: @c:\wkdir\analyze_rewrite2.sql TRUNCATE TABLE rewrite_table;
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.
|
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: @c:\wkdir\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);
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 Examine the customers_dim dimension definition:
The requested cust_state_province attribute represents the 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. @c:\wkdir\do_rewrite3.sql SELECT COUNT(*)
|
The following example demonstrates the power and flexibility of the query rewrite capabilities. The following example not only does a join back, but also 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: @c:\wkdir\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);
The optimizer rewrites the query to take advantage of the cust_sales_mv materialized view and joins it back to customers and joins 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 loss 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:
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, Oracle database determines the following:
Oracle database uses all of this information to join the materialized view not only with the customers table, but also joins 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: @c:\wkdir\rewrite_sel.sql SELECT COUNT(*) FROM (SELECT co.country_name, |
| 3. | Now execute the query. @c:\wkdir\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;
|
| 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. @c:\wkdir\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);
Note that the materialized view rewrite does not occur in this plan.
|
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. Previously, to take advantage of materialized views in such a situation, you needed to create a materialized view containing all the information for the fact table. Now, you can incorporate a predicate condition in the materialized view definition to allow for 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: @c:\wkdir\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;
Make a 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. @c:\wkdir\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;
It takes longer to create the materialized view, because all data must be touched, joined, and aggregated.
|
Rewrite Using Multiple Materialized Views
New for Oracle Database 10g Release 2 is the ability for query rewrite to use multiple materialized views in order to resolve a query.
| 1. |
Create the following two materialized views that contain the data for November and December in 2001 only From a SQL*Plus session logged on to the SH schema, run cr_qw_mmv.sql, or copy the following SQL statement into your SQL*Plus session: @c:\wkdir\cr_qw_mmv.sql CREATE MATERIALIZED VIEW nov_2001
BUILD IMMEDIATE REFRESH COMPLETE
ENABLE QUERY REWRITE AS
SELECT cust_id, prod_id, time_id, sum(amount_sold) AS dollars
FROM sales s
WHERE time_id >= TO_DATE('01-NOV-2001','DD-MON-YYYY') AND
time_id <= TO_DATE('30-NOV-2001','DD-MON-YYYY')
GROUP BY time_id, cust_id, prod_id;
CREATE MATERIALIZED VIEW dec_2001
BUILD IMMEDIATE REFRESH COMPLETE
ENABLE QUERY REWRITE AS
SELECT cust_id, prod_id,
time_id, sum(amount_sold) AS dollars
FROM sales s
WHERE time_id >= TO_DATE('01-DEC-2001','DD-MON-YYYY') AND
time_id <= TO_DATE('31-DEC-2001','DD-MON-YYYY')
GROUP BY time_id, cust_id, prod_id;
|
| 2. |
Now execute a query to retrieve the sales by customer for the period of November 5th to December 15th. Run explain_mmv.sql, or copy the following SQL statement into your SQL*Plus session: @c:\wkdir\explain_mmv.sql DELETE FROM plan_table; COMMIT; EXPLAIN PLAN FOR
SELECT cust_id, sum(amount_sold) AS dollars
FROM sales s
WHERE time_id >= TO_DATE('05-NOV-2001','DD-MON-YYYY') AND
time_id <= TO_DATE('15-DEC-2001','DD-MON-YYYY')
GROUP BY cust_id;
--See how the query uses only these two materialized views to obtain the data
set linesize 132
set pagesize 999
select * from table(dbms_xplan.display);
|
Estimating the Size of Materialized Views
Besides the improvement in creation time, you 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 information 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. @c:\wkdir\estimate_mv_size1.sql set serveroutput on;
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: @c:\wkdir\estimate_mv_size2.sql
|
| 3. | Now look in the data dictionary to get the actual sizes of the two new materialized views. @c:\wkdir\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 ;
The materialized view containing only the subset of data is about nine 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.
|
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 steps:
| 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: @c:\wkdir\analyze_subset_rewrite.sql TRUNCATE TABLE rewrite_table; DECLARE
no_of_msgs NUMBER;
Rewrite_Array SYS.RewriteArrayType := SYS.RewriteArrayType();
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_state_province = ''Dublin''
AND (c.cust_id = s.cust_id)
AND (s.prod_id = p.prod_id)
GROUP BY c.cust_id, p.prod_id'
BEGIN
dbms_mview.Explain_Rewrite(querytxt, '', Rewrite_Array);
no_of_msgs := Rewrite_Array.count;
FOR i IN 1..no_of_msgs LOOP
DBMS_OUTPUT.PUT_LINE(Rewrite_Array(i).message);
END LOOP;
END;
/
|
Rewriting with Join Back and Aggregation Rollup
The materialized view containing the subset of data can be used for query rewrite like any other materialized view. Those materialized views only have to pass the data containment check for being eligible for rewrite.
The following is an example of using the subset materialized view with a query, where a join back and an aggregation rollup 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: @c:\wkdir\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);
|
| 2. | Run this query also: @c:\wkdir\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);
|
By using the extensions to the GROUP BY clause in the form of GROUPING SETS, ROLLUP, and their concatenation, you are allowed 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:
|
It must contain a grouping distinguisher, which is the GROUPING_ID function on all GROUP BY expressions. For example, if the GROUP BY clause of the materialized view is GROUP BY CUBE(a, b), then the SELECT list should contain GROUPING_ID(a, b). | |
|
The GROUP BY clause of the materialized view should not result in any duplicate groupings. For example, GROUP BY GROUPING SETS ((a, b), (a, b)) would disqualify a materialized view from general rewrite. | |
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 expression: From a SQL*Plus session logged on to the SH schema, run the following SQL statement: @c:\wkdir\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');
The following query contains exactly the same GROUPING SETS as there in 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: @c:\wkdir\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); When both the 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 selects the country_id column from the customers table 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: @c:\wkdir\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);
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 rewritten 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 may 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: @c:\wkdir\rewrite_gby3.sql
PROMPT full access of MV and usage of SALES fact for 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);
|
There may be situations where you want to stop the query from executing if it did not rewrite. One such situation is 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. @c:\wkdir\prep4_roe.sql ALTER MATERIALIZED VIEW cust_sales_aggr disable query rewrite;
|
| 2. | The following statement will not be rewritten with any materialized view, as the plan output shows. @c:\wkdir\xplan4_roe.sql
EXPLAIN PLAN FOR
|
| 3. | Consequently, the query will fail when you leverage the new REWRITE_OR_ERROR capabilities. @c:\wkdir\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;
|
Having a fact table that is partitioned offers two additional benefits for materialized views. If only some partitions have changed, due to a DML or a partition maintenance operation, is useful for:
|
Query rewrite: As long as no stale area of the materialized view is touched, it can be used for rewrite. | |
|
Refresh: The partition information is used to improve refresh of a materialized view | |
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:
|
Query rewrite can use an materialized view in ENFORCED or TRUSTED, mode if the rows from the materialized view used to answer the query are known to be FRESH. | |
|
The fresh rows in the materialized view are identified by adding selection predicates to the materialized view's WHERE clause. You rewrite a query with this materialized view if its answer is contained within this (restricted) materialized view. Note that support for materialized views with selection predicates is a prerequisite for this type of rewrite. | |
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, you can use a refresh known as Partition Change Tracking (PCT).
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. Ensuring 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: @c:\wkdir\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 ;
You can ignore any ORA-2149 and ORA-12002 SQL errors.
|
2. Create a Materialized View Containing the Partitioning Key
The simplest way to take advantage of 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: @c:\wkdir\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;
Note that you define the materialized view as FAST REFRESHABLE ON DEMAND. The materialized view can become stale.
|
| 2. |
The materialized view is FRESH. @c:\wkdir\show_status_of_pkey_mv.sql ALTER MATERIALIZED VIEW costs_mv COMPILE; SELECT mview_name, refresh_mode, refresh_method, staleness FROM user_mviews
|
3. Analyze the Materialized View Containing the Partitioning Key
1. |
Use the dbms_mview.explain_mview procedure you already know. @c:\wkdir\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;
/
You can see that Partition Change Tracking (PCT) is enabled for the COSTS table and for query rewrite.
|
4. Performing 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 to 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: @c:\wkdir\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'));
|
| 2. |
The materialized view is FRESH. @c:\wkdir\show_status_of_pkey_mv_cost.sql ALTER MATERIALIZED VIEW costs_mv COMPILE; SELECT mview_name, refresh_mode, refresh_method, staleness FROM user_mviews
|
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: @c:\wkdir\insert_costs.sql INSERT INTO costs VALUES (20, '02-JAN-02',999, 99, 2.50, 25.00);
|
| 2. | Query the data you inserted. @c:\wkdir\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);
|
| 3. |
As expected, the status of the materialized view becomes STALE, because the newly inserted data is currently not reflected in the materialized view. @c:\wkdir\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%';
|
You have seen that the materialized view is generally stale. However, it could be used for rewrite as long as no stale data is 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. @c:\wkdir\show_sum_pkey_mv.sql SELECT SUM(sum_units) FROM costs_mv;
|
| 2. | Do a fast refresh. @c:\wkdir\fast_refresh_pkey_mv.sql
Rem Now do a fast refresh
EXEC dbms_mview.refresh('costs_mv','F');
Compare the refresh time to the creation time of the materialized view. Because you only have to refresh the costs_q1_2002 and 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: @c:\wkdir\show_sum_pkey_mv.sql SELECT SUM(sum_units) FROM costs_mv;
|
| 4. |
The materialized view is obviously fresh again. @c:\wkdir\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%';
|
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: @c:\wkdir\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);
|
| 2. | Unlike storing the partitioning key, you now have only one value for each partition, which reduces the cardinality enormously. @c:\wkdir\show_pm.sql
SELECT pmarker_costs, count(*)
FROM costs_pm_mv group by pmarker_costs;
|
| 3. | PCT capabilities are available for this materialized view. @c:\wkdir\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;
/
|
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. | |
| 2. | |
| 3. |
| 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: @c:\wkdir\insert_costs.sql INSERT INTO costs VALUES (20, '02-JAN-02',999, 99, 2.50, 25.00);
|
| 2. | The information about your current changes also shows up in the materialized view log of the COSTS table. @c:\wkdir\show_mv_log_costs.sql
SELECT count(*)
FROM mlog$_costs;
|
| 3. |
As expected, both materialized views based on the COSTS table are stale. @c:\wkdir\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%';
|
| 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. @c:\wkdir\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;
The query is rewritten with the partially stale costs_pm_mv materialized view.
|
| 2. |
The plan shows the rewritten query. @c:\wkdir\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);
|
| 3. |
Now issue the query. @c:\wkdir\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 !!
Note: Unlike a materialized view containing the partitioning key itself, a partially stale materialized view containing the partition marker information can be used for rewrite only 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.
|
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: @c:\wkdir\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;
|
| 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: @c:\wkdir\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;
/
|
| 3. | Check the summary in your materialized views again. @c:\wkdir\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;
|
| 4. |
The costs_pm_mv and costs_pm materialized views are fresh again. @c:\wkdir\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%';
|
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:
|
||
|
Using Tune_Mview to Make a Materialized View Fast Refreshable |
|
To generate materialized view suggestions with Tune_Mview , follow these steps:
| 1. |
Use the new tuning capabilities to obtain advice for a potential materialized view that you will create. From a SQL*Plus session logged on to the SH schema, run tune_mv01.sql, or copy the following SQL statements into your SQL*Plus session: @c:\wkdir\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' - ) ;
|
| 2. |
View the results about the materialized view. @c:\wkdir\mvtune_result01.sql Rem mvtune_results01.sql Note that the DISTINCT clause is replaced by the GROUP BY construct, and the COUNT(*) is added to the |
To use Tune_Mview to make a materialized view fast refreshable, follow these steps:
| 1. |
You need to clean up and intentionally create an environment where you cannot create a fast refreshable materialized view. Run the following script to clean the environment. @c:\wkdir\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;
|
| 2. | Try to create a fast refreshable materialized view. This will fail. @c:\wkdir\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; |
| 3. |
Use the tuning capabilities of the SQL Access Advisor to fix the error shown above. 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: @c:\wkdir\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;
|
| 4. |
Like the other SQL Access Advisor functionality, you can generate a ready-to-go SQL script for the necessary operations. Run the script tunemv_script.sql. @c:\wkdir\tunemv_script.sql CREATE DIRECTORY advisor_results AS 'c:\wkdir'; EXECUTE DBMS_ADVISOR.CREATE_FILE - ( dbms_advisor.get_task_script(:name_02) - , location => 'ADVISOR_RESULTS' - , filename => 'advisor_tune_mv_script.sql' - ) ; You generated the SQL script in directory /tmp,
named advisor_tune_mv_script.sql. If this directory does not exist, you will receive 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.
|
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: @c:\wkdir\cleanup_mod4.sql
DROP MATERIALIZED VIEW LOG ON sales;
|
In this tutorial, you learned how to:
 |
Enable query rewrite | |
|
Perform a materialized view refresh and rewrite in a variety of ways | |
|
Use partitioning and materialized views | |
|
Use TUNE_MVIEW to make a materialized view fast refreshable | |
Place the cursor over this icon to hide all screenshots.