Определим используется ли значение 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 значения в индекс.
Сообщения
