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

　「NULL処理構文の理解と効率的なSQLの作成」の５回目の今回は、「IS NULL照会に対する改善方法検索」についてです。

---

７.５　IS NULL照会に対する改善方法検索

　最近では、SMSシステムと連動してDBサーバの特定指標やOS Resourceなどに対して基準値あるいは閾値を設定し、それを超える場合、該当担当者にSMSを送信できるようにすることが多くなっています。 また、これに関連する履歴はテーブルに保存され、いつ、誰に、いかなる理由でメッセージを送信したことを知ることができます。 このようなSMS転送に関連するデータを保持するTN_SNSというテーブルがあります。 このテーブルには、SMS転送が完了したものと完了していないものの両方が格納されます。 そして転送するかどうかを保存するSMS-YN列があります。

・　テーブル:SMS伝送(Table Name:TN_SMS)
・　カラム:SMS伝送チェック(Column Name:SMS_YN)

　一般に、テーブルと列を定義するときにデータ型を設定することを心配していますが、将来の列をどのように使用するかについての部分を軽く考えている場合がよくあります。 したがって、プログラム開発が終了して運用する過程でデータが増加するほど、カラムの属性などについて見落としていた部分がすぐにパフォーマンス問題として現れることで、カラム属性の誤った定義に対する状況を後悔する場合がしばしばあります。

　このような状況を上記のSMS関連業務を利用して説明してみましょう。 TN_SMSテーブルの列のうち、SMSの転送をチェックする列(SMS_YN)をNullableと定義し、SMS転送になっていないデータにはNULLを、転送が完了したデータにはYに更新するプログラムがあると仮定するとき、そのプログラムの持つ性能問題とその解決法について調べてみることにします。

　まず、上記のような業務プロセスを持つテスト例題を作ってみましょう。

- SMS_YNカラムをCHAR(1)そしてNULLABLEで生成

CREATE TABLE TN_SMS (SMS_NO, SMS_ID VARCHAR2(100) NOT NULL, SMS_YN CHAR(1)) ;

-伝送しなければならないデータをテーブルに入力する時SMS_YN(伝送の有無)をNULLで入力

INSERT INTO TN_SMS VALUES (1, 'SHPARK', '') ;

COMMIT ;

- SMS伝送しなければならない対象抽出

SELECT * FROM TN_SMS WHERE SMS_YN IS NULL ;

- SMS伝送後伝送の有無アップデート

UPDATE TN_SMS SET SMS_YN = 'Y'
WHERE SMS_NO = 1 ;

COMMIT;

　このような形式でプログラムが実行されると、ＳＭＳを送信すべき対象を探すプログラムは、ＴＮ＿ＳＭＳテーブルのデータが増えるほど、プログラムの性能が悪化します。 なぜなら、SMS転送テーブルのデータのうち未送信データはごく一部に過ぎないが、対象を照会するためにはSMS_YN IS NULLで照会しなければならないため、インデックスを使用することができず、Full Table Scanでデータを抽出しなければならないからです。

* NULL_T5テーブルは合計100,000件を有している。 データのうちC3カラムがNULLであるデータは合計10件で全体テーブル件数の0.01%です。

SELECT COUNT(*) AS c3_null_cnt,
       (count(*)/100000)*100 AS c3_null_ratio
  FROM null_T5
 WHERE c3 is null ;

C3_NULL_CNT C3_NULL_RATIO
----------- -------------
         10           .01

　C3 IS NULL条件に対するデータと実行計画は下記のとおりです。

SELECT *
  FROM null_T5
 WHERE c3 is null ;
      C1 C2 C3
-------- -- --
    9999  P
   19999  F
   29999  V
   39999  L
   49999  B
   59999  R
   69999  H
   79999  X
   89999  N
   99999  D

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

　C3カラムがIS NULLであるデータは全体データのうち0.01%である合計10件であるからC3カラムがIS NULL条件で照会される場合Full Table Scanよりインデックス スキャンで処理されることが効率的です。 しかし前のSQLのようにC3 IS NULL条件で問い合わせれば、NULL値はインデックス(単一カラム インデックス)に含まれないためにFull Table Scanで処理しなければなりません。 もちろん、結合インデックスで構成された場合IS NULLで照会をしても、効率的なインデックスの使用が可能なのですが、現在SQLの場合はインデックス使用が不可の状態です。

　本格的にIS NULL照会の改善策を探してみましょう。 結果から言えば、以下のように3つの方法で不要なFull Table Scanによる非効率を改善することができます。

・　改善法案1. NVL処理とFUNCTION BASED INDEX生成
・　改善法案2. カラム属性変更(DEFAULT設定)とNULLデータ アップデート
・　改善法案3. カラム追加およびインデックス生成後WHERE節変更

　これからはこの三つ方案に対して詳しく調べてみることにしましょう。

７.５.１　NVL処理とFUNCTION BASED INDEX生成

　この方法は新規インデックス生成とSQL変更が可能な場合に使用することができます。 IS NULLの性能問題改善プロセスは次のとおりです。

- NVLを利用したFunction Based Index生成

CREATE INDEX null_t5_idx_02 ON null_t5 ( NVL(c3,'ISNULL') ) ;

-統計情報収集
exec dbms_stats.gather_table_stats(OWNNAME=>'SCOTT',TABNAME=>'NULL_T5',CASCADE=>TRUE,ESTIMATE_PERCENT=>100)  ;

- WHERE節変更
SELECT *
FROM null_t5
--WHERE c3 IS NULL
WHERE NVL(c3,'ISNULL') = 'ISNULL'

----------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes  |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |     9  |
|   1 |  TABLE ACCESS BY INDEX ROWID| NULL_T5        |     1 |     9  |
|*  2 |   INDEX RANGE SCAN          | NULL_T5_IDX_02 |     1 |        |
----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(NVL("C3",'ISNULL')='ISNULL')

　C3カラムに対してNVL(C3,’ISNULL’)を実行した値を基にFunction Based Indexを生成し、IS NULL比較条件はNVL(C3,’ISNULL’) = ‘ISNULL’に変更して効率的なインデックス スキャン方式で実行されました。

７.５.２　カラム属性変更(DEFAULT設定)とNULLデータ アップデート

　この方法では、テーブルの列の属性を変更し、テーブル全体のデータを更新する必要があるという負担があります。 実業務環境で使用するテーブルの件数が多ければ適用しにくい改善案となります。 しかし、まだ開発中なら、以下のように変更することも考慮してみるべきです。

- C3カラムにDEFAULT VALUE設定するためにALTER TABLE実行

ALTER TABLE null_t5 MODIFY (c3 char(1) default 'N') ;

- T_NULLテーブルに現在の存在するC3 IS NULLであるデータを'N'でアップデート実行
UPDATE null_t5
SET c3 = 'N'
WHERE c3 IS NULL ;
COMMIT ;

-統計情報収集
exec dbms_stats.gather_table_stats(OWNNAME=>'SCOTT',TABNAME=>'NULL_T5',CASCADE=>TRUE,ESTIMATE_PERCENT=>100)  ;

- WHERE節変更

SELECT *
FROM null_t5
--WHERE c3 IS NULL
WHERE c3 = 'N'

----------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |     9 |
|   1 |  TABLE ACCESS BY INDEX ROWID| NULL_T5        |     1 |     9 |
|*  2 |   INDEX RANGE SCAN          | NULL_T5_IDX_01 |     1 |       |
----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C3"='N')

７.５.３　カラム追加およびインデックス生成後WHERE節変更

　この方法は結合インデックスの特徴を利用することです。 NULL比較がインデックスを使用できない理由はインデックスがNULLデータを保存しないという特徴のためです。 しかし、結合インデックスの場合、先頭カラムがNOT NULL性格を持つカラムならば、Nullable性格のインデックス カラムのNULLデータまで保存されることになります。 NULLデータもインデックスに存在するのでIS NULLも、インデックス使用ができるようになります。 理解を助けるためにデータを使用して説明してみるようにします。

インデックス構成(単一カラム インデックス:カラムA)

・　カラムAの値:NULL (NULL値であるからインデックスに保存されない。)

インデックス構成(結合インデックス:カラムB、カラムA)

・　カラムBとAの値:1,NULL (BカラムまでNULLの場合、インデックスに保存されませんが、B値がNULLでない値を有しているのでインデックスに保存されなければなりません。 すなわち、Aは同じようにNULLですがBと結合インデックスになる場合、Bカラムの値にNULLがないならばAはNULLでもNULLではなくても関係なく常にインデックスに保存になります。）

　NOT NULL制約があるカラムを結合インデックスに含む場合、NULL比較条件がインデックスを使用できるのかテストをしてみることにします。

- C4新規カラム追加
:既存カラムのうち、Nullがなくて、任意に条件を追加しても値が変わらないカラムがすでに
存在するならば、必ず新規カラムを作る必要はない。

ALTER TABLE null_t5 ADD (c4 CHAR(3) DEFAULT 'ALL') ;

-既存インデックス変更(C3 -> C4,C3)

DROP INDEX null_t5_idx_01 ;

CREATE INDEX null_t5_idx_01 ON null_t5 (c4, c3) ;

select *
  FROM null_t5
 where c3 is null
   AND c4 = 'ALL' ;


----------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes  |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |    10  |
|   1 |  TABLE ACCESS BY INDEX ROWID| NULL_T5        |     1 |    10  |
|*  2 |   INDEX RANGE SCAN          | NULL_T5_IDX_01 |     1 |        |
----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C4"='ALL' AND "C3" IS NULL)

　テーブル設計時C3カラムに対してDefault値をNに設定してカラムの属性をNOT NULL属性で生成することにすれば、上のようにカラムを追加したり結合インデックスを生成しなくても効率的な処理ができでしょう。

　テーブルを作成するときに属性を明確に定義するのが最善ですが、すでに列の属性を正しく設定していないため、IS NULL照会でパフォーマンスの問題が発生することがよくあります。 そのような場合には、前から改善案に提案した、改善案１、２、３を適切に適用して性能問題を解決できることを望みます。

---

　今回は、ここまでになります。次回は、「IS NOT NULL照会に対する改善方法検索」についてになります。それでは、see you ^^