Oracleで性能の良いSQLの書き方についてまとめてみました。
Oracleだけではなく一般的にこうした方が良いという内容もあります。
目次
1. 性能の良いSQLとは？
性能の良いSQLは、次の通り定義してみました。
・インデックスを使用するSQL。（今回はBツリーを対象）
・不要なテーブル結合や、ソート処理を行わない。
2. インデックスを使用しないケース
せっかく列にインデックスを設定しても、実行計画をみてみると使われていない。
そんな時は、Where句で次のような条件を使用していないかチェック。
1. NULL比較やNOT（!=）を使用
2. 検索条件の列で演算（関数を使用）している
3. 中間・後方一致検索
4. INリストまたは、ORを使用
1. NULL比較やNOT（!=）を使用
Bツリーインデックスには、NULL値は格納されないため、NULL値を比較してもインデックスは使用されません。
また、NOT（!=）は、”一致しない”条件のため、インデックスを使用せず、フルスキャンの方が早いとオプティマイザ判断し、インデックスが使われないことがあります。
条件として、比較する値が少なければ、OR や IN を使用した方が早いことがあります。
2. 検索条件の列で演算（関数を使用）している
列にインデックスを設定していても、関数などで演算を行うと、演算を行った結果の値がオプティマイザ統計に格納されていないため、インデックスが使用できません。
どうしても演算が必要 かつ インデックスを使用したい場合は、ファンクションインデックスを作成することで、インデックスが使用されます。
また、暗黙的なデータ型変換でも索引は使用されません。
暗黙的な型変換でも、内部ではTO_NUMBER関数などが実行されているためです。
3. 中間・後方一致検索
Bツリーインデックスでは、大小比較でデータを絞り込むため、先頭が決まらないと絞り込みが行えません。
ただし、Oracle 9iからは「索引スキップ・スキャン」が使用できるため、先頭の列にインデックスが設定されていなくても、インデックスを使用することができます。ただし、先頭の列にインデックスが設定されている場合に比べると、効率が悪くなります。
なので、基本的には先頭列にインデックスを設定した方が良いです。
4. INリストまたは、ORを使用
データの偏りによっては、インデックスが使用されない、または効率が悪いアクセスになることがあります。
3. 性能をあげるSQLの書き方
1. IN句よりできるだけ、EXISTS句を使用する
同じ条件をEXISTS句でかける場合は、EXISTS句を使用する。
2. テーブル結合は件数を絞ってから行う
結合する行数が多いと、その分結合処理に時間がかかるため、できるだけ事前にデータ件数を絞ってからテーブル結合を行う。
3. ソート処理はできるだけ少ない件数で行う
例えば、上位100のデータをソートという場合は、ROWNUM < 101 というような条件をつけてソート対象のデータを少なくする。
4. 無駄なソート処理をしない
ORDER BY は明示的にソートを行いますが、DISTINCT、UNION、INTERSECT、MINUSなどは内部で暗黙的にソート処理を行います。そのため、ソート済みのデータをさらに同じソート順でソートする、など無駄なソート処理は行わないようにします。
5. 重複データが存在しない場合は、DISTINCTを指定しない
重複データが無い列にDISTINCTを指定すると、一意性処理（HASH UNIQUE）が行われます。
なので、重複データが無いと明らかになっている列には、DISTINCTは指定しません。
6. UNIONを使用する際は、UNION ALL が使えるか検討する
UNIONは、同じレコードがある場合、マージして１つのレコードにするため、内部でソート処理を行います。
もし、UNIONで結合するそれぞれのデータに、重複するレコードが無いと明らかな場合は、UNION ALL を使用することで、ソート処理を行わないようにすることができます。
4. その他
1. ストアドプロシージャーより、できればファンクションを使用する
ファンクションはSELECT文で呼び出せるため、視認性がいい。
2. 中間テーブルは一時表を使用する
バッチ処理などで多重実行を行う時は、中間テーブルではなく一時表を使用する。
一時表は、トランザクション固有（DELETE ROWS）、セッション固有（PRESERVE ROWS）で作成できるため多重実行を行う際にそれぞれのトランザクション、セッションでデータを扱えるため便利。
3. 結合するテーブル数は多くても６つまで
オプティマイザが、どの順番で結合すれば一番効率的に行えるか計算しますが、10g以降は、結合するテーブル組み合わせを最大で2000パターンまでしか計算しない。
そのため、７テーブル以上あると全ての組み合わせが評価されないためベストでは無い実行計画になる可能性がある。
そのため、テーブル結合は多くても６テーブル以内にする。
5. 参考にさせていただいたサイト
https://www.oracle.com/technetwork/jp/database/articles/tsushima/tsm09-1598259-ja.html
https://www.oracle.com/technetwork/jp/database/articles/tsushima/tsm11-1598263-ja.html