catch-img

Oracle SQLチューニング（第26回）「NULL処理構文の理解と効率的なSQLの作成」（2／７回）

Oracle技術情報

Oracle SQLチューニング

　今回は、「NULL処理構文の理解と効率的なSQLの作成」の二回目として「NVLの活用」というテーマでお届けします。それでは、始めましょう。

７.２　NVLの活用

７.２.１　実行計画の分離

　普通”C3 LIKE:B1 ||’%'”のような照会は:B1値によりC3 =:B1 (:B1 IS NOT NULL)やC3 IS NOT NULL (:B1 IS NULL)のデータを問い合わせるために作成する場合が多句あります。 このようにBind値により抽出結果が変わらなければならない場合には一般的にNVL,LIKE,DECODEを利用してSQLを作成します。 その中に最も多く利用される方法がLIKEです。 しかし、NVL,LIKE,DECODE中の何を使ってSQLを作成するかによりSQLの性能差が現れることになります。 このような性能差がなぜ発生するのか下のテスト結果を通じて確認してみることにしましょう。

SQL[1]. Likeを利用したSQL作成時性能

SELECT *
  FROM NULL_T
 WHERE c3 LIKE :b1 ||'%'

---------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |  5000 |   126K|     8   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID| NULL_T       |  5000 |   126K|     8   (0)|
|*  2 |   INDEX RANGE SCAN          | NULL_T_IDX02 |   810 |       |     4   (0)|
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C3" LIKE :B1||'%')
       filter("C3" LIKE :B1||'%')

SQL[2]. NVLを利用したSQL作成法

SELECT *
  FROM NULL_T
WHERE c3 = NVL(:b1,c3)

-----------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              | 90001 |  2285K|   113   (1)|
|   1 |  CONCATENATION                |              |       |       |            |
|*  2 |   FILTER                      |              |       |       |            |
|*  3 |    TABLE ACCESS FULL          | NULL_T       | 90000 |  2285K|   111   (1)|
|*  4 |   FILTER                      |              |       |       |            |
|   5 |    TABLE ACCESS BY INDEX ROWID| NULL_T       |     1 |    26 |     2   (0)|
|*  6 |     INDEX RANGE SCAN          | NULL_T_IDX02 |     1 |       |     1   (0)|
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(:B1 IS NULL)
   3 - filter("C3" IS NOT NULL)
   4 - filter(:B1 IS NOT NULL)
   6 - access("C3"=:B1)

SQL[3]. DECODEを利用したSQL作成法

SELECT *
  FROM null_t
 WHERE c3 = DECODE(:b1,NULL,c3,:b1)

-----------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              | 90002 |  2109K|   115   (1)|
|   1 |  CONCATENATION                |              |       |       |            |
|*  2 |   FILTER                      |              |       |       |            |
|*  3 |    TABLE ACCESS FULL          | NULL_T       | 90000 |  2109K|   111   (1)|
|*  4 |   FILTER                      |              |       |       |            |
|   5 |    TABLE ACCESS BY INDEX ROWID| NULL_T       |     2 |    48 |     4   (0)|
|*  6 |     INDEX RANGE SCAN          | NULL_T_IDX02 |     2 |       |     1   (0)|
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(:B1 IS NULL)
   3 - filter("C3" IS NOT NULL)
   4 - filter(:B1 IS NOT NULL)
   6 - access("C3"=:B1)


　SQL[1] ～ SQL[3]の実行計画を調べれば、NVL,LIKE,DECODEでそれぞれ作成したSQL中NVL,DECODEを活用したSQLは:B1のNULLの有無により実行計画が分かれます。 しかし、LIKEの場合はインデックス スキャンで処理されますが、:B1の値がNULLの場合C3 LIKE ‘%’で実行されますのでSQL性能が急激に落ちることになります。 このような場合NVLをうまく活用すればSQLの性能を改善することができます。 カラムを問い合わせる変数値の範囲や値の有無にともなう実行計画分岐に対する詳しい内容は“照会パターンに合うようにSQL実行計画分岐すること”で詳しく調べてみることにしましょう。

７.２.２　IS NULL照会の改善

　テーブル設計時にカラムのNULLable制約が非常に重要な部分を占める場合があります。 一つの例を見てみましょう。 Interface (Temp) 性格のテーブルに生成された特定の Nullable カラムが Interface 処理かどうかを管理（状態）するカラムとしましょう。 このカラムのデータは、INSERT時に処理前を意味するNULLを入力し、処理された後に完了するかどうかをUPDATEするとします。 このような場合、未処理のデータを抽出するにはIS NULLと照会され、時間が経過してNULLデータがすべて処理された後はNOT NULLのデータのみが存在するはずです。 ところが、新規INSERTされたNULLデータがごく一部であっても、IS NULL条件はインデックスが存在しても使用できず、Full Table Scanで行うことになります。

SELECT *
  FROM T_NULL
 WHERE c3 IS NULL

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |    19 |   152 |    56   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T_NULL |    19 |   152 |    56   (2)| 00:00:01 |
----------------------------------------------------------------------------


　前述のSQLを確認してみると、照会条件がC3 IS NULLで照会され、C3列に効率的なインデックスがあってもFull Table Scanで行われます。 表に新規索引の作成とSQL変更のみが可能な場合、そのSQLに対するパフォーマンス改善作業は難しく見えますが、方法がないわけではありません。

　解決の糸口はNVLとOracleで提供するFunction Based Index （注１）を活用することです。 NVLを利用してNULLデータを特定値で置き換えて、その値で新規インデックスを生成すれば望むままFull Table Scanによる非効率を解消することができます。 詳しい内容は” IS NULL照会に対する改善方法検索”を参照してください。

（注１）FBI（Function Based Index）とは、関数を実行したり、演算を処理した結果に対して仮想カラムで生成したインデックスを言います。 一般に、Where句でカラムの操作や関数を使用してインデックスを使用できない場合は、FBIを使用してパフォーマンスを向上させることができます。

　上記のように既存のC3カラム値にNVLを適用し、その結果を仮想カラムに保存し、新しい仮想カラムにインデックスを生成してNVL（c3、「ISNULL」）=「ISNULL」という形で比較するとインデックスが使用でき、効率的な実行となります。


　今回は、ここまでとなります。次回は、「グループ関数(COUNT,SUM,MAX,MIN)とNVL処理」と題してお送りします。ご期待機ださい。では、see you ^^


前の記事

prev-article-image

Oracle SQLチューニング（第25回）「NULL処理構文の理解と効率的なSQLの作成」（１／７回）

次の記事

next-article-image

Oracle SQLチューニング（第27回）「NULL処理構文の理解と効率的なSQLの作成」（3／７回）

CONTACT

他社に頼らず自社でデータベースを監視・運用をしませんか？
MaxGaugeがサポートします

お役立ち資料は
こちらから

資料ダウンロード

不明点がある方は、
こちらからお問い合わせください

お問い合わせ

お電話でのお問い合わせはこちら

03-3518-0771

平日 10時～18時

人気記事ランキング

タグ一覧

logo_img
ページトップへ戻る

©EXEM JAPAN Corp. All Rights Reserved.