マテリアライズド・ビューの作成及び管理
MVIEW検証環境
書籍注文情報ORDER_INFO
PROD(TOM)desc order_info;
Name Null? Type
----------------------------------------- -------- ----------------------------
ORDER_ID NOT NULL NUMBER
ORDER_DATE NOT NULL DATE
BOOK_NO NOT NULL VARCHAR2(20)
BOOK_CNT NOT NULL NUMBER(2)
OPERATOR_ID VARCHAR2(10)
CUST_ID VARCHAR2(10)
書籍情報BOOK
PROD(TOM)desc book;
Name Null? Type
----------------------------------------- -------- ----------------------------
BOOK_NO NOT NULL VARCHAR2(20)
BOOK_NM VARCHAR2(100)
PUB_DATE DATE
高速リフレッシュ・マテリアライズドビュー
マテリアライズドビューの作成要件
集計SQL
select o.operator_id,b.book_nm,sum(o.book_cnt) from order_info o, book b where o.book_no = b.book_no group by o.operator_id,b.book_nm
高速リフレッシュ
手動でリフレッシュ
クエリリライトを有効にする
マテリアライズドビューを作成する前に、SQLの実行計画を確認
PROD(TOM)>set autotrace traceonly;
PROD(TOM)>select
o.operator_id,b.book_nm,sum(o.book_cnt)
from
order_info o,
book b
where
o.book_no = b.book_no
group by o.operator_id,b.book_nm;
990 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1667192454
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 701 | 30143 | 1509 (8)| 00:00:17 |
| 1 | HASH GROUP BY | | 701 | 30143 | 1509 (8)| 00:00:17 |
|* 2 | HASH JOIN | | 506K| 20M| 1429 (3)| 00:00:16 |
| 3 | TABLE ACCESS FULL| BOOK | 30 | 720 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| ORDER_INFO | 506K| 9389K| 1417 (2)| 00:00:16 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("O"."BOOK_NO"="B"."BOOK_NO")
Statistics
----------------------------------------------------------
41 recursive calls
0 db block gets
6874 consistent gets
0 physical reads
0 redo size
32909 bytes sent via SQL*Net to client
1100 bytes received via SQL*Net from client
67 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
990 rows processed
PROD(TOM)>
マテリアライズドビュー・ログの作成
CREATE MATERIALIZED VIEW LOG ON TOM.ORDER_INFO WITH ROWID, SEQUENCE (OPERATOR_ID,BOOK_NO,BOOK_CNT) INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW LOG ON TOM.BOOK WITH ROWID, SEQUENCE (BOOK_NO,BOOK_NM) INCLUDING NEW VALUES; PROD(TOM)>
マテリアライズドビューの作成
CREATE MATERIALIZED VIEW TOM.ORDER_INFO_MV (OPERATOR_ID,BOOK_NM,CNT) REFRESH FAST ON DEMAND ENABLE QUERY REWRITE AS SELECT O.OPERATOR_ID,B.BOOK_NM,SUM(O.BOOK_CNT) FROM ORDER_INFO O, BOOK B WHERE O.BOOK_NO = B.BOOK_NO GROUP BY O.OPERATOR_ID,B.BOOK_NM ORDER BY 1;
クエリリライトによる実行計画が変わる。
ORDER_INFO_MVを全表走査になる。
PROD(TOM)>select
o.operator_id,b.book_nm,sum(o.book_cnt)
from
order_info o,
book b
where
o.book_no = b.book_no
group by o.operator_id,b.book_nm;
990 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3423484028
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 990 | 71280 | 3 (0)| 00:00:01 |
| 1 | MAT_VIEW REWRITE ACCESS FULL| ORDER_INFO_MV | 990 | 71280 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
43 recursive calls
0 db block gets
142 consistent gets
0 physical reads
0 redo size
30814 bytes sent via SQL*Net to client
1100 bytes received via SQL*Net from client
67 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
990 rows processed
PROD(TOM)>
BOOK毎の注文数で集計するSQLを変更しても、実行計画がMVIEWを利用するようにリライトされた。
PROD(TOM)>select
b.book_nm,sum(o.book_cnt)
from
order_info o,
book b
where
o.book_no = b.book_no
group by b.book_nm;
30 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 958428709
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 990 | 64350 | 4 (25)| 00:00:01 |
| 1 | HASH GROUP BY | | 990 | 64350 | 4 (25)| 00:00:01 |
| 2 | MAT_VIEW REWRITE ACCESS FULL| ORDER_INFO_MV | 990 | 64350 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
43 recursive calls
0 db block gets
91 consistent gets
0 physical reads
0 redo size
1264 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
30 rows processed
PROD(TOM)>
手動で高速リフレッシュ
DBMS_MVIEW.REFRESH
PROD(TOM)>EXECUTE DBMS_MVIEW.REFRESH('ORDER_INFO_MV','F');
PL/SQL procedure successfully completed.
PROD(TOM)>
page revision: 17, last edited: 18 Nov 2009 14:10