マテリアライズ照会表とオプティマイザー (V5R4)

知ってる人が研修に行って学習してきた資料をとっても簡略に再現してみました。

V5R3 のグループ PTF で使用できるようになった、マテリアライズド照会テーブルという機能があります。

こちらでも紹介している機能です。最近の商用 RDB の中では先進的ではあるが一般的になりつつある機能とは言えるでしょう。
もちろん使用するシステム/アプリケーションの要件次第なのですが、パフォーマンスに効果はありそうですね。


この資料/デモがマテリアライズド照会テーブル (MQT) を使用するかしないかでどんな差がでてくるかを対比して見るようになっているので、まず最初にそれ (MQT) を使用しない状態で SQL を実行し、結果を見てみることから始まります。

マテリアライズド照会テーブル (MQT) を使用しない場合

こちらが MQT を使用しない状態で、しかもそれに類する機能を使わない"純"な状態への設定と、MQT の効果をみるための SQL とコマンド です。

CL:CRTDUPOBJ OBJ(QAQQINI) FROMLIB(QSYS) OBJTYPE(*FILE) TOLIB(DBITSODB01) DATA(*YES);

CL:CHGQRYA QRYOPTLIB(DBITSODB01);


UPDATE DBITSODB01.QAQQINI SET QQVAL = '*NONE' WHERE QQPARM = 'ALLOW_TEMPORARY_INDEXES';
UPDATE DBITSODB01.QAQQINI SET QQVAL = '*NONE' WHERE QQPARM = 'CACHE_RESULTS';


SET SCHEMA DBITSODB01;


SELECT YEAR, QUARTER, MONTH,
  SUM(REVENUE_WO_TAX) AS TOTAL_REVENUE,
  SUM(REVENUE_W_TAX) AS NET_REVENUEW,
  SUM(PROFIT_WO_TAX) AS TOTAL_PROFIT,
  SUM(PROFIT_W_TAX) AS NET_PROFIT,
  COUNT(*) AS ROW_PER_GROUP
 FROM ORDERS 
  GROUP BY YEAR, QUARTER, MONTH
  ORDER BY YEAR, QUARTER, MONTH;

上記 SQL に対する Visual Explain の結果です。

マテリアライズド照会テーブル (MQT) を使用した場合

わかりやすくするために上記の SQL をそのまま使用してマテリアライズド照会テーブルを作成します。これで少なくとも直後はこのテーブルを参照しない理由はありませんよね。

CREATE SUMMARY TABLE DBITSODB01.REVENUESUMMARY AS (SELECT YEAR, QUARTER, MONTH,
  SUM(REVENUE_WO_TAX) AS TOTAL_REVENUE,
  SUM(REVENUE_W_TAX) AS NET_REVENUEW,
  SUM(PROFIT_WO_TAX) AS TOTAL_PROFIT,
  SUM(PROFIT_W_TAX) AS NET_PROFIT,
  COUNT(*) AS ROW_PER_GROUP
 FROM ORDERS 
  GROUP BY YEAR, QUARTER, MONTH
  ORDER BY YEAR, QUARTER, MONTH) DATA INITIALLY IMMEDIATE REFRESH DEFERRED MAINTAINED BY USER ENABLE QUERY OPTIMIZATION NOT VOLATILE ;

オプティマイザーによるより高度な使用方法などはまたおいおい調べていくことにしたいと思いますが、ここではまず出発点としてとても単純なケースで動きを確認しましょう。

マテリアライズド照会テーブルがあれば参照するように QAQQINI の設定も行っておきます。

UPDATE DBITSODB01.QAQQINI SET QQVAL = '*ALL' WHERE QQPARM = 'MATERIALIZED_QUERY_TABLE_USAGE';
UPDATE DBITSODB01.QAQQINI SET QQVAL = '*ANY' WHERE QQPARM = 'MATERIALIZED_QUERY_TABLE_REFRESH_AGE';

準備ができたところで、上記とまったく同じ SQL を実行し、Visual Explain の結果を見てみます。

CREATE SUMMARY TABLE DBITSODB01.REVENUESUMMARY AS (SELECT YEAR, QUARTER, MONTH,
  SUM(REVENUE_WO_TAX) AS TOTAL_REVENUE,
  SUM(REVENUE_W_TAX) AS NET_REVENUEW,
  SUM(PROFIT_WO_TAX) AS TOTAL_PROFIT,
  SUM(PROFIT_W_TAX) AS NET_PROFIT,
  COUNT(*) AS ROW_PER_GROUP
 FROM ORDERS 
  GROUP BY YEAR, QUARTER, MONTH
  ORDER BY YEAR, QUARTER, MONTH) DATA INITIALLY IMMEDIATE REFRESH DEFERRED MAINTAINED BY USER ENABLE QUERY OPTIMIZATION NOT VOLATILE ;


UPDATE DBITSODB01.QAQQINI SET QQVAL = '*ALL' WHERE QQPARM = 'MATERIALIZED_QUERY_TABLE_USAGE';
UPDATE DBITSODB01.QAQQINI SET QQVAL = '*ANY' WHERE QQPARM = 'MATERIALIZED_QUERY_TABLE_REFRESH_AGE';


SELECT YEAR, QUARTER, MONTH,
  SUM(REVENUE_WO_TAX) AS TOTAL_REVENUE,
  SUM(REVENUE_W_TAX) AS NET_REVENUEW,
  SUM(PROFIT_WO_TAX) AS TOTAL_PROFIT,
  SUM(PROFIT_W_TAX) AS NET_PROFIT,
  COUNT(*) AS ROW_PER_GROUP
 FROM ORDERS 
  GROUP BY YEAR, QUARTER, MONTH
  ORDER BY YEAR, QUARTER, MONTH;

上記 SQL に対する Visual Explain の結果です。

処理時間もそうですが、処理された行が劇的に減っているのが確認できますね。
それだけシステム資源 (CPU/メモリー/ディスク IO) が節約されている/他のことに使用できるようになっていることがわかりますね。

[Top Pageに戻る]

Ads by TOK2