こんにちは。
データベース（といえばOracle）エンジニアのAです。
今日は、Oracleの意外と知られていないけれど素晴らしい機能クエリーリライトとOracle11gからの新機能リザルト・キャッシュについて紹介します。
SQLを発行すると、Oracleのオプティマイザによって実行計画が立てられます。
以前のバージョンにはルールベースのオプティマイザ(RBO)も存在していましたがOracle10gでサポート対象外となりました。もうだいぶ忘れちゃったんですけど、FROM句にテーブルを書く順序によって実行計画が変わるという、それは恐ろしいオプティマイザでした。
某データベースが10gなのにもかかわらずRBOで動いていたのも今となってはよい思い出です。
これに対してコストベースオプティマイザ(CBO)は、データへのアクセス方式や結合方法について、最も処理コストの低いものを予測して実行計画を立ててくれるそうです。すばらしいですね！
oracle9くらいのバージョンでは、複雑なSQLを記述すると、相当頭のわるい実行計画を立ててくれたこともありました。が、10gR2くらいからものすごく賢くなったように思います。
以下に、クエリーリライト、リザルトキャッシュの簡単な説明と、オプティマイザにそれぞれの機能を選択させる設定について記述します。
■クエリーリライトとは
Oracle8iから導入されたマテリアライズドビュー(Mview)についてはご存知でしょうか。
「実体のあるビュー」と書くとピンと来る方が多いかもしれません。
クエリの結果を実際のテーブルにキャッシュしておくためのオブジェクトです。
クエリーリライトとは、このMviewをFROM句に記載することなく透過的に参照させる機能になります。
■設定
_________________________________________________________________________________________________________________________
/* オプティマイザがCBOであることを確認（Oracle10g以降では通常デフォルトCBO） */
SHOW PARAMETER OPTIMIZER_MODE;
_________________________________________________________________________________________________________________________
/* 初期化パラメータ QUERY_REWRITE_ENABLEDがTRUE（デフォルト）であることを確認 */
SHOW PARAMETER QUERY_REWRITE_ENABLED;
_________________________________________________________________________________________________________________________
/* SQL実行ユーザに QUERY REWRITE 権限を付与 */
GRANT QUERY REWRITE TO TEST_USER;
______________________________________________________________________________________________________________________
__________________________________________________________________________________________________________________________
/* enable query rewrite を指定してMviewを作成する */
create materialized view MVIEW1
refresh complete
enable query rewrite
as select "カテゴリID", "製品A購入数", "顧客ランク",
"年代コード", "性別コード", "都道府県コード", "職業コード"
,count(*) as "件数"
from "顧客情報1"
group by "カテゴリID", "製品A購入数", "顧客ランク",
"年代コード", "性別コード", "都道府県コード", "職業コード" ;
______________________________________________________________________________________________________________________
■実行計画の確認
__________________________________________________________________________________________________________________________
/* 元表 顧客情報1対してSELECTを発行したときの実行計画で MVIEW1 が参照される */
SQL> SELECT "顧客ランク",count(*) FROM "顧客情報1"
WHERE "製品A購入数" >20 GROUP BY "顧客ランク";
実行計画
今回は、シンプルなサマリを用いたMviewで試してみましたが、複数のテーブルを結合するようなクエリであってもクエリーリライトを利用することができます。
■リザルトキャッシュとは
リザルトキャッシュはクエリの実行結果のみをキャッシュする機能です。
Oracleは検索に利用したデータをキャッシュし、次回同じブロックに参照があった場合には、そのデータを利用して高速なレスポンスを確保していますが、リザルトキャッシュは、検索結果そのものをキャッシュすることができます。
■設定
_________________________________________________________________________________________________________________________
/* 初期化パラメータ RESULT_CACHE_MODE を AUTOまたはFORCEに設定 */
ALTER SYSTEM SET RESULT_CACHE_MODE=AUTO;
_________________________________________________________________________________________________________________________
/* 初期化パラメータ RESULT_CACHE_MAX_SIZE を 0以外に設定 */
ALTER SYSTEM SET RESULT_CACHE_MAX_SIZE=200M;
_________________________________________________________________________________________________________________________
■実行計画の確認
_________________________________________________________________________________________________________________________
/* 元表 顧客情報1対してSELECTを発行したときの実行計画で リザルトキャッシュ が参照される */
SQL> SELECT "顧客ランク",count(*) FROM "顧客情報1"
WHERE "製品A購入数" >20 GROUP BY "顧客ランク";
実行計画
