Немного о null и индексах в разных СУБД

Определим используется ли значение null в индексах btree в различных СУБД. Для тестирования возьмем три разные базы: Informix, DB2 и Oracle.

В каждой базе создается таблица в которой в индексированном поле 30% значений null.
create table t1 (f1 int);
Таблица наполняется данными (зависит от базы, ниже пример для Oracle):
insert into t1 select 1 from dual connect by level <=30;
insert into t1 select null from dual connect by level <=70;
Строится индекс по таблице:
create index idx_t1 on t1(f1);

И собирается статистика для оптимизатора.
Запускается запрос select * from t1 where f1 is null; с поиском по этому полю условие is null. Проверяем используется ли индекс.

Оракл версия 10gR2  ( для получения плана запроса используем set autotrace on) :

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    30 |    60 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |    30 |    60 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("F1" IS NULL)

TABLE ACCESS FULL т.е. в случае когда делается поиск по значениям null, делается полный скан таблицы.
в индексах b-tree значения null не включается.


Informix версия 11.70UC2 (для получения плана запроса используем set explain on):

select * from t1 where f1 is null;

Estimated Cost: 2
Estimated # of Rows Returned: 30

  1) informix.t1: INDEX PATH

    (1) Index Name: informix.idx_t1
        Index Keys: f1   (Key-Only)  (Serial, fragments: ALL)
        Lower Index Filter: informix.t1.f1 IS NULL

Индекс используется. В индексах b-tree включается значение null



DB2 версия v9.7.0.2 (для получения плана запроса используем db2expln) :

Statement:

  select *
  from db2inst1.t1
  where f1 is null

Access Table Name = DB2INST1.T1  ID = 3,5
|  Index Scan:  Name = DB2INST1.IDX_T1  ID = 1
|  |  Regular Index (Not Clustered)
|  |  Index Columns:
|  |  |  1: F1 (Ascending)
|  #Columns = 1
|  Skip Inserted Rows
|  Avoid Locking Committed Data
|  Currently Committed for Cursor Stability
|  #Key Columns = 1
|  |  Start Key: Inclusive Value
|  |  |  |  1: NULL
|  |  Stop Key: Inclusive Value
|  |  |  |  1: NULL
|  Index-Only Access
|  Index Prefetch: None
|  Lock Intents
|  |  Table: Intent Share
|  |  Row  : Next Key Share
|  Sargable Index Predicate(s)
|  |  Return Data to Application
|  |  |  #Columns = 1
Return Data Completion

В плане видим что также используется индекс (Index-Only Access), т.е. null значения включаются в индекс.


Т.о. из трех рассматриваемых СУБД только Оракл не включает значения null в индекс, Informix и DB2 наоборот согласно данным оптимизатора, включают null значения в индекс.