Database Reference
In-Depth Information
How It Works
The following sections describe what a materialized view is and how it works. After describing the concepts that
materialized views are based on, query rewrite and refreshes are covered in detail.
Concepts
Let's say you have to improve the performance of the following query (available in the mv.sql script), which is based
on the sample schema sh (the Oracle Database Sample Schemas manual describes this fully):
SELECT p.prod_category, c.country_id,
sum(quantity_sold) AS quantity_sold,
sum(amount_sold) AS amount_sold
FROM sales s, customers c, products p
WHERE s.cust_id = c.cust_id
AND s.prod_id = p.prod_id
GROUP BY p.prod_category, c.country_id
ORDER BY p.prod_category, c.country_id
If you judge the efficiency of the execution plan by applying the methods and rules described in Chapters 10 and
13, you'll find that everything is fine. The estimations are excellent, and the number of logical reads per returned row
of the different access paths is very low:
-----------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | Buffers |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 81 | 3094 |
| 1 | SORT GROUP BY | | 1 | 68 | 81 | 3094 |
|* 2 | HASH JOIN | | 1 | 968 | 956 | 3094 |
| 3 | TABLE ACCESS FULL | PRODUCTS | 1 | 72 | 72 | 3 |
| 4 | VIEW | VW_GBC_9 | 1 | 968 | 956 | 3091 |
| 5 | HASH GROUP BY | | 1 | 968 | 956 | 3091 |
|* 6 | HASH JOIN | | 1 | 918K| 918K| 3091 |
| 7 | TABLE ACCESS FULL | CUSTOMERS | 1 | 55500 | 55500 | 1456 |
| 8 | PARTITION RANGE ALL| | 1 | 918K| 918K| 1635 |
| 9 | TABLE ACCESS FULL | SALES | 28 | 918K| 918K| 1635 |
-----------------------------------------------------------------------------------
2 - access("ITEM_1"="P"."PROD_ID")
6 - access("S"."CUST_ID"="C"."CUST_ID")
The “problem” is that lots of data is processed before the aggregation takes place. The performance can't be
improved by just changing an access path or a join method, because they're already as optimal as they can be; in other
words, their full potential is already exploited. It's time then to apply an advanced optimization technique. Let's create
a materialized view based on the query to be optimized.
A materialized view is created with the CREATE MATERIALIZED VIEW statement. In the simplest case, you have to
specify a name and the query on which the materialized view is based. Note that the tables on which the materialized
 
Search WWH ::




Custom Search