【Oracle】SELECT文の高速化
この記事でわかる・できること
- OracleでSELECT文を高速化する方法
この記事の対象者
- OracleでSQLを書く人全般
動作環境・使用するツールや言語
- Windows 10 Pro 22H2
- Oracle 12cR2 Enterprise Edition
実行計画
まず高速化したい任意のSQLの実行計画を確認します。
SET SERVEROUTPUT OFF
SELECT
employee_id,
last_name,
first_name,
department_name
FROM
employees e,
departments d
WHERE
e.department_id = d.department_id
AND last_name LIKE 'T%'
ORDER BY
last_name;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR())は直前に実行したSQLの実行計画を共有プール上から取得しています。
EXPLAIN PLANはあくまでも見積もりなので上記の方が正確です。
実行結果は下記のようになります。
SQL_ID 40p7g2zq1uz5q, child number 0
-------------------------------------
SELECT employee_id, last_name, first_name,
department_name FROM employees e, departments d WHERE
e.department_id = d.department_id AND last_name LIKE 'T%' ORDER
BY last_name
Plan hash value: 2804786540
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
| 1 | SORT ORDER BY | | 5 | 190 | 6 (34)| 00:00:01 |
| 2 | MERGE JOIN | | 5 | 190 | 5 (20)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 |
|* 5 | SORT JOIN | | 5 | 110 | 3 (34)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES | 5 | 110 | 2 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | EMP_NAME_IX | 5 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
7 - access("LAST_NAME" LIKE 'T%')
filter("LAST_NAME" LIKE 'T%')
詳細は割愛しますが、Time列を確認して急に増加しているところがあったら、その処理に時間がかかっているということになります。
その行のOperation列を確認してください。
TABLE ACCESS FULLやINDEX FULL SCAN、INDEX SKIP SCANになっていたらインデックスの貼り方によって改善できる可能性があります。
-
TABLE ACCESS FULL
インデックスを使わず表の全レコードを読み込んでいます。
インデックスの付与を検討してください。
ただしレコードの大部分がどうしても必要な場合はこれが最適解になることも多いです。 -
INDEX FULL SCAN
インデックスブロックを全て読み込みます。
TABLE ACCESS FULLは複数のブロックをまとめて読み込めますが、INDEX FULL SCANは一つずつなので低速になりやすくなります。
インデックスブロックはソートされているので、結果をソートされた状態で返す時に使うことがあります。または、インデックスブロックの方がブロック数が少ないことが多いので、こちらの方がTABLE ACCESS FULLより速いと判断されれば使うことがあります。 -
INDEX SKIP SCAN
複合インデックスを使ったとき、1列目が使用できず2列目が使用できた場合に使うことがあります。1列目をスキップするかどうかの判定で全部見るのでかなり遅くなります。
2列目だけのINDEXを作成することを検討してください。 -
INDEX FAST FULL SCAN
インデックスブロックを全て読み込みます。INDEX FULL SCANと違って複数のブロックをまとめて読み込めるので高速です。
必要な列がインデックスに全てあり、かつNOT NULL制約があると使用可能です。
高速化には有効ですが、複合インデックスの列が多いとインデックスブロックも多くなるのでサイズが大きくなることに注意してください。 -
INDEX RANGE SCAN
ユニークではないインデックスで検索するか、＜や＞など範囲検索を使用すると発生します。
インデックスに合致する複数のレコードがあるためROWIDを完全には特定できないが、ある程度まで絞り込めています。
さらに条件を追加するなどして範囲を狭めることができないか検討してください。
フルスキャンでもそうですが、アクセス範囲が広いと他のユーザと競合して待ち行列を作ってしまうことがあります。 -
INDEX UNIQUE SCAN
主キーなどユニークなキーで検索します。
最も高速なので改善の余地はほぼありません。
インデックスが使われないケース
実行計画を確認してTABLE ACCESS FULLだったのでインデックスを付与したはいいが使ってくれないというケースがあります。
いくつか考えられる原因があります。
暗黙の型変換
VARCHAR2列 = 1などはインデックスを使用しません。
暗黙の型変換はバグの原因にもなりやすいので使用は推奨しません。
インデックス列に対して関数や算術を使用している
左辺（インデックス列）ではなく右辺に対して関数や算術を使用するようにしてください。
ただしファンクションインデックスを作成すればインデックス列に対しても使用可能です。
LIKEの中間一致、後方一致
Oracle Text（全文検索機能）では中間一致、後方一致でもインデックスが使用できます。
ただし運用の難易度は高めです。
IS NULL、IS NOT NULL
NULLはBツリーインデックス（一般的なインデックス）に含まれないため、インデックスを使用できません。
ビットマップインデックスでは使用できますが、Standard Editionでは使えず、また更新が非常に遅いという欠点があります。
NVL関数やCOALESCE関数でNULL値を変換してファンクションインデックスにするか、複合インデックスで（NULLを含む列,1）のようにしてNULL以外の値を含めるようにすれば使用できます。
IS NOT NULLなら、例えば0以上のデータとNULLしかない列に対して>=0という条件で代用できます。
可能ならばDBの定義を変更し、NULL値を特定のデフォルト値に変換してNULLの代わりに使い、NOT NULL制約を付けることを検討してください。
統計情報が古い
デフォルトでは自動的に収集してくれますが、実行計画が変動して急激に遅くなることがあるため、現場ではこの機能をオフにしていることもあります。
統計情報を再取得すれば妥当な実行計画になることもあります。
JOINするテーブルが多い
JOINするテーブルは５つまでが推奨となっています。
これより多いとOracleが途中で実行計画の計算処理を打ち切ってしまいます。
計算を打ち切らないようにパラメータ(_OPTIMIZER_MAX_PERMUTATIONS)を変更することも可能ですが、非常に負荷がかかるので最適な実行計画を調べる以外の用途では使わない方がいいです。
正規化し過ぎるとJOINが増えるので、可能であれば非正規化を検討してください。
ビューを多用すると不要なテーブルをJOINしている場合もあります。ビューの実体は単なるSQL文です。分解して再構成できないか検討してください。不要な集計やソートをしてメモリを浪費している場合もあります。
メモリを使いすぎるとディスクに書き出してしまうので追加の物理アクセスが発生し、メモリ内での処理に比べて低速になります。
バッチ処理などであれば中間テーブルを作成して処理を分割する方がいいでしょう。
実行計画が妥当ではない
Oracleの実行計画をヒント句でコントロールすることができます。
ただし検索条件によってデータに偏りがある場合や動的なSQLの場合、指定したヒント句がどのような場合でも有効であるかは注意が必要です。
このケースでは速くなるかもしれないが別のケースでは極端に遅くなるということもあります。
また、インデックスを無理やり使わせたとしても速くならないこともあります。インデックスでデータが絞れない場合はフルスキャンの方が速いため、Oracleがそれを理解した上でインデックスを使用しているということになります。
データがあまり絞り込めていない
条件を追加し、複合インデックスを利用してさらに絞り込めないか検討してください。
複合インデックスはカーディナリティが高い列が順番に作成した方がいいですが、検索の仕方によってはこの限りではありません。
例えば1~10,000,000までの番号を持つA列と1~1000までの番号を持つB列を考えた時、A<=999,999 AND B=1だとAの方がカーディナリティが高いにもかかわらずBの方がデータ量を削れます。
運用上このような検索の仕方が多い場合はBを先にしてください。
CREATE TABLE W_TEMP
(
A NUMBER,
B NUMBER
)
;
DECLARE
TYPE REC_TYPE IS TABLE OF W_TEMP%ROWTYPE;
REC REC_TYPE;
BEGIN
FOR i IN 1..1000000 LOOP
INSERT INTO W_TEMP VALUES(i,MOD(i,1000)) ;
END LOOP;
END;
/
--A列が先の複合インデックス
CREATE INDEX W_TEMPA ON W_TEMP(A,B);
--B列が先の複合インデックス
CREATE INDEX W_TEMPB ON W_TEMP(B,A);
SET SERVEROUTPUT OFF
SELECT * FROM W_TEMP WHERE A<=999999 AND B=1;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());
実行計画ではBが先の複合インデックスW_TEMPBを使用していることがわかります。
SQL_ID ajc6zuwh5nfxn, child number 0
-------------------------------------
SELECT * FROM W_TEMP WHERE A<=999999 AND B=1
Plan hash value: 2464146463
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
|* 1 | INDEX RANGE SCAN| W_TEMPB | 924 | 24024 | 5 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B"=1 AND "A"<=999999)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
DROP TABLE W_TEMP;
インデックスがどうしても使えない場合
ここまでの対応でインデックスの付与が有効ではない場合は別の手段を検討します。
とにかくデータを減らす
サブクエリやビューなどでテーブルの全データを取得している場合があります。実際全データが必要でない場合はサブクエリやビューに条件を追加することを検討してください。
Oracleが外のSQLから条件を拾ってきてサブクエリやビューに押し込んでくれる機能もありますが、万能なわけではありません。
途中重複行が発生する場合はGROUP BYやDISTINCT、EXISTSなどを使用して行数を削ってください。（ソートの負荷に注意）
アクセスするレコードを最小限にすることを念頭に置く必要があります。
マテリアライズドビュー
実体のあるビューとしてよく紹介されますが、SELECT文の結果を保持しておくオブジェクトです。
インデックスを貼ることができる、非正規化を比較的容易に実現できるなどのメリットがあります。
元表が更新されると指定したタイミングでリフレッシュされます。
整合性を重視するなら元表が更新されると同時にリフレッシュする必要があります。ただし負荷がかかるので、マスタなど更新が少ないテーブルに対して作成した方がよいでしょう。
Enterprise Editionではクエリーリライトという機能があり、マテリアライズドビューを使用した方が速い場合はOracleがマテリアライズドビューを参照してくれる機能を設定することもできます。AP改修が不要になるというメリットがあります。
パーティショニング
インデックスでは絞り込めない程度の選択率の条件の場合、パーティショニングが有効なことがあります。
パーティション全体を読み込む場合でも複数ブロックをまとめて読み込めるので、インデックスよりも高速です。ここでは詳細は割愛しますが、インデックスと併用することもできます。
パーティションキーを更新するような処理があった場合、パーティション間を移動して再配置するような処理が必要なため、非常に時間がかかるので注意してください。
インメモリ
テーブルをメモリの中に格納し、さらに列指向データベースの特徴を持たせます。
列指向データベースは集計処理など列方向の処理を得意としています。
なおOracleでは処理によって行方向か列方向か自動的に使い分けるので意識はしなくてもいいです。
MEMORY_TARGETやSGA_TAGETの中から確保するのでリソースに注意してください。
ALTER TABLE 【テーブル名】 INMEMORY;
パラレルクエリ
I/Oが集中しないようにパーティショニングと併用したりディスクを分散したりすると有効です。
I/Oが集中するとプロセス間で待ちが発生する、リソースを浪費しやすいなどデメリットもあるので注意が必要です。
アドバイザの利用
SQLチューニングアドバイザなどの機能により改善点を発見します。
アドバイザを鵜呑みにすると、そのSQLは多少速くなっても他の処理が致命的に遅くなるということもあるのであくまでも参考程度にしてください。
当然、アドバイザはOracle内部のことしかわからないので、限界もあります。
入出力の見直し
SELECT文自体の処理結果が本当に必要なものではない可能性もあります。
実は処理していない不要なレコードまで出力していないか、それ以降の処理を確認してください。
呼び出し元の処理も不要に何度も呼び出していないかなど確認する必要があります。
前後の入出力を確認した上で必要最低限のレコードにアクセスするように修正してください。
リソース、要件の見直し
チューニングした上でまだ要件に満たないのであれば、そもそも必要なI/Oに対してリソースが不足している可能性があります。
ディスクの交換やメモリの増設、あるいは要件自体を見直してください。