索引を作成したのにパフォーマンスが悪いケース：Oracle SQLチューニング講座（7）（1/3 ページ）
本連載では、Oracleデータベースのパフォーマンス・チューニングの中から、特にSQLのチューニングに注目して、実践レベルの手法を解説する。読者はOracleデータベースのアーキテクチャを理解し、運用管理の実務経験を積んでいることが望ましい。対象とするバージョンは現状で広く使われているOracle9iの機能を基本とするが、Oracle 10gで有効な情報も随時紹介していく。（編集局）
前回の「パフォーマンスを向上させるSQLの記述法」では、SQLの記述方法を統一することのメリットについて説明しました。SQLの処理ステップを理解し、記述方法を統一することがパフォーマンスへも大きく影響することが理解できたと思います。今回は、索引を使用したデータアクセス方法に絞って、索引を使用するためのSQLの記述方法や、索引を使用した検索時の注意点について説明します。
索引を使用できないケース
「索引を作成してあるのに処理が遅い」「チューニングのために索引を追加したが処理時間が変わらない」といったケースは非常に多く見受けられます。原因はいくつも考えられますが、最も多いのは「せっかく作成してある索引を使用できていない」ケースです。これらは、SQLの記述方法や索引に対する理解不足により引き起こされています。それでは、具体的な例を用いて、索引が使用されないSQLについて説明していきます。
NULL値の検索
「supplier」表の「s_suppkey」列に索引が作成されているとして、図1のようなSQL文を実行し、SQLトレース、TKPROFユーティリティを使用して実行計画を取得します。
図1の実行計画の（1）から、全表スキャンが実行されており、「s_suppkey」列に作成されている索引が使用されていないことが確認できます。OracleではB*Tree索引にNULL値のデータを含まないため、IS
NULL検索では、索引を使用することができません。従って、検索条件列に索引が作成されていても全表スキャンとなってしまいます。
これらのデータを頻繁に検索する必要があり、パフォーマンスが要求される場合には、
- NULLを特定の値に置き換える
- ビットマップ索引を使用する
のような方法が考えられます注1。
注1
NULL値を特定の値に置き換える場合には、既存のアプリケーションへの影響なども考慮する必要があります。ビットマップ索引は構造上、データが頻繁に変更される列には適していません。テーブルが参照主体で使用される場合に使用を検討します。
例えば、NULL値の代わりにシステム上あり得ない値として「-1」をセットした場合、図2のように索引スキャンが行われます。また、ビットマップ作成はNULL値のデータを含むため、検索条件にIS
NULLが指定されていても、図3のように索引スキャンを実行することができます。図3はビットマップ索引を使用したときのSQLトレース、TKPROFユーティリティの結果になります。（1）と（2）からビットマップ索引を使用し、ROWIDへの変換が行われて検索されていることが分かります。
そのほかに索引が使用されないケース
代表的な例としてNULL値の検索を例に挙げましたが、条件列に索引が作成されていてもオプティマイザが索引スキャンを選択できないケースには、以下のようなものがあります。索引使用の有無はパフォーマンスに大きく影響するため、SQLを記述する際はこれらの基本をしっかりと覚えておく必要があります。
| ケース | SQLの例 | 対処方法 |
|---|---|---|
| NULL値の検索 | ・列名 IS NULL | ‐NULL値を別のデータに置き換える ‐ビットマップ索引を使用する |
| 暗黙の型変換 | ・CHAR列 = 1 ・VARCHAR2列 = 1 |
‐比較するデータ型を列のデータ型に合わせる CHAR列 = '1' CHAR列 = TO_CHAR(1) ‐INDEXヒントを使用する（索引列にNOT NULL制約が必要） |
| 索引列に対して、関数や算術を実施 | ・VARCHAR2列||'様' = '斉藤様' ・NUMBER列 * 20 = 10000 ・substr(VARCHAR2列,1,2) = 'AB' |
‐関数、演算を右辺（索引列でない方）に移動する ・VARCHAR2列 = '斉藤' ・NUMBER列 = 10000/20 ・VARCHAR2列 LIKE 'AB%' ‐関数索引を使用する（Oracle 9i以上で使用可能） ［例］ CREATE INDEX SUBSTR_IDX ON SUPPLIER( SUBSTR(列名,4,6) ); ‐INDEXヒントを使用する（索引列にNOT NULL制約が必要） |
| LIKEの中間一致、後方一致 | ・列名 LIKE'%TEST%' ・列名 LIKE'%TEST' |
‐INDEXヒントを使用する（索引列にNOT NULL制約が必要） |
| !=、の使用 （Not Equals） |
・列名 != '1' ・列名 '1' |
‐inで置き換える（可能な場合） ・列名 in ('2','3') 注）inで指定できる最大リスト数は1000個 ‐INDEXヒントを使用する（索引列にNOT NULL制約が必要） |
| 表1 索引を使用できないSQLと対処方法 | ||
Copyright © ITmedia, Inc. All Rights Reserved.