OracleのB*Tree索引にはNULLが含まれる場合があるんです！ - その性質を使ってチューニングすることもあるよ:) その２ Tweet

OracleのB*Tree索引にはNULLが含まれる場合があるんです！ - その性質を使ってチューニングすることもあるよ:)
の続きです。

念のために、もう一つの主役NULL登場してもらいましょう。

IX1_TAB01索引はNullableなFOO列だけの索引なので、このタイプの索引ではNULLは索引に格納されることはありません。
いくらヒントで索引を指定しても索引にはNULLは格納されていないので全表走査になるはず。

注）：索引が多いので意図した索引を利用するようにヒントで固定しています
例２）FOO IS NULL　で検索

orcl@SCOTT> r
  1  SELECT
  2  	/*+
  3  		gather_plan_statistics
  4  		index(tab01 ix1_tab01)
  5  		no_index(tab01 ix3_tab01)
  6  		no_index(tab01 ix4_tab01)
  7  	*/
  8  	*
  9  FROM
 10  	tab01
 11  WHERE
 12* 	foo IS NULL


       FOO        BAR HO         ID
---------- ---------- -- ----------
[NULL]     [NULL]     **          1
[NULL]              1 **          3


ーーー中略ーーー
Plan hash value: 2044041692

-------------------------------------------------------------------------------------
| Id  | Operation         | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |      1 |        |      2 |00:00:00.01 |       8 |
|*  1 |  TABLE ACCESS FULL| TAB01 |      1 |      2 |      2 |00:00:00.01 |       8 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("FOO" IS NULL)

想定通り。NULLは格納されていないので索引が利用できず全表走査になっています。
索引からROWIDを取得できないので全表走査してフィルタリングしている箇所がポイントです。

OracleのB*Tree索引にはNULLが入らない都市伝説の始まりはここだったんじゃないか？　とい言っている方がいたのですが、ここだけの話が広まってしまい

OracleのB*Tree索引にはNULLは絶対格納されない。

という都市伝説になってしまったのだろうと。。確かに入ってないですからねー。 (^^;;;

では、NULLが索引に含まれる一例を見てみましょう。

例３）ID列（NOT NULL)とFOO列（Nullable）で作成した複合索引を ID=1 AND FOO IS NULLで検索
注）：索引が多いので意図した索引を利用するようにヒントで固定しています

Predicate Informationセクションのaccess predicateを見るとわかると思いますが、ID=1 AND FOO IS NULLで索引をアクセスしています。
NULLが索引に含まれていない場合はこのような状況にはなりません。　
複合索引では1列でもNOT NULL制約がり他の列がNullableである場合、NULLは格納されます。

NULLは格納されることもあるんです。マニュアル上の表現は少々わかりにくいとは思いますが、このような状況を指しています。

orcl@SCOTT> r
  1  SELECT
  2  	/*+
  3  		gather_plan_statistics
  4  		no_index(tab01 ix1_tab01)
  5  		index(tab01 ix3_tab01)
  6  		no_index(tab01 ix4_tab01)
  7  	*/
  8  	*
  9  FROM
 10  	tab01
 11  WHERE
 12  	id = 1
 13* 	AND foo IS NULL

       FOO        BAR HO         ID
---------- ---------- -- ----------
[NULL]     [NULL]     **          1

ーーー中略ーーー
Plan hash value: 2558346564

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |           |      1 |        |      1 |00:00:00.01 |       3 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TAB01     |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  2 |   INDEX RANGE SCAN                  | IX3_TAB01 |      1 |      1 |      1 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=1 AND "FOO" IS NULL)

少し寄り道。

そういえば、偶になんですが、IS NOT NULLだと索引使うことはないという都市伝説も聞いたことがあります。
それも都市伝説なんですよね。
次の例は、その都市伝説を覆す一例を。

例４）FOO IS NOT NULL　で検索

IX1_TAB01索引は、NullableなFOO列だけの索引です。これまでの検証でこのタイプの索引にはNULLが格納されていないことは確認できると思います。
ようするに、NULLじゃないものは、まるっととズバッと索引に含まれているはずでっす！
なので、FOO IS NOT NULLという検索条件だと索引を使ってくれます。

このような性質を知っているとチューニングに役立つんですよ。
（この例のようなチューニング方法を使ったことは過去数度あるんですよ、苦肉の策でしたけどw　NULLの数が非常に多い場合、この手の索引のセグメントサイズは非常に小さくなります(索引に格納されるエントリそのものがNULLを除くと非常に少ない場合）。その性質を利用したチューニング法もあるんです。）

余談NOTE:
INDEX FAST FULL SCANにならない理由:索引にNOT NULL制約の列が少なくとも１列あること、という前提条件を満たせていないからです。チューニングや設計時に必要な知識なので覚えておくと何かの時に役に立つと思います：）

注）：索引が多いので意図した索引を利用するようにヒントで固定しています

orcl@SCOTT> r
  1  SELECT
  2  	/*+
  3  		gather_plan_statistics
  4  		index(tab01 ix1_tab01)
  5  		no_index(tab01 ix3_tab01)
  6  		no_index(tab01 ix4_tab01)
  7  	*/
  8  	*
  9  FROM
 10  	tab01
 11  WHERE
 12* 	foo IS NOT NULL


       FOO        BAR HO         ID
---------- ---------- -- ----------
         1 [NULL]     **          2
         1          1 **          4

ーーー中略ーーー
Plan hash value: 2840602802

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |           |      1 |        |      2 |00:00:00.01 |       4 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TAB01     |      1 |      2 |      2 |00:00:00.01 |       4 |
|*  2 |   INDEX FULL SCAN                   | IX1_TAB01 |      1 |      2 |      2 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("FOO" IS NOT NULL)

恐竜博とか＊＊博ってやつは、最後のショップがメインで博のほうはおまけなんじゃないかと最近思ってるw　子供達の目が活き活きしているのはショップに入ってからだ！w

次回へつづく。