MQT（マテリアライズ照会表）の作成方法

MQT（マテリアライズ照会表）の作成方法について説明します。
MQTとは、実体のあるビューです。
実体があるということは、インデックスを付与することできる。表スペースを必要とするということになります。
尚、MQTはMaterialized Query Tableの略です。

MQTの作成

ここではMQTを作成のSQLを説明します。
構文は以下の通りです。
構文の詳細は公式サイトを参照ください。
MQTなのに、「CREATE TABLE」で始まるところが個人的に違和感ありです。
何故「CREATE MATERIALIZED QUERY TABLE」ではないのか…。

【文法】

REFRESHオプション

構文	意味
DEFERRED	手動反映
IMMEDIATE	即時（自動）反映

【実行例】

実際にSQLを実行しMQTを作成します。
SQLは以下の通りです。

CREATE TABLE MQT_PRODUCT
AS
(
    SELECT PROD_NO,
           CATE_NO,
           PROD_NAME,
           MAKER,
           PRICE,
           PROD_DESC
      FROM MST_PRODUCT
     WHERE STOP_FLG = 0
)
DATA    INITIALLY DEFERRED
REFRESH DEFERRED
; 

SQLの実行結果は以下の通りです。
これでMQTが作成されました。

SQL> CREATE TABLE MQT_PRODUCT
   2 AS
   3 (
   4     SELECT PROD_NO,
   5            CATE_NO,
   6            PROD_NAME,
   7            MAKER,
   8            PRICE,
   9            PROD_DESC
  10       FROM MST_PRODUCT
  11      WHERE STOP_FLG = 0
  12 )
  13 DATA    INITIALLY DEFERRED
  14 REFRESH DEFERRED
  15 ;

DB250000I: コマンドは正常に完了しました。
  

リフレッシュが必要なのでREFRESHコマンドを実行後にMQTをSELECTしてみます。

REFRESH TABLE MQT_PRODUCT
;

SELECT PROD_NO, CATE_NO, PROD_NAME
  FROM MQT_PRODUCT
 WHERE CATE_NO = 1006
; 

実行結果は以下になります。データが抽出されました。

SQL> REFRESH TABLE MQT_PRODUCT
   2 ;

DB250000I: コマンドは正常に完了しました。

SQL> SELECT PROD_NO, CATE_NO, PROD_NAME
   2   FROM MQT_PRODUCT
   3  WHERE CATE_NO = 1006
   4 ;

      PROD_NO       CATE_NO PROD_NAME                                                 
------------- ------------- ----------------------------------------------------------
        60001          1006 まさお君が来る！　１巻                                     
        60002          1006 まさお君が来る！　２巻                                     
        60003          1006 やさしいJava                                              
        60004          1006 はじめてのHTML                                            
  

MQTの削除

　MQTの削除のSQLを説明します。
　構文は以下の通りです。

【文法】

【実行例】

実際にSQLを実行しMQTを削除します。
SQLは以下の通りです。

DROP TABLE MQT_PRODUCT
; 

SQLの実行結果は以下の通りです。
これでMQTが削除されました。

SQL> DROP TABLE MQT_PRODUCT
   2 ;

DB250000I: コマンドは正常に完了しました。
  

REFRESHオプションの違い

　REFRESHオプションの違いについて説明します。
　MQTの作成では手動反映のMQTを作成しました。
　自動反映のMQTも作成して違いを確認します。

　以下のSQLを実行し手動反映、自動反映のMQTを作成します。

CREATE TABLE MQT_PRODUCT_DEF
AS
(
    SELECT PROD_NO,
           CATE_NO,
           PROD_NAME
      FROM MST_PRODUCT
     WHERE STOP_FLG = 0
       AND CATE_NO  = 1006
)
DATA    INITIALLY DEFERRED
REFRESH DEFERRED
;

CREATE TABLE MQT_PRODUCT_IMM
AS
(
    SELECT PROD_NO,
           CATE_NO,
           PROD_NAME
      FROM MST_PRODUCT
     WHERE STOP_FLG = 0
       AND CATE_NO  = 1006
)
DATA    INITIALLY DEFERRED
REFRESH IMMEDIATE
;

REFRESH TABLE MQT_PRODUCT_DEF;
REFRESH TABLE MQT_PRODUCT_IMM;
  

SQLの実行結果は以下の通りです。
これでMQTの作成とリフレッシュが行われました。

SQL> CREATE TABLE MQT_PRODUCT_DEF
   2 AS
   3 (
   4     SELECT PROD_NO,
   5            CATE_NO,
   6            PROD_NAME
   7       FROM MST_PRODUCT
   8      WHERE STOP_FLG = 0
   9        AND CATE_NO  = 1006
  10 )
  11 DATA    INITIALLY DEFERRED
  12 REFRESH DEFERRED
  13 ;

DB250000I: コマンドは正常に完了しました。

SQL> CREATE TABLE MQT_PRODUCT_IMM
   2 AS
   3 (
   4     SELECT PROD_NO,
   5            CATE_NO,
   6            PROD_NAME
   7       FROM MST_PRODUCT
   8      WHERE STOP_FLG = 0
   9        AND CATE_NO  = 1006
  10 )
  11 DATA    INITIALLY DEFERRED
  12 REFRESH IMMEDIATE
  13 ;

DB250000I: コマンドは正常に完了しました。

SQL> REFRESH TABLE MQT_PRODUCT_DEF;

DB250000I: コマンドは正常に完了しました。

SQL> REFRESH TABLE MQT_PRODUCT_IMM;

DB250000I: コマンドは正常に完了しました。

  

両方のMQTをSELECTします。
作成直後なので内容は同じです。

SQL> SELECT * FROM MQT_PRODUCT_DEF;


      PROD_NO       CATE_NO PROD_NAME                                                 
------------- ------------- ----------------------------------------------------------
        60001          1006 まさお君が来る！　１巻                                     
        60002          1006 まさお君が来る！　２巻                                     
        60003          1006 やさしいJava                                              
        60004          1006 はじめてのHTML                                            
SQL> SELECT * FROM MQT_PRODUCT_IMM;

      PROD_NO       CATE_NO PROD_NAME                                                 
------------- ------------- ----------------------------------------------------------
        60001          1006 まさお君が来る！　１巻                                     
        60002          1006 まさお君が来る！　２巻                                     
        60003          1006 やさしいJava                                              
        60004          1006 はじめてのHTML                                            

  

元のテーブル（基礎表）に対しINSERTを実行しをSELECTします。
手動反映のMQT「MQT_PRODUCT_DEF」には変更が反映されませんが、自動反映のMQT「MQT_PRODUCT_IMM」には変更が反映され行が追加されます。

SQL> INSERT INTO MST_PRODUCT VALUES(60005, 1006, '料理の基本', 'TOKUGAWA',  1000, '', 0);

DB250000I: コマンドは正常に完了しました。


SQL> SELECT * FROM MQT_PRODUCT_DEF;

      PROD_NO       CATE_NO PROD_NAME                                                 
------------- ------------- ----------------------------------------------------------
        60001          1006 まさお君が来る！　１巻                                     
        60002          1006 まさお君が来る！　２巻                                     
        60003          1006 やさしいJava                                              
        60004          1006 はじめてのHTML                                            
SQL> SELECT * FROM MQT_PRODUCT_IMM;

      PROD_NO       CATE_NO PROD_NAME                                                 
------------- ------------- ----------------------------------------------------------
        60001          1006 まさお君が来る！　１巻                                     
        60002          1006 まさお君が来る！　２巻                                     
        60003          1006 やさしいJava                                              
        60004          1006 はじめてのHTML                                            
        60005          1006 料理の基本                                                
  

手動反映のMQTをREFRESHすると変更が反映されます。

SQL> REFRESH TABLE MQT_PRODUCT_DEF;

DB250000I: コマンドは正常に完了しました。

SQL> SELECT * FROM MQT_PRODUCT_DEF;

      PROD_NO       CATE_NO PROD_NAME                                                 
------------- ------------- ----------------------------------------------------------
        60001          1006 まさお君が来る！　１巻                                     
        60002          1006 まさお君が来る！　２巻                                     
        60003          1006 やさしいJava                                              
        60004          1006 はじめてのHTML                                            
        60005          1006 料理の基本                                                
  

MQTの定義の確認

　MQTの定義(CREATE文)はカタログビューSYSCAT.VIEWSのTEXTで確認することができます。
　MQTなのに何故VIEWSなのか。。。は謎である。

SELECT TEXT
  FROM SYSCAT.VIEWS
 WHERE VIEWNAME = 'MQT_PRODUCT'
; 

SQLの実行結果は以下の通りです。

SQL> SELECT TEXT
   2   FROM SYSCAT.VIEWS
   3  WHERE VIEWNAME = 'MQT_PRODUCT'
   4 ;
CREATE TABLE MQT_PRODUCT  AS  (      SELECT PROD_NO,             CATE_NO,             PROD_NAME,
MAKER,             PRICE,             PROD_DESC        FROM MST_PRODUCT       WHERE STOP_FLG = 0
)  DATA    INITIALLY DEFERRED  REFRESH DEFERRED