Oracle索引能否存储NULL值？询问周围的朋友，基本都认为不能存储NULL值，但也有一些持反对意见的人，认为其他数据库索引能存储NULL值，为什么ORACLE不可以？那到底实际情况什么样子？无论哪种观点，总应该找权威的观点来做理论支持，不能人云亦云(做技术的人就是这么较劲，来不得半点马虎)。在翻阅了大量资料后，搜集到相关内容如下：
1.B*Tree indexes, except in the special case of cluster B*Tree indexes, do not store completely Null entries, but bitmap and cluster indexes do.--《Expert one on Oracle》
2. Columns with many duplicate values or many rows withNULLvalues should not be included or should be the last-named columns in the index definition. --Oracle Online Documention.
对上面两条内容合并一下可以得出结论：B树索引可以存储NULL值，但不允许索引字段全部为NULL，若索引字段全部为NULL，索引不会存储。
下面我们通过实验进行说明。
Oracle版本信息：
SELECT * FROM v$version WHERE rownum=1;
BANNER
-------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
1.create测试表，并插入相关数据
drop table t purge;
create table t (id number,data varchar2(100));
insert into t values(1,'a');
insert into t values(2,'b');
insert into t values(3,'c');
insert into t values(100,'aaa');
insert into t values(103,'ccc');
commit;
2.创建索引
create index t_ind_id on t(id);
3.分析索引
通过analyze index分析索引
analyze index t_ind_id validate structure;
select name, lf_rows from index_stats;
NAME LF_ROWS
------------------------------ ----------
T_IND_ID 5
进一步treedump索引层次结构
select OBJECT_ID,DATA_OBJECT_ID from dba_objects where owner='SCOTT' and OBJECT_NAME='T_IND_ID';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
17477 17477
alter session set events 'immediate trace name treedump level 17477';
select tracefile from v$process where addr=(select paddr from v$session where sid=(select distinct sid from v$mystat));
输出结果如下：
*** 2017-02-22 03:42:20.194
----- begin tree dump
leaf: 0x1002823 16787491 (0: nrow: 5 rrow: 5)
----- end tree dump
通过trace文件可以看到索引共记录了5个条目，其中0x1002823为索引的16进制地址,16787491对应索引的10进制地址。
将地址转化为具体的数据文件和块
SELECT dbms_utility.data_block_address_file(16787491),dbms_utility.data_block_address_block(16787491) FROM dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(16787491) DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(16787491)
---------------------------------------------- -----------------------------------------------
4 10275
通过命令将该块的内容转储出来
alter system dump datafile 4 block 10275;
Leaf block dump
===============
header address 139905684912740=0x7f3e54a6ba64
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 5
kdxcofbo 46=0x2e
kdxcofeo 7971=0x1f23
kdxcoavs 7925
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
row#0[8020] flag: ------, lock: 2, len=12
col 0; len 2; (2): c1 02
col 1; len 6; (6): 01 00 24 1c 00 00
row#1[7971] flag: ------, lock: 2, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 01 00 24 1c 00 04
row#2[8008] flag: ------, lock: 2, len=12
col 0; len 2; (2): c1 04
col 1; len 6; (6): 01 00 24 1c 00 01
row#3[7996] flag: ------, lock: 2, len=12
col 0; len 2; (2): c2 02
col 1; len 6; (6): 01 00 24 1c 00 02
row#4[7983] flag: ------, lock: 2, len=13
col 0; len 3; (3): c2 02 04
col 1; len 6; (6): 01 00 24 1c 00 03
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 10275 maxblk 10275
其中col 0--索引的键值，col1--rowid(对于叶子节点来说)，通过dbms_stats.convert_raw_value转换可以确认和实际情况一样
declare n number;
begin
dbms_stats.convert_raw_value('c102',n);
dbms_output.put_line('c102--->' || n);
dbms_stats.convert_raw_value('c103',n);
dbms_output.put_line('c103--->' || n);
dbms_stats.convert_raw_value('c104',n);
dbms_output.put_line('c104--->' || n);
dbms_stats.convert_raw_value('c202',n);
dbms_output.put_line('c202--->' || n);
dbms_stats.convert_raw_value('c20204',n);
dbms_output.put_line('c20204--->' || n);
end;
/
c102--->1
c103--->2
c104--->3
c202--->100
c20204--->103
4.上面啰啰嗦嗦写了那么多，只为证明一个问题：索引准确存储了5个记录的相关索引键值。下面进入正题
插入几条id为空的记录，再看看索引的情况
insert into t(data) values('d');
insert into t(data) values('eeeee');
insert into t(data) values('ffffff');
commit;
通过analyze index分析索引
analyze index t_ind_id validate structure;
select name, lf_rows from index_stats;
NAME LF_ROWS
------------------------------ ----------
T_IND_ID 5
再次treedump索引层次结构
alter session set events 'immediate trace name treedump level 17477';
select tracefile from v$process where addr=(select paddr from v$session where sid=(select distinct sid from v$mystat));
*** 2017-02-22 03:59:08.032
leaf: 0x1002823 16787491 (0: nrow: 5 rrow: 5)
----- end tree dump
细心的你发现什么了吗？咦，为什么我刚刚插入了3条记录，索引的条目并没有增长呢？B树索引可以存储NULL值，但不允许索引字段全部为NULL，若索引字段全部为NULL，索引不会存储。 因为该索引为单列索引，且插入的数据索引字段为NULL，等于整个索引键值为NULL，不会保存，因此索引条目没有增加。
趁热打铁，我们再创建一个复合索引的例子来印证刚才的理论。
1.create测试表，并插入相关数据
drop table t1 purge;
create table t1 (id number,data varchar2(100));
insert into t1 values(1,'a');
commit;
2.创建索引
create index t1_ind_id on t1(id,data);
3.分析索引
通过analyze index分析索引
analyze index t1_ind_id validate structure;
select name, lf_rows from index_stats;
NAME LF_ROWS
------------------------------ ----------
T_IND_ID 1
insert into t1(data) values('b');
commit;
analyze index t1_ind_id validate structure;
select name, lf_rows from index_stats;
NAME LF_ROWS
------------------------------ ----------
T1_IND_ID2
insert into t1(id) values(3);
commit;
analyze index t1_ind_id validate structure;
select name, lf_rows from index_stats;
NAME LF_ROWS
------------------------------ ----------
T1_IND_ID 3
insert into t1 values(NULL,NULL);
commit;
analyze index t1_ind_id validate structure;
select name, lf_rows from index_stats;
NAME LF_ROWS
------------------------------ ----------
T1_IND_ID 3
发现了什么？当id为NULL，data不是NULL或者id不是NULL，data为NULL的时候，索引条目都会增加，但当id，data都为NULL的时候，索引条目并没有增加。
treedump索引层次结构，可以清楚的发现复合索引的键值可以部分为NULL。
row#0[8018] flag: ------, lock: 0, len=14
col 0; len 2; (2): c1 02
col 1; len 1; (1): 61
col 2; len 6; (6): 01 00 28 2c 00 00
row#1[7993] flag: ------, lock: 2, len=13
col 0; len 2; (2): c1 04
col 1; NULL
col 2; len 6; (6): 01 00 28 2c 00 02
row#2[8006] flag: ------, lock: 0, len=12
col 0; NULL
col 1; len 1; (1): 62
col 2; len 6; (6): 01 00 28 2c 00 01
----- end of leaf block dump -----
通过复合索引的实验，更加清楚的说明了B树索引可以存储NULL值，但不允许索引字段全部为NULL，若索引字段全部为NULL，索引不会存储。了解了上述内容后，对我们实际工作有何意义？创建索引时应该考虑什么？
1.若列对应的业务数据确实不存在NULL值的情况，建议创建表语句添加NOT NULL的约束
2.列对应的业务数据可能存在NULL值的情况，可以通过如下方法创建索引:
a)函数索引
create index t_ind_id on t(nvl(id,0));
b)伪列
create index t_ind_id on t(id,0);
c)复合索引
create index t_ind_id on t(id,data);
延伸：
明白了索引不能存储全部NULL值的键值，就解释了某些执行计划走全表扫描的原因了。
drop table t purge;
create table t (id number,data varchar2(100));
begin
for i in 1..10000 loop
insert into t values(trunc(dbms_random.value(1,1000)),dbms_random.string('a',5));
end loop;
commit;
end loop;
create index t_id on t(id);
exec dbms_stats.gather_table_stats(USER,'T',CASCADE=>TRUE);
SQL> SELECT count(*) FROM t;
COUNT(*)
----------
10000
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 10000 | 9 (0)| 00:00:01 |
-------------------------------------------------------------------
为什么执行计划是TABLE ACCESS FULL，因为字段id没有NOT NULL限制，如果走索引可能会漏掉id=NULL值的记录，从而统计结果有问题。
alter table t modify id not null;
SQL> SELECT count(*) FROM t;
COUNT(*)
----------
10000
Execution Plan
----------------------------------------------------------
Plan hash value: 3689807224
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| T_ID | 10000 | 7 (0)| 00:00:01 |
----------------------------------------------------------------------
可以看到执行计划从TABLE ACCESS FULL转换成了INDEX FAST FULL SCAN,优化了SQL的执行效率。
2293
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
