Oracleの機能を使って表の結合を高速化する:Oracle SQLチューニング講座(11)(2/4 ページ)
本連載では、Oracleデータベースのパフォーマンス・チューニングの中から、特にSQLのチューニングに注目して、実践レベルの手法を解説する。読者はOracleデータベースのアーキテクチャを理解し、運用管理の実務経験を積んでいることが望ましい。対象とするバージョンは現状で広く使われているOracle9iの機能を基本とするが、Oracle 10gで有効な情報も随時紹介していく。(編集局)
マテリアライズド・ビューの作成と評価
結合を含む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;
負荷の高い結合処理は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を実行します。
SQLは元表「lineitem」「supplier」「nation」の結合処理を指定していますが、実行計画を見るとMViewが参照されていることが確認できます。このように、クエリーリライト機能を利用することで、アプリケーションのSQLを変更せずに、MViewを利用することが可能になります。ただし、元表に対する更新頻度や、最新データを参照する必要があるのかなどを十分検討し、テストを行ってから利用するようにしてください。
Copyright © ITmedia, Inc. All Rights Reserved.