Oracleラーニングのパフォーマンス最適化(11)マテリアライズド・ビュー


マテリアライズド・ビューは、主にテーブル接続や集約などの時間のかかる操作の結果を事前に計算して保存するために使用されます.これにより、クエリーを実行する際に、これらの時間のかかる操作を回避し、迅速な結果を得ることができます.データ・ウェアハウスでは、クエリーの書き換え(query rewrite)メカニズムもよく使用されます.これにより、既存のクエリー・ステートメントを変更する必要がなくなり、Oracleは適切な物体化ビューを自動的に選択してクエリーを行い、完全に透明に適用されます.
マテリアライズド・ビューは、テーブルと同様に直接クエリーできます.物体化ビュー自体もパーティション化できます.
マテリアライズド・ビューには、クエリーのパフォーマンスを向上させるためにマテリアライズド・ビューを使用することと、インデックスと似たような面がたくさんあります.マテリアライズド・ビューはアプリケーションに対して透明であり、マテリアライズド・ビューの増加と削除はアプリケーション内のSQL文の正確性と有効性に影響しません.物体化ビューはストレージスペースを占有する必要がある.ベーステーブルが変化した場合、物体化ビューもリフレッシュする必要があります.
マテリアライズドビューは、集約されたマテリアライズドビューを含む3つのタイプに分けることができます.接続された物体化ビューのみが含まれます.ネストされたマテリアライズドビュー.3つの物体化ビューの高速リフレッシュの制限条件には大きな違いがありますが、他の点では大きな違いはありません.  
マテリアライズド・ビューを作成するときに、いくつかのオプションを指定できます.次に、いくつかの主な選択について簡単に説明します.
作成方法(Build Methods):BUILD IMMEDIATEとBUILD DEFERREDの2種類があります.BUILD IMMEDIATEは、マテリアライズドビューの作成時にデータを生成し、BUILD DEFERREDは作成時にデータを生成せず、以降必要に応じてデータを生成する.デフォルトはBUILD IMMEDIATEです.
クエリ書き換え(Query Rewrite):ENABLE QUERY REWRITEとDISABLE QUERY REWRITEの2種類が含まれます.作成したマテリアライズド・ビューがクエリーの書き換えをサポートしているかどうかをそれぞれ示します.クエリーの書き換えとは、マテリアライズド・ビューのベース・テーブルをクエリーすると、Oracleはマテリアライズド・ビューをクエリーして結果を得ることができるかどうかを自動的に判断し、可能であれば集計または接続操作を回避し、計算されたマテリアライズド・ビューから直接データを読み出すことです.デフォルトはDISABLE QUERY REWRITEです.
リフレッシュ(Refresh):ベース・テーブルにDML操作が発生した後、マテリアライズド・ビューがベース・テーブルと同期するときの方法を示します.リフレッシュモードには、ON DEMANDとON COMMITの2種類があります.ON DEMAND指物化ビューはユーザーが必要とする時にリフレッシュし、手作業でDBMS_を通過することができるMVIEW.REFRESHなどの方法でリフレッシュを行い、JOBタイミングでリフレッシュすることもできます.ON COMMITは、物体化ビューがベーステーブルのDML操作をコミットしながらリフレッシュされることを示す.リフレッシュの方法はFAST、COMPLETE、FORCE、NEVERの4種類があります.FASTリフレッシュはインクリメンタルリフレッシュを採用し、前回のリフレッシュ以降の変更のみをリフレッシュします.COMPLETEリフレッシュは、物体化ビュー全体を完全にリフレッシュします.FORCE方式を選択した場合、Oracleはリフレッシュ時にフラッシュ可能かどうかを判断し、可能であればFAST方式を採用し、そうでなければCOMPLETE方式を採用します.NEVERとは、マテリアライズド・ビューが更新されないことを意味します.デフォルトはFORCE ON DEMANDです.
マテリアライズド・ビューを作成するときにORDER BY文を指定し、生成したデータを一定の順序で保存することができます.ただし、この文はマテリアライズド・ビューの定義には書き込まれず、以降のリフレッシュにも無効です.
マテリアライズド・ビュー・ログ:高速リフレッシュが必要な場合は、マテリアライズド・ビュー・ログを作成する必要があります.マテリアライズド・ビュー・ログは、異なるマテリアライズド・ビューの高速リフレッシュの必要に応じて、ROWIDまたはPRIMARY KEYタイプとして作成できます.また、SEQUENCE、INCLUDING NEW VALUES、指定したカラムのリストを含めるかどうかを選択することもできます.
ON PREBUILD TABLE文が既存のテーブルにマテリアライズド・ビューを確立することを示すことができる.この場合、マテリアライズドビューとテーブルは同じ名前でなければなりません.マテリアライズド・ビューを削除すると、同じ名前のテーブルは削除されません.このような物体化ビューのクエリ書き換え要求パラメータQUERY_REWRITE_INTEGERITYはtrustedまたはstale_に設定する必要がありますtolerated.
マテリアライズドビューはパーティション化できます.また、パーティションベースの物体化ビューは、パーティション変化追跡(PCT)をサポートすることができる.このような特性を有する物体化ビューは、ベーステーブルがパーティション化メンテナンス操作を行った後も、高速リフレッシュ操作を行うことができる.
集計マテリアライズドビューの場合、CUBEまたはROLLUPは、GROUP BYリストで使用して、異なるレベルの集計マテリアライズドビューを作成することができる.
マテリアライズド・ビューの作成
SQL> conn / as sysdba
Connected.
SQL> grant create materialized view to scott;

Grant succeeded.

SQL> conn scott/tiger
Connected.
SQL> create materialized view mv_emp as select * from emp;

Materialized view created.

マテリアライズド・ビューのデフォルトの完全な文は次のとおりです.
CREATE MATERIALIZED VIEW SCOTT.MV_EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
TABLESPACE USERS
PCTUSED    0
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
USING INDEX
            TABLESPACE USERS
            PCTFREE    10
            INITRANS   2
            MAXTRANS   255
            STORAGE    (
                        INITIAL          64K
                        NEXT             1M
                        MINEXTENTS       1
                        MAXEXTENTS       UNLIMITED
                        PCTINCREASE      0
                        BUFFER_POOL      DEFAULT
                        FLASH_CACHE      DEFAULT
                        CELL_FLASH_CACHE DEFAULT
                       )
REFRESH FORCE ON DEMAND
WITH PRIMARY KEY
AS 
/* Formatted on 2015/9/8 15:28:44 (QP5 v5.252.13127.32847) */
SELECT "EMP"."EMPNO" "EMPNO",
       "EMP"."ENAME" "ENAME",
       "EMP"."JOB" "JOB",
       "EMP"."MGR" "MGR",
       "EMP"."HIREDATE" "HIREDATE",
       "EMP"."SAL" "SAL",
       "EMP"."COMM" "COMM",
       "EMP"."DEPTNO" "DEPTNO"
  FROM "EMP" "EMP";


COMMENT ON MATERIALIZED VIEW SCOTT.MV_EMP IS 'snapshot table for snapshot SCOTT.MV_EMP';

CREATE UNIQUE INDEX SCOTT.PK_EMP1 ON SCOTT.MV_EMP
(EMPNO)
LOGGING
TABLESPACE USERS
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;

基本にデータを挿入
SQL> INSERT INTO SCOTT.EMP (EMPNO,
                       ENAME,
                       JOB,
                       MGR,
                       HIREDATE,
                       SAL,
                       COMM,
                       DEPTNO)
   SELECT EMPNO - 1000,
          ENAME,
          JOB,
          MGR,
          HIREDATE,
          SAL,
          COMM,
          DEPTNO
     FROM scott.emp; 

14 rows created.

SQL> commit;

Commit complete.

マテリアライズド・ビューの表示
SQL> select count(*) from mv_emp;

  COUNT(*)
----------
	14

データは更新されていません
先に物体化ビューデータを保存するrowid
SQL> create table mv_rowid as select rowid rid from mv_emp;

Table created.

手動で物体化ビューをリフレッシュします
SQL> exec DBMS_SNAPSHOT.REFRESH('SCOTT.MV_EMP');

PL/SQL procedure successfully completed.

マテリアライズド・ビューの表示
SQL> select count(*) from mv_emp;

  COUNT(*)
----------
	28

rowidの交差の表示
SQL> select rowid rid from mv_emp intersect select rid from mv_rowid;

no rows selected

マテリアライズド・ビューのリフレッシュは、以前のデータを削除し、フル・リフレッシュを実行していることがわかります.
フルリフレッシュはリソースを消費しますが、基本データの一部だけが変化している場合はfast方式の高速リフレッシュを使用します.クイック・リフレッシュをサポートするには、ベース・テーブルのどのデータ・ローが変更されたかを知る必要があります.
これにより,ベーステーブルの変化を記録したMaterialized logが導入された.
次のテストを行います.
マテリアライズド・ビュー・ログの作成
SQL> CREATE MATERIALIZED VIEW LOG ON EMP;

Materialized view log created.
SQL>  select * from tab;

TNAME			       TABTYPE	CLUSTERID
------------------------------ ------- ----------
BONUS			       TABLE
DEPT			       TABLE
EMP			       TABLE
MLOG$_EMP		       TABLE
RUPD$_EMP		       TABLE
SALGRADE		       TABLE

マテリアライズド・ビュー・ログの作成後、2つのテーブルMLOG$_が追加されました.EMP,RUPD$_EMP.この2つのテーブルはempデータの変化を記録するために使用されます.
次に、すばやくリフレッシュされた物体化ビューを作成します.
SQL> create materialized view fmv_emp refresh fast as select * from emp;
Materialized view created.

SQL> select count(*) from fmv_emp;

  COUNT(*)
----------
	28

empテーブルのデータの一部を削除します.
SQL> delete from emp where empno<7000;

14 rows deleted.

ログ表の確認
SQL> col CHANGE_VECTOR$$ for a10
SQL> select * from MLOG$_EMP;

     EMPNO SNAPTIME$$	       D O CHANGE_VEC	   XID$$
---------- ------------------- - - ---------- ----------
      6369 4000/01/01 00:00:00 D O 0000       2.2519E+15
      6499 4000/01/01 00:00:00 D O 0000       2.2519E+15
      6521 4000/01/01 00:00:00 D O 0000       2.2519E+15
      6566 4000/01/01 00:00:00 D O 0000       2.2519E+15
      6654 4000/01/01 00:00:00 D O 0000       2.2519E+15
      6698 4000/01/01 00:00:00 D O 0000       2.2519E+15
      6782 4000/01/01 00:00:00 D O 0000       2.2519E+15
      6788 4000/01/01 00:00:00 D O 0000       2.2519E+15
      6839 4000/01/01 00:00:00 D O 0000       2.2519E+15
      6844 4000/01/01 00:00:00 D O 0000       2.2519E+15
      6876 4000/01/01 00:00:00 D O 0000       2.2519E+15

     EMPNO SNAPTIME$$	       D O CHANGE_VEC	   XID$$
---------- ------------------- - - ---------- ----------
      6900 4000/01/01 00:00:00 D O 0000       2.2519E+15
      6902 4000/01/01 00:00:00 D O 0000       2.2519E+15
      6934 4000/01/01 00:00:00 D O 0000       2.2519E+15

14 rows selected.

削除されたデータのプライマリ・キーはログ・テーブルに記録され、操作タイプも記録されます.
この場合、既定では手動でリフレッシュされているため、マテリアライズド・ビューはアクティブにリフレッシュされません.
SQL> select count(*) from fmv_emp;

  COUNT(*)
----------
	28

ビューのリフレッシュ
SQL> exec DBMS_SNAPSHOT.REFRESH('FMV_EMP');

PL/SQL procedure successfully completed.

SQL>  select count(*) from fmv_emp;

  COUNT(*)
----------
	14

SQL>

データは同期され、マテリアライズド・ビュー・ログのレコードも削除されました.次のように
SQL> select * from MLOG$_EMP;

no rows selected

次に、マテリアライズド・ビューを自動的にリフレッシュする方法を見てみましょう.
マテリアライズドビューを作成し、1分ごとにリフレッシュします.
SQL> create materialized view auto_refresh_mv_emp refresh fast on demand start with sysdate next sysdate+1/24/60 as select * from emp;

Materialized view created.

SQL> select count(*) from auto_refresh_mv_emp;

  COUNT(*)
----------
	14

SQL>

ベーステーブルの内容の変更
SQL> INSERT INTO SCOTT.EMP (EMPNO,
                       ENAME,
                       JOB,
                       MGR,
                       HIREDATE,
                       SAL,
                       COMM,
                       DEPTNO)
   SELECT EMPNO + 1000,
          ENAME,
          JOB,
          MGR,
          HIREDATE,
          SAL,
          COMM,
          DEPTNO
     FROM scott.emp;  

14 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from auto_refresh_mv_emp;

  COUNT(*)
----------
	14

1分待ってから.
SQL>  select count(*) from auto_refresh_mv_emp;

  COUNT(*)
----------
	28

SQL>

別のビューを見てみましょう
SQL> select count(*) from fmv_emp;

  COUNT(*)
----------
	14

更新されていません.もし今からこの物体化ビューを更新したら.
SQL> select count(*) from mlog$_emp;

  COUNT(*)
----------
	14

SQL> exec DBMS_SNAPSHOT.REFRESH('FMV_EMP');

PL/SQL procedure successfully completed.

SQL> select count(*) from mlog$_emp;

  COUNT(*)
----------
	 0

SQL> select count(*) from fmv_emp;

  COUNT(*)
----------
	28

なぜauto_refresh_mv_Emp自動リフレッシュ時、mlog$Empテーブルは空になっていませんか?Oracleがマテリアライズド・ビューをリフレッシュすると、同じベース・テーブルに依存するマテリアライズド・ビューがリフレッシュされていない場合、mlog$_が空になりません.Empテーブルでは、SNAPTIME$$フィールドの値のみが変更されます.
では、マテリアライズドビューcommitをリフレッシュするとしたら?
SQL> create materialized view refresh_at_commit_emp refresh fast on commit as select * from emp;

Materialized view created.

SQL> select count(*) from refresh_at_commit_emp;

  COUNT(*)
----------
	14

ベーステーブルの内容の変更
SQL> INSERT INTO SCOTT.EMP (EMPNO,
                       ENAME,
                       JOB,
                       MGR,
                       HIREDATE,
                       SAL,
                       COMM,
                       DEPTNO)
   SELECT EMPNO + 1000,
          ENAME,
          JOB,
          MGR,
          HIREDATE,
          SAL,
          COMM,
          DEPTNO
     FROM scott.emp;  

14 rows created.

SQL> select count(*) from refresh_at_commit_emp;

  COUNT(*)
----------
	14

SQL> commit;

Commit complete.

SQL>  select count(*) from refresh_at_commit_emp;

  COUNT(*)
----------
	28

SQL>

可視物体化ビューは、トランザクションのコミットとともにリフレッシュされます.
マテリアライズド・ビューの高速リフレッシュには、次の制限があります.
The defining query of the materialized view is restricted as follows:
  • The materialized view must not contain references to non-repeating expressions like  SYSDATE  and  ROWNUM .
  • The materialized view must not contain references to  RAW  or  LONG   RAW  data types.
  • It cannot contain a  SELECT  list subquery.
  • It cannot contain analytic functions (for example,  RANK ) in the  SELECT  clause.
  • It cannot contain a  MODEL  clause.
  • It cannot contain a  HAVING  clause with a subquery.
  • It cannot contain nested queries that have  ANYALL , or  NOT   EXISTS .
  • It cannot contain a  [START WITH …] CONNECT BY  clause.
  • It cannot contain multiple detail tables at different sites.
  • ON   COMMIT  materialized views cannot have remote detail tables.
  • Nested materialized views must have a join or aggregate.
  • Materialized join views and materialized aggregate views with a  GROUP   BY  clause cannot select from an index-organized table.

  • 高速リフレッシュは、物体化ビューのログにも制限があります.私たちが前に作成したログは、最も簡単です.多くの高速リフレッシュの要件を満たすことはできません.
    次のようなマテリアライズド・ビュー・ログは、最も完全なログです.
    SQL> CREATE MATERIALIZED VIEW LOG ON emp WITH SEQUENCE,ROWID
    (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
    INCLUDING NEW VALUES; 
    
    Materialized view log created.
    
    SQL> desc mlog$_emp;
     Name					   Null?    Type
     ----------------------------------------- -------- ----------------------------
     EMPNO						    NUMBER(4)
     ENAME						    VARCHAR2(10)
     JOB						    VARCHAR2(9)
     MGR						    NUMBER(4)
     HIREDATE					    DATE
     SAL						    NUMBER(7,2)
     COMM						    NUMBER(7,2)
     DEPTNO 					    NUMBER(2)
     M_ROW$$					    VARCHAR2(255)
     SEQUENCE$$					    NUMBER
     SNAPTIME$$					    DATE
     DMLTYPE$$					    VARCHAR2(1)
     OLD_NEW$$					    VARCHAR2(1)
     CHANGE_VECTOR$$				    RAW(255)
     XID$$						    NUMBER
    
    SQL>

    クエリ書き換え{{くえり:かきかえ}}
    sqlクエリの場合、ベーステーブルが直接クエリされます.しかし、このクエリは、マテリアライズド・ビューによって迅速に結果を得ることができます.では、Oracleはクエリーを書き換え、クエリーを物体化ビューにデータを取りに行きます.
    SQL> CREATE MATERIALIZED VIEW agg_emp
       REFRESH FAST ON DEMAND
       ENABLE QUERY REWRITE
    AS
         SELECT deptno,
                SUM (sal) sum_sal,
               -- AVG (sal) avg_sal,
                COUNT (*) dept_cnt
           FROM emp
       GROUP BY deptno;
    
    Materialized view created.
    SQL> select deptno,sum(sal) from emp group by deptno;
        DEPTNO   SUM(SAL)
    ---------- ----------
    	30	18800
    	20	21750
    	10	17500
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2367329769
    
    ----------------------------------------------------------------------------------------
    | Id  | Operation		     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT	     |	       |     3 |    78 |     3	 (0)| 00:00:01 |
    |   1 |  MAT_VIEW REWRITE ACCESS FULL| AGG_EMP |     3 |    78 |     3	 (0)| 00:00:01 |
    ----------------------------------------------------------------------------------------

    また、物体化ビューにインデックスを作成することもできます.
    SQL> create unique index agg_emp_pk on agg_emp(deptno);
    
    Index created.