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
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.
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:
Back to Topic List
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.
|
Back to Topic List
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
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
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;
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;
/
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.
|
| 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;
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;
/
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;
|
| 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;
/
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
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);
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;
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);
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:
|
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. 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;
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);
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;
|
| 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);
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;
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);
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);
|
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);
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;
|
| 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);
|
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;
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;
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;
/
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;
/
|
| 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 ;
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;
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);
|
| 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);
|
Back to Topic
Back to Topic List
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:
|
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 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');
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);
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);
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);
|
Back to Topic List
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;
|
| 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);
|
| 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;
|
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:
|
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. |
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.
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 ;
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;
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
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;
/
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'));
|
| 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%'
|
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;
|
| 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);
|
| 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%';
|
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;
|
| 2. |
Do a fast refresh.
@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_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;
|
| 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%';
|
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);
|
| 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;
|
| 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;
/
|
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:
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;
|
| 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;
|
| 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%';
|
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;
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);
|
| 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);
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;
|
| 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;
/
|
| 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;
|
| 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%';
|
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' -
) ;
|
| 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;
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;
|
| 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;
|
| 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;
|
| 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' -
) ;
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.
|
Back to Topic
Back to Topic List
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
|
To learn more about
Oracle Database 10g, refer to additional OBEs on the OTN
Web site. |
|
To learn more about
other Business Intelligence products, refer to the BI OBEs located here. |
|
To ask a question about this OBE tutorial,
post a query on the OBE
Discussion Forum |
Back to Topic List
Place the cursor over this icon to hide all screenshots.
|
Bookmark
