Materialized View
1)creation
1@@@@create a table for a sql statement
@@@original sql statement
sql> set linesize 200
sql> conn sh/sh
sql> ed
SELECT c.cust_id , SUM(amount_sold)
FROM sales s , customers c
WHERE s.cust_id=c.cust_id
GROUP BY c.cust_id
/
SQL> /
7059 rows selected.
Execution Plan
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7059 | 103K| | 2233 (7)| 00:00:27 | | |
| 1 | HASH GROUP BY | | 7059 | 103K| 21M| 2233 (7)| 00:00:27 | | |
|* 2 | HASH JOIN | | 918K| 13M| | 459 (10)| 00:00:06 | | |
| 3 | INDEX FAST FULL SCAN| CUSTOMERS_PK | 55500 | 270K| | 28 (4)| 00:00:01 | | |
| 4 | PARTITION RANGE ALL | | 918K| 8973K| | 419 (8)| 00:00:06 | 1 | 28 |
| 5 | TABLE ACCESS FULL | SALES | 918K| 8973K| | 419 (8)| 00:00:06 | 1 | 28 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("S"."CUST_ID"="C"."CUST_ID")
Statistics
----------------------------------------------------------
second:(with cache)
1840 consistent gets
0 physical reads
@@@
sql> ed
CREATE TABLE cust_sales_sum AS
SELECT c.cust_id , SUM(amount_sold) AS amount
FROM sales s , customers c
WHERE s.cust_id=c.cust_id
GROUP BY c.cust_id
/
SQL> select * from cust_sales_sum;
7059 rows selected.
Execution Plan
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7059 | 179K| 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| CUST_SALES_SUM | 7059 | 179K| 6 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
48 recursive calls
510 consistent gets
15 physical reads
2@@@@create a materialized view for that sql statement
sql> conn sh/sh
sql> ed
CREATE MATERIALIZED VIEW cust_sales_mv
ENABLE QUERY REWRITE AS
SELECT c.cust_id , SUM(amount_sold) AS amount
FROM sales s, customers c
WHERE s.cust_id=c.cust_id
GROUP BY c.cust_id
/
SQL> /
Materialized view created.
sql> ed
SELECT c.cust_id , SUM(amount_sold) AS amount
FROM sales s, customers c
WHERE s.cust_id=c.cust_id
GROUP BY c.cust_id
/
SQL> /
7059 rows selected.
Execution Plan
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7059 | 179K| 6 (0)| 00:00:01 |
| 1 | MAT_VIEW REWRITE ACCESS FULL| CUST_SALES_MV | 7059 | 179K| 6 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
second:(with cache)
5 recursive calls
506 consistent gets
0 physical reads
first:(without cache)
4082 recursive calls
0 db block gets
1679 consistent gets
0 physical reads
@@@summary:
@@@materialized view is convenient for refresh and rewrite for sql.
@@@create table is simlar to create materialized view.
2)maintainance
1@@@@refresh
@@@use EM => adminstration => materialized view
<1> on demand (manual)
<2> on commit
<3> automatically (schedual)
@@@evaluation: auto is most useful.
2@@@@use "dbms_mview" package
@@@refresh a specific materialized views.
SQL> exec dbms_mview.refresh('cust_sales_mv');
@@@refresh all materialized view which depend on a set of base table.
SQL> VARIABLE fail NUMBER;
SQL> exec dbms_mview.refresh_dependent(:fail,'customers,sales');
PL/SQL procedure successfully completed.
SQL> print fail
@@@
@@@on demand
@@@use EM => adminstration => materialized view , it could be changed easily.
SQL> select refresh_mode, staleness, update_log
from dba_mviews
where mview_name='CUST_SALES_MV' and owner='SH';
REFRESH_MODE STALENESS UPDATE_LOG
------------------ ------------------------------ ------------------------------
DEMAND FRESH
@@@
@@@on commit
@@@use EM => adminstration => Materialized view => 2M
ALTER MATERIALIZED VIEW SH.CUST_SALES_MV REFRESH ON COMMIT
@@@
@@@on schedual
ALTER MATERIALIZED VIEW SH.CUST_SALES_MV NOLOGGING REFRESH START WITH
to_date('07-03-2012 15:17:36','MM-dd-yyyy hh24:mi:ss')
NEXT sysdate + 3/1440
3@@@@query rewrite
<1>hint "REWRITE_OR_ERROR"
@@@
@@@check the whether the sql statement rewrited or not.
@@@if it is not rewrited, echo error to user,
@@@if it is rewrited, echo nothing
sql> ed
CREATE MATERIALIZED VIEW cust_orders_mv
ENABLE QUERY REWRITE AS
SELECT c.customer_id, SUM(order_total) AS amt
FROM oe.orders s, oe.customers c
WHERE s.customer_id = c.customer_id
GROUP BY c.customer_id
/
SQL> /
Materialized view created.
sql> ed
SELECT /*+ REWRITE_OR_ERROR */
c.customer_id,
SUM(order_total) AS amt
FROM oe.orders s, oe.customers c
WHERE s.customer_id = c.customer_id
GROUP BY c.customer_id
/
sql> /
FROM oe.orders s, oe.customers c
*
ERROR at line 4:
ORA-30393: a query block in the statement did not rewrite
<2>query rewrite example
@@@
@@@create a materialized view cost_per_year_mv
SQL> show parameter query
NAME TYPE VALUE
------------------------------------ --------------------------------- ---------
query_rewrite_enabled string true
query_rewrite_integrity string enforced
SQL> show parameter mode @@@<==the parameter must be CBO
NAME TYPE VALUE
------------------------------------ --------------------------------- ----------
optimizer_mode string ALL_ROWS
sql> ed
CREATE MATERIALIZED VIEW cost_per_year_mv
ENABLE QUERY REWRITE AS
SELECT t.week_ending_day,
t.calendar_year,
p.prod_subcategory,
sum(c.unit_cost) AS dollars
FROM costs c, times t, products p
WHERE c.time_id = t.time_id
AND c.prod_id = p.prod_id
GROUP BY t.week_ending_day,
t.calendar_year,
p.prod_subcategory
/
sql> ed @@@<==here query_rewrite enabled and work,"MAT_VIEW REWRITE ACCESS FULL"
SELECT t.week_ending_day,
t.calendar_year,
p.prod_subcategory,
sum(c.unit_cost) AS dollars
FROM costs c, times t, products p
WHERE c.time_id = t.time_id
AND c.prod_id = p.prod_id
GROUP BY t.week_ending_day, t.calendar_year,
p.prod_subcategory
HAVING sum(c.unit_cost) > 10000 @@@<==just a "having statement" more than the mv query.
/
174 rows selected.
Execution Plan
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 174 | 10788 | 7 (0)| 00:00:01 |
|* 1 | MAT_VIEW REWRITE ACCESS FULL| COST_PER_YEAR_MV | 174 | 10788 | 7 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
0 recursive calls
0 db block gets
35 consistent gets
sql> ed @@@<==here "MAT_VIEW ACCESS FULL"
SELECT week_ending_day,
prod_subcategory,
dollars
FROM cost_per_year_mv
WHERE calendar_year = '1999'
AND dollars > 10000
/
9 rows selected.
Execution Plan
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 558 | 7 (0)| 00:00:01 |
|* 1 | MAT_VIEW ACCESS FULL| COST_PER_YEAR_MV | 9 | 558 | 7 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
0 recursive calls
0 db block gets
24 consistent gets
4@@@@fast refresh which base on materialized view log
@@@if the base table often was modified by DML, may be you could create
@@@the mview log on it.
@@@use EM => adminstration => materialized view log
@@@detail referencing
http://docs.oracle.com/cd/B12037_01/server.101/b10759/statements_6003.htm
@@@
SQL> ed
CREATE MATERIALIZED VIEW LOG ON oe.product_information
WITH ROWID, SEQUENCE (list_price, min_price, category_id)
INCLUDING NEW VALUES;
SQL> /
Materialized view log created.
SQL> ed
CREATE MATERIALIZED VIEW oe.products_mv
REFRESH FAST ON COMMIT
AS SELECT SUM(list_price - min_price), category_id
FROM product_information
GROUP BY category_id;
SQL> /
Materialized view created.
转载于:https://blog.51cto.com/majesty/919966