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

先日、OracleのB*Tree索引には絶対にNULLが含まれないって思い込んでる人が意外にいるよね〜とか。
OracleのB*Tree索引には絶対NULLが含まれないって都市伝説があるのはなんでだろう。
Oracle® Database概要12cリリース1 (12.1) - 一意索引と非一意索引

みたいなことが話題になって、
ある一人が、「だよね〜、ダンプ見れば含まれてるのわかります。」って言ってて、それ、ふつ〜の人は見ないからw

と思いつつ、私の周りには、やはり、変態が多いことに改めて気づいた次第です。：）　はい。

で、
変態じゃない、ごく一般的なエンジニアの方々（ブロックダンプを華麗かつ自然にキメちゃわない方々）向けに、

OracleのB*Tree索引にNULLが含まれているか、NULLが含まれていないかの簡単な確認方法をお伝えしなければ！w

ということで、数回にわけて書いておこうかと思ってます。（予定は未定w）

（実は、このネタとほぼ同じことを１年前ぐらい前に、ローカルかつクローズドな勉強会？、でも使ってました。　最近、やってないみたいですけど）

環境は最近の定番　Oracle Database 12c R1 EE

orcl@SCOTT> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE	12.1.0.2.0	Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

SCOTTスキーマに以下のような表と索引を作成し少ないですが、データを登録
今回の主役は索引とNULLなのでデータ量少なくても必要なパターンが登録できていれば十分です。
（データ量を増やせばチューニングのお題の元ネタにもなると思います。）

orcl@SCOTT> create table tab01 (foo number, bar number, hoge char(2) not null,id number not null);

Table created.

orcl@SCOTT> insert into tab01 values(null,null,'**',1);

1 row created.

orcl@SCOTT> insert into tab01 values(1,null,'**',2);

1 row created.

orcl@SCOTT> insert into tab01 values(null,1,'**',3);

1 row created.

orcl@SCOTT> insert into tab01 values(1,1,'**',4);

1 row created.

orcl@SCOTT> commit;

Commit complete.

orcl@SCOTT> alter table tab01 add constraint pk_tab01 primary key(id) using index;

Table altered.

orcl@SCOTT> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'TAB01',cascade=>true,no_invalidate=>false);

PL/SQL procedure successfully completed.

orcl@SCOTT> create index ix1_tab01 on tab01 (foo);

Index created.

orcl@SCOTT> create index ix2_tab01 on tab01 (bar,foo);

Index created.

orcl@SCOTT> create index ix3_tab01 on tab01(id,foo);

Index created.

orcl@SCOTT> create index ix4_tab01 on tab01(id,bar,foo);

Index created.

FOOとBAR列はNullableにしています

orcl@SCOTT> desc tab01
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FOO                                                NUMBER
 BAR                                                NUMBER
 HOGE                                      NOT NULL CHAR(2)
 ID                                        NOT NULL NUMBER

主演の索引たち

NULLが索引でどう扱われるかを確認するため、事前作成の索引をたくさん用意してしました。後から作るの面倒なのでw
これだけ類似索引も含めて多数の索引があると確認時に意図した索引が利用されない可能性も高いため、検証では内容に合わせて利用する索引をヒントで指定することにします。

ちなみに、
普通の環境でこんなに索引があったら、アンチパターン：インデックスショットガンですからね。ご注意ください:) たまにこのような環境に遭遇することはありますが。。。

IX1_TAB01はnullableなFOO列だけの単一列索引
IX2_TAB01はnullableなBAR列とFOO列からなる複合索引
IX3_TAB01とTX4_TAB01は上記の列に加え主キー列のID列を第1キーとする複合索引

としてあります。

orcl@SCOTT> break on table_name on index_name skip 1
orcl@SCOTT> select table_name,index_name,column_name from user_ind_columns where table_name='TAB01' order by table_name,index_name,column_position;

TABLE_NAME                     INDEX_NAME                     COLUMN_NAME
------------------------------ ------------------------------ ------------------------------
TAB01                          IX1_TAB01                      FOO

                               IX2_TAB01                      BAR
                                                              FOO

                               IX3_TAB01                      ID
                                                              FOO

                               IX4_TAB01                      ID
                                                              BAR
                                                              FOO

                               PK_TAB01                       ID

登録したデータは以下の通り
NULLは索引とともに今回の主役なので、どこがNULLになっているかメモしておいてくださいませ。

orcl@SCOTT> set null [NULL]
orcl@SCOTT> select * from tab01 order by id;

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

さて、索引にNULLが含まれているか、いないか、どうやって確認すると思います？　ブロックダンプを華麗にキメきめる以外の方法でw

SQLチューニングをしたことがある方なら一般的に利用してい（と思ってる）機能で簡単に確認できちゃうんですよ。これが。

SQL*Plusのautotraceや、explain plan それに、DBMS_XPLAN.DISPLAY*　なファンクションでも確認できます。
SQLclはautotraceにはまだ未対応となっているようですが、それ以外の方法ならはできるようです。
（今回はSQL*Plusを使い、dbms_xplan.display_cursor(format=>'ALLSTATS LAST'))で確認します）

どうやって、どの部分で確認するのか？　
autottraceやDBMS_XPLAN.DISPLAY*などでリストされるpredicate information部分のaccess/filter predicate部分で確認できるんです！

例１）FOO = 1　で検索した例

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

おわかりでしょうか？
Predicate Infomation部分から、Id=2のINDEX RANGE SCANで　IX1_TAB01索引を　FOO=1でアクセスしていることが読み取れますよね？
つまり、1 という値が索引に含まれている（含まれる）からaccess pathとしてIX1_TAB01索引を参照しているわけです。

orcl@SCOTT> r
  1  SELECT
  2  	/*+
  3  		gather_plan_statistics
  4  		index(tab01 ix1_tab01)
  5  	*/
  6  	*
  7  FROM
  8  	tab01
  9  WHERE
 10* 	foo = 1

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

orcl@SCOTT> select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));

ーーー中略ーーー

Plan hash value: 3795960549

-----------------------------------------------------------------------------------------------------------
| 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 RANGE SCAN                  | IX1_TAB01 |      1 |      2 |      2 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------------------

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

   2 - access("FOO"=1)

ということで、今日は馬事公苑の散歩は気持ちいいよ〜。

次回へつづく。