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:
|
Bookmark