单列索引与NULL值
作者 ：OoNiceDream【转载时请务必以超链接形式标明文章原始出处和作者信息】
链接：http://www.dbaroad.me/archives/2009/06/index_with_null_1.html
链接：http://www.dbaroad.me/archives/2009/06/index_with_null_1.html
常见的B-Tree单列索引中，并不会记录null值的索引条目，因而is null等条件的查询走不了索引，走的是全表扫，而Bitmap索引则不同，它会记录NULL值的索引条目：
B-Tree Index:
SQL> create table test_btree (name varchar2(10) default null); Table created. SQL> begin 2 for i in 1..100 loop 3 insert into test_btree values(default); 4 commit; 5 end loop; 6 end; 7 / PL/SQL procedure successfully completed. SQL> create index ind_btree on test_btree(name); Index created. SQL> select object_id from dba_objects 2 where object_name='IND_BTREE'; OBJECT_ID ---------- 109139 SQL> alter session set events 'immediate trace name treedump level 109139'; Session altered. |
叶子节点中未记录有索引条目：
----- begin tree dump leaf: 0x410eea 4263658 (0: nrow: 0 rrow: 0) ----- end tree dump |
Bitmap Index：
SQL> create table test_bitmap (name varchar2(10) default null); Table created. SQL> begin 2 for i in 1..100 loop 3 insert into test_bitmap values(default); 4 commit; 5 end loop; 6 end; 7 / PL/SQL procedure successfully completed. SQL> create bitmap index ind_bitmap on test_bitmap(name); Index created. SQL> select object_id from dba_objects 2 where object_name='IND_BITMAP'; OBJECT_ID ---------- 109141 SQL> alter session set events 'immediate trace name treedump level 109141'; Session altered. |
叶子节点中记录有索引条目：
----- begin tree dump leaf: 0x410f02 4263682 (0: nrow: 1 rrow: 1) ----- end tree dump SQL> select 2 dbms_utility.data_block_address_file(4263682) file_id#, 3 dbms_utility.data_block_address_block(4263682) block_id# 4 from dual; FILE_ID# BLOCK_ID# ---------- ---------- 1 69378 SQL> alter system dump datafile 1 block 69378; System altered. Leaf block dump =============== header address 9223376400541909084=0x800003f80005805c kdxcolev 0 KDXCOLEV Flags = - - - kdxcolok 0 kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y kdxconco 4 kdxcosdc 0 kdxconro 1 kdxcofbo 38=0x26 kdxcofeo 7983=0x1f2f kdxcoavs 7945 kdxlespl 0 kdxlende 0 kdxlenxt 0=0x0 kdxleprv 0=0x0 kdxledsz 0 kdxlebksz 8016 row#0[7983] flag: ------, lock: 0, len=33 col 0; NULL col 1; len 6; (6): 00 41 0e fa 00 00 col 2; len 6; (6): 00 41 0e fa 00 67 col 3; len 15; (15): cf ff ff ff ff ff ff ff ff cc ff ff ff ff 0f ----- end of leaf block dump ----- |
因而对于is null等条件的查询，B-Tree类型的索引是用不上的，而Bitmap索引则可以：
SQL> select count(*) from test_btree 2 where name is null; Execution Plan ---------------------------------------------------------- Plan hash value: 2736914969 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 7 | | | |* 2 | TABLE ACCESS FULL| TEST_BTREE | 100 | 700 | 2 (0)| 00:00:01 | --------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("NAME" IS NULL) Note ----- - dynamic sampling used for this statement SQL> select count(*) from test_bitmap 2 where name is null; Execution Plan ---------------------------------------------------------- Plan hash value: 2521385644 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 7 | 1 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 7 | | | | 2 | BITMAP CONVERSION COUNT | | 100 | 700 | 1 (0)| 00:00:01 | |* 3 | BITMAP INDEX FAST FULL SCAN| IND_BITMAP | | | | | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("NAME" IS NULL) Note ----- - dynamic sampling used for this statement |
在B-Tree索引中，INDEX FAST FULL SCAN要求索引中至少有一列是带有not null约束的，BITMAP索引显然就不受这一约束了。
另外有一点需要注意的是，在唯一索引中，Oracle认为NULL与NULL是不同的：
sys@ORCL>create table t (a number); Table created. sys@ORCL>create unique index ind_t on t(a); Index created. sys@ORCL>insert into t values(1); 1 row created. sys@ORCL>insert into t values(1); insert into t values(1) * ERROR at line 1: ORA-00001: unique constraint (SYS.IND_T) violated sys@ORCL>insert into t values(null); 1 row created. sys@ORCL>insert into t values(null); 1 row created. sys@ORCL>commit; Commit complete. |
但对group by 语句来说却是一样的：
sys@ORCL>select count(*),a from t group by a; COUNT(*) A ---------- ---------- 1 1 2 |
因而我们在创建唯一索引时，常常要求至少有一列是NOT NULL的。
— The End —
关键字: 基础知识
站内搜索