oracle materialized view 清除,Materialized View

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.