概要
この記事では、SQLクエリをより効率的に記述するためのベストプラクティスとテクニックに焦点を当てています。データベースのクエリはシステム全体のパフォーマンスに直結するため、最適な書き方を知ることは重要です。インデックスの効果的な活用方法、適切な結合の選択、そして条件の効果的な書き方など、SQLの最適化に関する具体的な手法を解説します。各SQL文に関する実行計画の結果も掲載していますので、ぜひご確認ください。
なお、Oracle19cとOracle12cでの利用実績がありますが、他のデータベースやバージョンにおいての検証は行っておりません。
新しい情報は随時追加されますので、お楽しみにしてください。
SQLの最適化に関連する基本的なアイデア
以下の通りと考えています。
1.インデックスの利用
2.正しいJOINの選択
INNER JOIN、LEFT JOIN、RIGHT JOINなど、状況に応じて最適なJOINを選択します。
3.サブクエリの最適化
サブクエリを避け、JOINを使用して効率的なクエリを作成します。
4.クエリの再利用
同じクエリが複数回実行される場合、クエリの結果を一時テーブルに保存して再利用します。
5.インデックス対象のカラム
WHERE 句やJOIN 句に含まれるカラムにインデックスを設定します。
6.冗長なデータの排除
不要なデータや冗長なデータをクエリから除外します。
7.クエリのキャッシュ
キャッシュを有効にして、頻繁に実行されるクエリの再実行を避けます。
8.適切なデータ型の使用:
より効率的なデータ型を使用してデータベースの容量を最小限に抑えます。
9.正規化と非正規化:
データベースの正規化と非正規化のバランスを取ります。
これらのヒントを基に、具体的なクエリを最適化することができます。また、特定のデータベースシステムによって最適な方法が異なるため、データベースの種類に応じて最適化手法を検討することも重要です。
実行計画の確認方法
explain plan for select * from person order by person_id desc;
select * from table(DBMS_XPLAN.DISPLAY);
例を挙げます。
Plan hash value: 4258456859
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 166K| 50M| | 13397 (1)| 00:00:01 |
| 1 | SORT ORDER BY | | 166K| 50M| 72M| 13397 (1)| 00:00:01 |
| 2 | TABLE ACCESS FULL| PERSON | 166K| 50M| | 2146 (1)| 00:00:01 |
-------------------------------------------------------------------------------------
Oracle実行計画の解説
Oracleの実行計画に表示されるBYTES、TEMP_SPACE、COST、TIMEの各項目について説明します。
1. BYTES:
BYTESは、特定の操作によって処理されるデータのサイズをバイト単位で示します。この値は、データの量を評価するのに役立ちます。大きなBYTESの値は、クエリが多くのデータを処理していることを示し、パフォーマンスの向上のために考慮すべきポイントとなります。
2. TEMP_SPACE (一時テーブルスペース):
TEMP_SPACEは、一時テーブルや一時セグメントを使用する操作において、その操作が使用する一時的な領域（テンポラリースペース）のサイズを示します。クエリがソートやハッシュ結合のために一時的な領域を必要とする場合、この項目が表示されます。適切な設定で一時テーブルスペースを構成することが重要です。
3. COST:
COSTは、クエリの実行コストを示す指標です。この値は、クエリプランの各ステップの相対的なコストを示し、クエリプランの最適性を評価するために使用されます。一般的には、COSTが低いほど、そのステップは最適であると見なされます。
4. TIME:
TIMEは、各操作が実行されるのにかかる予想される時間を示します。ただし、これは実際の実行時間ではなく、オプティマイザーによって見積もられた値です。TIMEが高いステップは、クエリの全体的な実行時間に影響を与える可能性があります。クエリの最適化とパフォーマンスの向上を考える際に重要な情報ですが、単位や正確性は確認が必要です。
※実行計画を分析する際、全表スキャンになっているかを確認する一方、上記の各項目の値も確認したほうが良いと思います。
データアクセス方法の解説
実行計画の妥当性を判断する場合、データアクセス方法は１つの重要な要素です。データアクセス方法は、データベースクエリがどのようにデータを取得するかを示す重要な情報です。Oracleにおいて、以下のデータアクセス方法があります。
全表スキャン(TABLE ACCESS FULL)
テーブル内のすべての行を順次読み取る方法です。これは、検索条件がない場合やデータが少量の場合に使用されることがあります。ただし、大規模なテーブルでは対象データがキャッシュメモリに存在しなければ、データファイルから読み込むことが必要であり、パフォーマンスが低下します。検索条件を指定しないまたは検索条件で指定する列に索引がない場合、全表スキャンが発生します。
-- person_idにおいて、インデックスは設定されていません
explain plan for select * from test_tbl where person_id = 0;
select * from table(DBMS_XPLAN.DISPLAY);
実行結果は以下の通りです。
Plan hash value: 602094504
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 315 | 2291 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST_TBL | 1 | 315 | 2291 (1)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("PERSON_ID"=0)
インデックスレンジスキャン( INDEX RANGE SCAN )
指定された範囲内のデータを検索する方法です。検索条件に指定した列が設定済のインデックスに含まれる場合、発生します。
-- AS30LOG_IP_APIDは以下の列により構成されたインデックスです。
-- remote_host, ACTION_ID,PERSON_ID
explain plan for select * from as30log where remote_host = '123.10.8.1';
select * from table(DBMS_XPLAN.DISPLAY);
実行結果は以下の通りです。
Plan hash value: 3306237225
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 1810 | 14 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| AS30LOG | 10 | 1810 | 14 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | AS30LOG_IP_APID | 10 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("REMOTE_HOST"='123.10.8.1')
一意スキャン（INDEX UNIQUE SCAN）
一意性制約を利用して効率的にデータを取得します。検証条件に指定した列は対象列がユニークインデックスまたは主キーであれば発生します。
-- person_idに関しては、主キーに設定されています。
explain plan for select person_id, person_name from person where person_id = '123';
select * from table(DBMS_XPLAN.DISPLAY);
以下はこのクエリの実行計画です。
Plan hash value: 3519488248
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| PERSON | 1 | 7 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SMC_PERSON | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("PERSON_ID"=123)
インデックスファーストフルスキャン（INDEX FAST FULL SCAN）
インデックス全体を高速にスキャンし、その結果としてインデックスに含まれるすべての列を取得します。
SQLで参照列は全てインデックスに含まれ、検証条件に列を指定しない場合、発生します。
-- AS30LOG_IP_APIDは以下の列により構成されたインデックスです。
-- remote_host, ACTION_ID,PERSON_ID
explain plan for select remote_host, action_id, person_id from as30log ;
select * from table(DBMS_XPLAN.DISPLAY);
以下はこのクエリの実行計画です。
Plan hash value: 3388218003
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 584K| 15M| 1922 (1)| 00:00:01 |
| 1 | INDEX FAST FULL SCAN| AS30LOG_IP_APID | 584K| 15M| 1922 (1)| 00:00:01 |
----------------------------------------------------------------------------------------
インデックスフルスキャン（INDEX FULL SCAN）
対象のテーブルのインデックス全体を順次スキャンして、条件に一致するすべての行を検索するプロセスを指します。INDEX FULL SCANは、通常、検索条件がインデックスの左端にない場合や、特定の列に対する条件が不十分な場合に発生します。Oracle 19cで検証したSQL文の実行計画の結果では、全てINDEX FAST FULL SCANになりましたため、例を挙げることができません。
結合方法
実行計画の妥当性を判断する場合、結合方法は１つの重要な要素です。以下では、これら４つの異なる結合方法について説明します：
ネステッドループ（NESTED LOOP）
ネステッドループは、ループ処理をネストにして結合処理が行われるアルゴリズムです。外側のループが内側のループを制御し、条件に合致する行を探索する仕組みを持っています。ネステッドループ結合は、結合対象のデータサブセットが比較的小さい場合に特に適しています。
詳細な説明については、以下の記事をご参照ください。
ネステッドループの詳細解説と実践的なヒント
ハッシュ結合（Hash Join）
ハッシュ値を使用して結合処理が行われるアルゴリズムです。
ハッシュ結合の基本的な動作
- 一方の表データの結合キーからハッシュテーブルを作成します。
- 他方の表の結合キーからハッシュ値を算出します。
- 算出したハッシュ値と結合キーを PGA に格納します。メモリに収まらない場合、TEMP ファイルを利用します。
使用例
explain plan for SELECT /*+ leading(p) use_hash(c)*/*
FROM lcqa1.test_person p, lcqa1.test_company c
WHERE p.company_id = c.company_id;
このクエリの実行計画は以下の通りです。
Plan hash value: 1082620218
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17 | 7514 | 7 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 17 | 7514 | 7 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| TEST_PERSON | 72 | 21888 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TEST_COMPANY | 16 | 2208 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("P"."COMPANY_ID"="C"."COMPANY_ID")
ハッシュ結合を利用している場合でも、必ずしも全表スキャンになるわけではありません。ハッシュ結合は、テーブル全体をスキャンすることなくハッシュテーブルを使用して結合を行います。
パフォーマンス向上のためのポイント
- メモリサイズの適切な調整:
PGA サイズを大きく調整します。 - 適切なインデックスの存在:
ハッシュ結合の前に結合対象のテーブルに対して適切なインデックスを作成します。これにより、結合処理の効率が向上します。 - ハッシュ関数の選択:
ハッシュ結合の性能はハッシュ関数の選択に影響を受けます。高品質で効率的なハッシュ関数を選択し、ハッシュ衝突を最小限に抑えるようにします。 - データの分布の確認:
ハッシュ結合の性能は、結合対象のデータの分布に依存します。データの分布が不均一な場合は、適切な対策を講じることが必要です。 - データベースの統計情報の最新化:
データベースの統計情報を最新化しておくことで、クエリプランナーが最適な実行計画を選択しやすくなります。
マージ結合（Merge Join）
マージ結合（Merge Join）は、データベースクエリで複数のテーブルを結合するためのアルゴリズムの一つです。このアルゴリズムは、結合キーを使用して両方のテーブルを順次スキャンし、マージ（合併）することで結合を実現します。
マージ結合の基本的な動作
- 両方のテーブルのスキャン:
結合キーに基づいて、結合対象となる両方のテーブルを順次スキャンします。 - マージ:
スキャン中に、結合キーが一致する行をマージして結果セットに追加します。
結合キーが一致しない場合は、次の行に進みます。 - 結合の継続:
どちらかのテーブルの終端に達するまで、結合を継続します。
もし片方のテーブルが終端に達した場合、残りのテーブルの行は結合されません。 - 結果セットの生成:
マージが完了すると、結合された行が含まれる結果セットが生成されます。
使用例
SELECT /*+ leading(p) user_merge(c)*/*
FROM lcqa1.test_person p, lcqa1.test_company c
WHERE p.company_id = c.company_id;
このクエリの実行計画は以下の通りです。
Plan hash value: 1082620218
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17 | 7514 | 7 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 17 | 7514 | 7 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| TEST_PERSON | 72 | 21888 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TEST_COMPANY | 16 | 2208 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("P"."COMPANY_ID"="C"."COMPANY_ID")
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax error (1))
---------------------------------------------------------------------------
1 - SEL$1
E - user_merge
マージ結合の場合、全表スキャンが発生するかどうかは、具体的なデータベース管理システムやクエリプランナーの実装に依存します。クエリプランナーは、最適な実行計画を選択するために統計情報や索引を利用し、必要に応じて部分的なスキャンを行うこともあります。
パフォーマンス向上のためのポイント
- 適切なインデックスの作成:
結合キーに対して適切なインデックスを作成することで、結合操作の効率が向上します。
結合キーがソートされていない場合でも、適切なインデックスが存在すれば、検索効率が向上します。 - データの統計情報を更新:
データベース管理システムが提供する統計情報（statistics）を定期的に更新することで、クエリプランナーが最適な実行計画を選択しやすくなります。
統計情報の更新により、結合キーの分布やデータの特性が正確に反映され、最適な結合アルゴリズムが選択される可能性が高まります。 - 適切なクエリの書き方:
クエリを適切に記述することもパフォーマンス向上に寄与します。冗長な条件や不要な列の取得を避け、必要なデータのみを取得するように心がけましょう。 - メモリの適切な利用:
マージ結合の性能はディスク I/O の影響を受けることがあります。メモリを十分に利用できる場合、データをメモリ上にキャッシュすることでディスクアクセスを軽減できます。 - データ量の把握:
テーブルのデータ量が非常に大きい場合、効率的なパーティショニングや分割を考えることが重要です。
ソートマージ「結合（sort merge Join）
ソートマージ（Sort-Merge）は、データベースにおける結合アルゴリズムの一つであり、通常は大きなデータセットを扱う場合に効果的です。このアルゴリズムは、結合対象のテーブルが事前にソートされていることを前提としています。
ソートマージ結合の基本的な動作
- ソート:
結合対象のテーブルは、結合キー（Join Key）を基準にソートされます。このソートは通常外部ソートと呼ばれる手法を用いて行われます。
外部ソートは、メモリの限られた容量内でソートするため、大規模なデータセットにも適用可能です。 - マージ:
ソートされた2つのテーブルをマージします。
結合キーが一致する行同士が結合され、一致しない場合は次の行に進みます。
ソートされたデータを使うことで、一致する行を順番に見つけることが効率的に行えます。 - 結合結果:
ソートマージの結果として、ソートされた状態で結合された結果が得られます。
使用例
explain plan for SELECT *
FROM lcqa1.test_person p, lcqa1.test_company c
WHERE p.company_id = c.company_id;
このクエリの実行計画は以下の通りです。
Plan hash value: 1850696154
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17 | 86445 | 7 (15)| 00:00:01 |
| 1 | MERGE JOIN | | 17 | 86445 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST_COMPANY | 16 | 25824 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | TEST_COMPANY_PK | 16 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 72 | 244K| 5 (20)| 00:00:01 |
| 5 | TABLE ACCESS FULL | TEST_PERSON | 72 | 244K| 4 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("P"."COMPANY_ID"="C"."COMPANY_ID")
filter("P"."COMPANY_ID"="C"."COMPANY_ID")
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
パフォーマンス向上のためのポイント
- 適切なインデックスの利用:
結合キーに対して適切なインデックスを使用することで、ソートのコストを削減できます。
インデックスが結合キーと一致していれば、ソートが不要になります。 - 外部ソートの最適化:
外部ソートの際、データがディスクに書き込まれる回数や、メモリの使用効率を最適化することで、ソート処理のパフォーマンスが向上します。 - ソートキーの選択:
ソートの対象となる結合キーの選択が重要です。ビジネスロジックやクエリの要件に基づいて最適なソートキーを選択することが必要です。 - メモリ管理:
外部ソートはメモリを使用してソートを行います。メモリの管理が重要であり、十分なメモリが確保できない場合にはディスクへの書き込みが発生するため、パフォーマンスが低下します。 - 結合条件の最適化:
結合条件を最適化することで、不要な結合が行われるのを避け、処理の効率を向上させることができます。 - 並列処理の活用:
大規模なデータセットに対しては、複数のプロセッサやコアを用いて並列処理を行うことで、結合処理のパフォーマンスを向上させることができます。
結合方法のメリット・デメリット
ソートマージ結合
通常の状況では、ソートマージ結合の実行効率はハッシュ結合よりも劣りますが、ソートマージ結合はより広範な用途に適しています。なぜなら、ハッシュ結合は通常等値結合条件にしか使用できず、一方でソートマージ結合は他の結合条件（例えば <、<=、>、>= など）にも使用できるからです。
通常の状況では、ソートマージ結合はOLTP（オンライン・トランザクション・プロセッシング）型のシステムに適していません。その本質的な理由は、OLTP型のシステムにとってはソートが非常に高コストな操作であるためです。もちろん、ソート操作を回避できれば、OLTP型のシステムでもソートマージ結合を使用できます。例えば、2つのテーブルがソートマージ結合されているが、実際にはそれらがソートされる必要がない場合があります。それは、各テーブルがそれぞれの結合列にインデックスが存在するためです。
厳密に言えば、ソートマージ結合には駆動テーブルの概念は存在しませんが、私は個人的には結合のプロセス中に実際には駆動テーブルと被駆動テーブルが存在していると考えています。
ネストループ結合
ネストループ結合に関しては、ドライブテーブルに対応するドライブ結果セットのレコード数が比較的少なく、かつ被駆動テーブルの結合列にユニークなインデックスが存在している場合（または被駆動テーブルの結合列に非ユニークながら選択性が高いインデックスが存在している場合）、この時点でネストループ結合の実行効率は非常に高くなります。しかし、ドライブテーブルに対応するドライブ結果セットのレコード数が多い場合、被駆動テーブルの結合列にインデックスが存在していても、ネストループ結合の実行効率は高くなりません。
ドライブ結果セットのレコード数が比較的少ない限り、ネストループ結合の前提条件が整っています。そして、ドライブ結果セットは、ドライブテーブルに対して指定された目標SQLの述語条件が適用された結果セットです。したがって、大きなテーブルもネストループ結合のドライブテーブルとして使用できます。重要なのは、目標SQLで指定された述語条件が（あれば）ドライブ結果セットのデータ量を減少させるかどうかです。
ネストループ結合には他の結合方法にはない利点があります。それは、ネストループ結合が迅速な応答を実現できることです。すなわち、既に結合されて条件に合致するレコードがあれば、すぐにそれを返し、全ての結合操作が完了するのを待つ必要がありません。ソートマージ結合やハッシュ結合も条件に合致するレコードをすぐに返すことができますが、ネストループ結合が特有の優位性があります。それは、ソートマージ結合はソートの完了後にデータを返し、ハッシュ結合はハッシュテーブルの構築が完了してからデータを返す必要があるためです。
Oracleがネストループ結合を使用し、かつ被駆動テーブルの結合列にインデックスが存在する場合、通常Oracleはインデックスに対して単一ブロックの読み取りを使用します。これはつまり、ネストループ結合のドライブ結果セットに含まれるレコードの数だけ、Oracleがこのインデックスにアクセスする必要があることを意味します。さらに、目標SQLのクエリ列が被駆動テーブルの関連するインデックスから得られない場合、ネストループ結合の後に被駆動テーブルに対してリターン操作を実行する必要があります（つまり、結合結果セットの各レコードに含まれるROWIDを使用して、被駆動テーブルから関連するクエリ列を取得する）。このリターン操作も通常は単一ブロックの読み取りを使用します。したがって、ネストループ結合の後に得られる結合結果セットのレコードの数だけ、Oracleはリターン操作を実行する必要があります。
この種の単一ブロックの読み取りでは、アクセスしようとするインデックスブロックまたはデータブロックがBuffer Cacheに存在しない場合、Oracleは物理I/Oを使用して対応するデータファイルからそれを取得する必要があります。明らかに、単一ブロックの読み取りの数が減少しない限り、これらの単一ブロックの読み取りで必要な物理I/Oの数を減少させることができれば、ネストループ結合の実行効率も向上します。
ネストループ結合の実行効率を向上させるためには、Oracle 11gではベクトルI/O（Vector I/O）が導入されました。ベクトルI/Oが導入されると、Oracleは元々一括の単一ブロックの読み取りで必要だった物理I/Oをまとめて、一括で処理することができます。これにより、単一ブロックの読み取りの数を減少させ、ネストループ結合の実行効率が向上します。
ベクトルI/Oの導入は、ネストループ結合に対応する実行計画にも反映されます。Oracle 11gでは、ネストループ結合が1回の実行で処理できるSQLであっても、実行計画にはネストループ結合の数が以前の1回から2回に変更されていることがあります。
ハッシュ結合
ハッシュ結合は、必ずしもソートされるわけではありません。むしろ、ほとんどの場合、ソートは必要ありません。
ハッシュ結合のドライブテーブルに対応する結合列の選択性は、可能な限り高くするべきです。なぜなら、この選択性が対応するハッシュバケット内のレコード数に影響し、ハッシュバケット内のレコード数がそのままハッシュバケットから一致するレコードを検索する効率に直接影響するからです。ハッシュバケット内のレコード数が多すぎると、対応するハッシュ結合の実行効率が著しく低下する可能性があります。この場合、典型的な症状は、ハッシュ結合が非常に長い時間実行されていないにもかかわらず、データベースサーバのCPU使用率が非常に高いというものです。これは目標のSQLが消費する論理読み取りが非常に低いことからもわかります。なぜなら、ほとんどの時間が上記のハッシュバケット内のすべてのレコードを反復処理するのに費やされ、ハッシュバケット内のレコードの反復処理はPGAのワークエリアで発生するため、論理読み取りが発生しないからです。
ハッシュ結合はCBO（コストベースの最適化）にのみ適用され、等値結合条件の場合にのみ使用できます（ハッシュ反結合であっても、Oracleは実際にはそれを等価な等値結合に変換します）。
ハッシュ結合は、小さなテーブルと大きなテーブルの間での表の結合および結合結果セットのレコード数が多い場合に非常に適しています。特に、小さなテーブルの結合列の選択性が非常に高い場合、ハッシュ結合の実行時間は、大きなテーブルの全体スキャンにかかる時間とほぼ同等と見なすことができます。2つのテーブルがハッシュ結合を行う場合、目標SQLで指定された述語条件が適用された後のデータ量が小さい方の結果セットに対応するハッシュテーブルがメモリ内（PGAのワークエリア内）に完全に収められる場合、ハッシュ結合の実行効率は非常に高くなります。
マージ結合
マージ結合に関しては、クエリに結合条件が漏れているためであり、したがってマージ結合は一般的には好ましくないです。ただし、意図的に行う場合もあります（たとえば、クエリで大きなテーブルの全表スキャン回数を減らすためにマージ結合を利用できる場合があります）。
また、クエリでORDERED Hintを使用し、かつそのSQLのテキスト内で位置が隣接する2つの表に直接の関連条件がない場合があります。
インデックスを適切に利用しない書き方
部分一致検索にLIKEキーワードを使用する場合
-- person_nameはインデックスに設定しました。
SELECT * FROM person WHERE person_name LIKE '%test%';
以下はこのクエリの実行計画です。
Plan hash value: 1493655343
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 213 | 67308 | 2142 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| PERSON | 213 | 67308 | 2142 (1)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("PERSON_NAME" IS NOT NULL AND "PERSON_NAME" LIKE '%test%')
後方一致検索にLIKEキーワードを使用する場合
-- person_nameはインデックスに設定しました。
SELECT * FROM person WHERE person_name LIKE '%test';
以下はこのクエリの実行計画です。
Plan hash value: 1493655343
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 213 | 67308 | 2142 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| PERSON | 213 | 67308 | 2142 (1)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("PERSON_NAME" IS NOT NULL AND "PERSON_NAME" LIKE '%test')
指定列に対し関数を使う場合
以下は person_name 列に対して関数を使用するクエリの実行計画です。この例では、person_name に対してインデックスが設定されています。
-- person_nameはインデックスに設定しました。
SELECT * FROM person WHERE upper(person_name) = 'TEST';
以下はこのクエリの実行計画です。
Plan hash value: 1493655343
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1663 | 513K| 2143 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| PERSON | 1663 | 513K| 2143 (1)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(UPPER("PERSON_NAME")='TEST')
この実行計画では、指定された person_name 列に対する関数 (UPPER) による検索条件が、インデックスを使用せずに全表スキャン (TABLE ACCESS FULL) されています。関数を使用した場合、通常はインデックスが効果的に活用されないため、全表スキャンが選択されることがあります。
インデックスが期待通りに効かないケース
1.低選択度のカラム:
インデックスの効果が低いのは、そのカラムに重複が多い場合です。たとえば、性別などが2つの値しか持たない場合、インデックスを使っても選択の効果が限定的です。
2.大量のNULL値:
インデックスされたカラムに大量のNULL値が存在する場合、インデックスは効果的でない可能性があります。NULL値が多すぎると、インデックスの効果が薄れます。
3.データ量が少ないテーブル:
データが少ないテーブルでは、全体をスキャンするコストが低いため、インデックスを使用するよりもテーブル全体をスキャンした方が効率的な場合があります。
4.複雑なクエリ:
クエリが複雑で、複数の結合や条件が組み合わさっている場合、最適な実行計画が難しくなり、インデックスが効かないことがあります。
5.更新頻度の高いカラム:
インデックスされたカラムが頻繁に更新される場合、更新に伴うインデックスの再構築やメンテナンスがコストを伴うため、インデックスが効かなくなることがあります。
6.クエリの選択項目が多い場合:
クエリが多くのカラムを選択する場合、全ての情報をインデックスから取得するよりも、テーブル全体をスキャンした方が効率的な場合があります。
SQLのパラメータバインディングの利用
パラメータバインディングを使用することで、データベースエンジンがクエリプランを再利用しやすくなります。バインド変数を使用することで、同じクエリが異なる値で実行されても、データベースは一度クエリを解析し、その後の実行で再利用できます。これにより、パフォーマンスが向上します。
SELECT * FROM persons WHERE id = :person_id
上記のSQLクエリでは、:person_idのようなバインド変数を使用しています。これにより、実行時に値をセットして安全かつ効率的にクエリを実行できます。
INとEXISTSのパフォーマンスの違いについて
Oracle 19cにおいて、INとEXISTSのパフォーマンスの差はほぼなくなりました。以前はEXISTSが勧められることがありましたが、最新のバージョンではどちらを使用しても遜色ない結果が得られます。
INを使用したクエリ
SELECT * FROM person A WHERE company_id IN (SELECT company_id FROM company B);
以下はこのクエリの実行計画です。
Plan hash value: 1220630797
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 165K| 50M| 2155 (1)| 00:00:01 |
| 1 | NESTED LOOPS | | 165K| 50M| 2155 (1)| 00:00:01 |
| 2 | TABLE ACCESS FULL| PERSON | 166K| 50M| 2146 (1)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN| SMC_COMPANY | 1 | 5 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("COMPANY_ID"="COMPANY_ID")
EXISTSを使用したクエリ
SELECT * FROM person A WHERE EXISTS (SELECT * FROM company B WHERE B.company_id = A.company_id);
以下はこのクエリの実行計画です。
Plan hash value: 1220630797
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 165K| 50M| 2155 (1)| 00:00:01 |
| 1 | NESTED LOOPS | | 165K| 50M| 2155 (1)| 00:00:01 |
| 2 | TABLE ACCESS FULL| PERSON | 166K| 50M| 2146 (1)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN| SMC_COMPANY | 1 | 5 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("B"."COMPANY_ID"="A"."COMPANY_ID")
ORとUNIONのパフォーマンスの違いについて
Oracle 19cにおいて、ORとUNIONのパフォーマンスの差はほぼなくなりました。以前はUNIONが勧められることがありましたが、最新のバージョンではどちらを使用しても遜色ない結果が得られます。
ORを使用したクエリ
SELECT * FROM person WHERE person_id = 1 OR person_id = 3;
以下はこのクエリの実行計画です。
Plan hash value: 4105939990
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 632 | 4 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| PERSON | 2 | 632 | 4 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | SMC_PERSON | 2 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("PERSON_ID"=1 OR "PERSON_ID"=3)
UNIONを使用したクエリ
SELECT * FROM person WHERE person_id = 1
union
SELECT * FROM person WHERE person_id = 3
以下はこのクエリの実行計画です。
Plan hash value: 2251806743
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 632 | 6 (34)| 00:00:01 |
| 1 | SORT UNIQUE | | 2 | 632 | 6 (34)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| PERSON | 1 | 316 | 2 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | SMC_PERSON | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| PERSON | 1 | 316 | 2 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | SMC_PERSON | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
NULL 値の判定を避けるべき
NULL 値の判定を行う場合、全表スキャン検索になってしまいましたので、パフォーマンスが低下します。
-- person_idはに対し、インデックスを作成しました。
SELECT * FROM person WHERE person_id is null;
以下はこのクエリの実行計画です。
Plan hash value: 3390279236
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 316 | 0 (0)| |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| PERSON | 166K| 50M| 2146 (1)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NULL IS NOT NULL)
最適化方法
NULLを0を設定すれば、インデックスを利用することができます。
SELECT * FROM person WHERE person_id = 0;
以下はこのクエリの実行計画です。
Plan hash value: 3519488248
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 316 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| PERSON | 1 | 316 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SMC_PERSON | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("PERSON_ID"=0)
※文字列のクラムに関しては、ブランクの判定はNULL判定と同様になります。
暗黙変換でもインデックスを利用することができます
-- LAST_CONTACT_DATEはに対し、インデックスを作成しました。
-- LAST_CONTACT_DATEはDATE型になります。
-- 以下のようにクエリを実行する際、 暗黙変換が行われます。
select LAST_CONTACT_DATE from person where LAST_CONTACT_DATE = '2016/10/01';
以下はこのクエリの実行計画です。
Plan hash value: 2881892849
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 21 | 168 | 3 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| INDEX_P_CONTACT_DATE | 21 | 168 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("LAST_CONTACT_DATE"='2016/10/01')
ORDER BY句のパフォーマンス改善
ORDER BY句に関して、一般的にはインデックスが有効に機能しないという誤解がありますが、実際にはそのようなことはありません。ORDER BY句でインデックスが使用される場合、パフォーマンスを向上させることができます。
指定列がインデックスとして設定されていない場合
-- test_tblにあるperson_idカラムに関しては、インデックスが設定されていません。
EXPLAIN PLAN FOR SELECT * FROM test_tbl ORDER BY person_id DESC;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
結果は以下の通りです。
Plan hash value: 307503828
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 159K| 529M| | 117K (1)| 00:00:05 |
| 1 | SORT ORDER BY | | 159K| 529M| 624M| 117K (1)| 00:00:05 |
| 2 | TABLE ACCESS FULL| TEST_TBL | 159K| 529M| | 2295 (1)| 00:00:01 |
---------------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
インデックス設定済の場合
-- test_tblとpersonは構成は同じですが、personテーブルにあるperson_idカラムに関しては、インデックスが設定済です。
explain plan for select * from person order by person_id desc;
select * from table(DBMS_XPLAN.DISPLAY);
結果は以下の通りです。
Plan hash value: 4258456859
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 166K| 50M| | 13397 (1)| 00:00:01 |
| 1 | SORT ORDER BY | | 166K| 50M| 72M| 13397 (1)| 00:00:01 |
| 2 | TABLE ACCESS FULL| PERSON | 166K| 50M| | 2146 (1)| 00:00:01 |
-------------------------------------------------------------------------------------
実行計画にあるBytes、TempSpc、Cost (%CPU)、Timeの項目を比較すると、インデックス設定済の場合はパフォーマンスが優れることがわかります。