ここではORACLEデータベースのSQLで、SELECT文をチューニングする方法を紹介します。

SQLの実行計画の見方や確認方法については↓で紹介していますので参考にしてください。
＞＞【ORACLE】SQLの実行計画の見方
＞＞【ORACLE】SQLの実行計画を取得する方法

SELECT文をチューニングする方法

SELECT文をチューニングする方法を5つ紹介します。
　１．インデックスの作成
　２．ヒントの追加
　３．パラレル実行
　４．SQLの書き換え
　５．バッファキャッシュの活用
以降でより詳しく解説していきます。

１．インデックスの作成

SQLをチューニングする最も簡単な方法がインデックスの作成です。

インデックスを作成することで、レコードの抽出にかかる時間を短縮することが出来ます。またインデックスはSQLを変更する必要がないので、プログラムのコードを変更する必要もありません。

インデックスの列にはWHERE句の条件で、抽出条件として書かれているものを書くと効果的です。詳しくは「【SQL】インデックスの項目の順番の正しい付け方」で解説してありますので参考にしてください。

次のようなSELECT文の場合は、COL1列にインデックスを作成します。

select * from table1 a where a.col1 = :cond;

インデックスはCREATE INDEX文で作成することが出来ます。

CREATE INDEX ix_table1_001 ON user1.table1(col1) TABLESPACE index_tbs;

インデックスが使用されると、フルスキャンからインデックスレンジスキャンへと実行計画が変化します。
–TABLE ACCESS BY INDEX ROWID table1
—-INDEX RANGE SCAN IX_table1_001

２．ヒントの追加

SQLは狙い通りに、効率良く実行されないケースがあります。
例えば、次のようなケースです。
・インデックスを作成したけど、インデックスが使われない
・テーブルの結合順序が狙い通りにならない
・たまに遅いときがある
・急に遅くなる時がある

原因は多岐に渡ります。例えば、次のようなことがあります。
・統計情報が最新でない
・SQLが複雑すぎるあまり、適切な実行計画が作成されない
・データの増減によって、実行の都度、実行計画が変わってしまう

こういったケースでは、ヒントが有効です。
ヒントを書き足すことで、実行計画をある程度、狙い通りに変更することが出来ます。
※ヒントは名前の通り、”ヒント”ですので、絶対に変更される訳ではありません。

いくつか、ヒントのサンプルケースを紹介します。

・作成したインデックスが使われない場合のヒント
select /*+INDEX(a IX_table1_001)*/ * from table1 a where a.col1 = :cond;

・FROM句に書いたテーブルの順番で結合したい場合のヒント
SELECT /*+ORDERED*/ a.* FROM TAB1 a,TAB2 b WHERE a.COL1 = b.COL1

・テーブル結合の順序を任意に決めたい場合のヒント
SELECT /*+LEADING(b,a)*/ a.* FROM TAB1 a,TAB2 b WHERE a.COL1 = b.COL1

・テーブル同士をネステッドループで結合したい場合のヒント
SELECT /*+USE_NL(a b)*/ a.* FROM TAB1 a,TAB2 b WHERE a.COL1 = b.COL1

・テーブル同士をハッシュジョインで結合したい場合のヒント
SELECT /*+USE_HASH(a b)*/ a.* FROM TAB1 a,TAB2 b WHERE a.COL1 = b.COL1

※もちろん、複数のヒントを同時に利用することもできます。
・ORDEREDヒントとUSE_NLを同時に利用した場合
SELECT /*+ORDERED USE_NL(a b)*/ a.* FROM TAB1 a,TAB2 b WHERE a.COL1 = b.COL1

３．SQLの書き換え

SQLの書き方は自由度が高く、結果として同じデータを抽出するSQLでも、書き方が異なるケースがあります。
そういった場合は、出来るだけ、効率よく、高速にデータを抽出できる書き方に変更しましょう。

ここでは、代表的なものを紹介します。

・IN句をEXISTS句に置き換え
（変更前）
select * from table1 a where a.div_cd in (select b.div_cd from table2 b where b.div_name = ‘IT’);

（変更後）
select * from table1 a
where exists (select ’x’ from table2 b where b.div_name = ‘IT’ and a.div_cd = b.div_cd);

・OR句の置き換え
（変更前）
select * from table1 a
where a.div_cd = ‘100’ OR a.div_cd = ‘200’;

（変更後）
select * from table1 a where a.div_cd = ‘100’
union all
select * from table1 a where a.div_cd = ‘200’;

・IN句の置き換え
（変更前）
select * from table1 a where a.div_cd in (‘100′,’200’);

（変更後）
select * from table1 a where a.div_cd = ‘100’
union all
select * from table1 a where a.div_cd = ‘200’;

４．パラレル実行

続いては、データベースサーバーのリソースを最大限に活用してチューニングする方法です。リソースに余裕があるのであれば、ORACLEのパラレル機能を活用してください。

これはセッション単位もしくはSQL単位に実行することができ、ORACLEデータベースの内部的プロセスを使って、SQLでのデータ抽出を並列実行することが出来ます。

※一歩間違えるとデータベースサーバー全体のリソースを消費しトラブルになりかねないので、お近くの上位SEやDBAに相談の上、利用してください。

・セッション単位
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 4 ;

・ヒント
SELECT /*+ PRALLEL(4) */ a.* FROM TAB1 a,TAB2 b WHERE a.COL1 = COL2

５．バッファキャッシュの活用

例えば、データの変更はほとんど発生しないが、オンライン処理などで何度も呼び出されるようなマスタなどは、予め、キャッシュにインプットしておきます。そして、キャッシュからアウトプットされないように、設定しておくことができます。

具体的には、バッファキャッシュ領域のうち、いくらかをKEEPバッファキャッシュ領域として割り当てます。
そして、対象となるテーブルの属性をKEEPにします。後は事前にSELECT文を実行するなどして、キャッシュにインプットします。
※こちらもデータベースサーバのキャッシュサイズに関わる変更なので、導入検討する場合はDBAに相談してください。
システムによっては、JAVA側でCACHEする仕組みもあると思います。必ずしもデータベース側での対応が必須ではありません。

・KEEPバッファキャッシュの指定
alter system db_keep_cache_size 5G

・テーブル属性の変更
ALTER TABLE tab1 BUFFER_POOL KEEP
※デフォルトに戻す場合は、ALTER TABLE tab1 BUFFER_POOL DEFAULT

まとめ

SELECT文をチューニングする方法を5つ紹介しました。
　１．インデックスの作成
　２．ヒントの追加
　３．パラレル実行
　４．SQLの書き換え
　５．バッファキャッシュの活用

SQLの遅い原因は多岐に渡ります。よくある原因は↓で紹介していますのでぜひ参考にしてください。
＞＞SQLが突然遅くなる原因と対応方法