マテリアライズド・ビューの作成及び管理

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)>
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License