Oracleの機能を使って表の結合を高速化する：Oracle SQLチューニング講座（11）（2/4 ページ）

[亀田明裕，株式会社アゲハ]

マテリアライズド・ビューの作成と評価

　結合を含むMViewを作成するには、MViewのSELECTリストに各表のROWIDを含める必要があります。そのほかにも、集計や結合を含むMViewを作成するには前提条件がありますので、事前に「Oracle9i データ・ウェアハウス・ガイド リリース2（9.2）」を参照してください。

　なお、今回の例ではON COMMITオプションを指定し、元表の更新がリアルタイムにMViewへ反映されるように作成しています。以下は、MViewの作成方法と、MViewを使用した場合の実行計画と実行統計になります。

CREATE MATERIALIZED VIEW Mview_l_s_n
REFRESH fast on commit
AS
SELECT l.rowid l_rowid,s.rowid s_rowid,n.rowid n_rowid,
n.n_name,l.l_shipdate,l.l_shipmode,s.s_name,s.s_address
FROM lineitem l,supplier s,nation n
WHERE l.l_suppkey=s.s_suppkey 
AND s.s_nationkey=n.n_nationkey;

図4　MViewの作成方法

図5　MViewを使用した場合の実行計画、実行統計（画面をクリックすると拡大します）

　負荷の高い結合処理はMView作成時に完了しており、すでに結合済みの状態でデータが保持されているため、MViewを索引スキャンするのみで結果を返すことが可能となっています。このため、図3と図5の実行計画、実行統計を比較すると、実行計画内の結合の有無、アクセスブロック数が大幅に減少していることが確認できます。ただし、ON COMMITオプションを指定してMViewを利用する場合、元表に対する更新処理のパフォーマンスが劣化する恐れがありますので、十分なテストを行ったうえで使用してください。

マテリアライズド・ビューでクエリーリライト機能を使う

　先ほどの例では、FROM句でMViewの名前を直接指定していましたが、クエリーリライト機能を利用することで、SQLを変更せずに、自動的に表への参照をMViewへの参照に置き換えることが可能となります。クエリーリライト機能を利用するためには、以下の設定が必要となります。

1.SQLを実行するユーザーはQUERY REWRITE権限を保持する
（ほかのユーザーが所有する表を参照する場合、GLOBAL QUERY REWRITE権限が必要）

2.コストベースのアプローチを利用している

3.初期化パラメータ「QUERY_REWRITE_ENABLED=TRUE」に設定されている　

4.MViewがクエリーリライト可能に設定されている
以下のSQLでREWRITE_ENABLED列がYである必要があります。

SQL> SELECT mview_name,rewrite_enabled FROM user_mviews
  2  WHERE mview_name='MVIEW_L_S_N';
MVIEW_NAME                     R
------------------------------ -
MVIEW_L_S_N                    Y

MViewをクエリーリライト可能に変更するには、以下のSQLを実行します。

ALTER MATERIALIZED VIEW Mview_l_s_n
ENABLE QUERY REWRITE;

5.初期化パラメータ「QUERY_REWRITE_INTEGRITY」の値が適切に設定されている
各設定値には以下の意味があります。

パラメータ値	意味
ENFORCED （デフォルト）	MViewと参照元のマスター表とのデータ整合性が取れているときにリライトが行われる
TRUSTED	データ関係の整合性はユーザーに依存しているが、基本的にENFORCEDと同じであり、ディメンションを利用するときに設定する
STALE_TOLERATED	MViewと参照元のマスター表とのデータ整合性が取れていないときもリライトが行われる

　クエリーリライト機能を利用するため上記設定を行い、先ほど実行した結合を含むSQLを実行します。

図6　クエリーリライト機能を使用した場合の実行計画、実行統計（画面をクリックすると拡大します）

　SQLは元表「lineitem」「supplier」「nation」の結合処理を指定していますが、実行計画を見るとMViewが参照されていることが確認できます。このように、クエリーリライト機能を利用することで、アプリケーションのSQLを変更せずに、MViewを利用することが可能になります。ただし、元表に対する更新頻度や、最新データを参照する必要があるのかなどを十分検討し、テストを行ってから利用するようにしてください。