| 组合索引与NULL值 | DBA之路：OoNiceDream |

组合索引与NULL值

作者：OoNiceDream【转载时请务必以超链接形式标明文章原始出处和作者信息】
链接：http://www.dbaroad.me/archives/2009/06/index_with_null_2.html

昨天写了个索引与NULL值，回头查看了资料，发现理解得太单一了，没把组合索引考虑上，而且组合索引中，NULL不是不记录，应该理解为不完全记录：

sys@ORCL>create table test (a number,b number);
 
Table created.
 
sys@ORCL>insert into test values(1,1);
 
1 row created.
 
sys@ORCL>insert into test values(1,null);
 
1 row created.
 
sys@ORCL>insert into test values(null,1);
 
1 row created.
 
sys@ORCL>insert into test values(null,null);
 
1 row created.
 
sys@ORCL>commit;
 
Commit complete.
 
sys@ORCL>create index ind_ab on test(a,b);
 
Index created.

以下为tee dump及leaf block信息：

----- begin tree dump
leaf: 0x40ed6a 4255082 (0: nrow: 3 rrow: 3)
----- end tree dump
 
 
row#0[8017] flag: ------, lock: 0, len=15
col 0; len 2; (2):  c1 02
col 1; len 2; (2):  c1 02
col 2; len 6; (6):  00 40 ed 62 00 00
row#1[8004] flag: ------, lock: 0, len=13
col 0; len 2; (2):  c1 02
col 1; NULL
col 2; len 6; (6):  00 40 ed 62 00 01
row#2[7991] flag: ------, lock: 0, len=13
col 0; NULL
col 1; len 2; (2):  c1 02
col 2; len 6; (6):  00 40 ed 62 00 02

可以看出所有列均为NULL值时，索引并不记录。对于这张表的is null查询，该组合索引也用不上的：

sys@ORCL>set autot trace exp 
sys@ORCL>select * from test where a is null;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |    52 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     2 |    52 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("A" IS NULL)
 
Note
-----
   - dynamic sampling used for this statement

对于组合索引，查询列(A）为is null时，该列(A）为组合索引的前导列，且索引中的其它列至少有一列是NOT NULL时，才有可能使用上该索引：

sys@ORCL>truncate table test;
 
Table truncated.
 
sys@ORCL>alter table test modify(b not null);
 
Table altered.
 
sys@ORCL>insert into test values(1,1);
 
1 row created.
 
sys@ORCL>insert into test values(null,1);
 
1 row created.
 
sys@ORCL>commit;
 
Commit complete.
 
sys@ORCL>set autot trace exp
sys@ORCL>select * from test where a is null;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 995238657
 
---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |     1 |    26 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IND_AB |     1 |    26 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("A" IS NULL)
 
Note
-----
   - dynamic sampling used for this statement

测试用三个列的组合索引，可以得出相同的结论。

对于组合位图索引，与单列的位图索引相同，NULL值是会被记录的，查询也能使用上该组合索引：

sys@ORCL>create table t (a number,b number);
 
Table created.
 
sys@ORCL>insert into t values(null,null);
 
1 row created.
 
 
Execution Plan
----------------------------------------------------------
 
-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | INSERT STATEMENT |      |     1 |   100 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------
 
sys@ORCL>set autot off;
sys@ORCL>insert into t values(1,1);
 
1 row created.
 
sys@ORCL>insert into t values(1,null);
 
1 row created.
 
sys@ORCL>insert into t values(null,1);
 
1 row created.
 
sys@ORCL>commit;
 
Commit complete.
 
sys@ORCL>create bitmap index ind_t on t(a,b);
 
Index created.

以下为tee dump及leaf block信息：

----- begin tree dump
leaf: 0x40ed7a 4255098 (0: nrow: 4 rrow: 4)
----- end tree dump   
 
row#0[8008] flag: ------, lock: 0, len=24
col 0; len 2; (2):  c1 02
col 1; len 2; (2):  c1 02
col 2; len 6; (6):  00 40 ed 42 00 00
col 3; len 6; (6):  00 40 ed 42 00 07
col 4; len 1; (1):  01
row#1[7986] flag: ------, lock: 0, len=22
col 0; len 2; (2):  c1 02
col 1; NULL
col 2; len 6; (6):  00 40 ed 42 00 00
col 3; len 6; (6):  00 40 ed 42 00 07
col 4; len 1; (1):  02
row#2[7964] flag: ------, lock: 0, len=22
col 0; NULL
col 1; len 2; (2):  c1 02
col 2; len 6; (6):  00 40 ed 42 00 00
col 3; len 6; (6):  00 40 ed 42 00 07
col 4; len 1; (1):  03
row#3[7944] flag: ------, lock: 0, len=20
col 0; NULL
col 1; NULL
col 2; len 6; (6):  00 40 ed 42 00 00
col 3; len 6; (6):  00 40 ed 42 00 07
col 4; len 1; (1):  00
----- end of leaf block dump -----

谓词中IS NULL查询可以用上该索引：

sys@ORCL>set autot trace exp
sys@ORCL>select * from t where a is null;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 373097103
 
--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     2 |    52 |     1   (0)| 00:00:01 |
|   1 |  BITMAP CONVERSION TO ROWIDS |       |     2 |    52 |     1   (0)| 00:00:01 |
|*  2 |   BITMAP INDEX FAST FULL SCAN| IND_T |       |       |            |          |
--------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("A" IS NULL)
 
Note
-----
   - dynamic sampling used for this statement
 
sys@ORCL>select * from t where b is null;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 373097103
 
--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     2 |    52 |     1   (0)| 00:00:01 |
|   1 |  BITMAP CONVERSION TO ROWIDS |       |     2 |    52 |     1   (0)| 00:00:01 |
|*  2 |   BITMAP INDEX FAST FULL SCAN| IND_T |       |       |            |          |
--------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("B" IS NULL)
 
Note
-----
   - dynamic sampling used for this statement
 
sys@ORCL>select * from t        
  2  where a is null and b is null;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 373097103
 
--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     1 |    26 |     1   (0)| 00:00:01 |
|   1 |  BITMAP CONVERSION TO ROWIDS |       |     1 |    26 |     1   (0)| 00:00:01 |
|*  2 |   BITMAP INDEX FAST FULL SCAN| IND_T |       |       |            |          |
--------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("A" IS NULL AND "B" IS NULL)
 
Note
-----
   - dynamic sampling used for this statement

— The End —

关键字: 基础知识

上一篇单列索引与NULL值

常见索引扫描方式（四）：INDEX SKIP SCAN 下一篇

喜欢DBARoad的文章，那就通过 RSS Feed 功能订阅阅读吧！

随机文章

才1个评论

1楼
06月 24th, 2009 at 14:39 Lacy 说:
Pretty nice post. I just found your blog and wanted to say
that I have really enjoyed reading your posts. In any case
I’ll be subscribing to your blog and I hope you write again soon!

Reply

公告牌:

当前位置: DBARoad > 基础知识 > 文章正文

组合索引与NULL值

相关文章

随机文章

才1个评论

我要评论

相关文章

最新文章

订阅

站内搜索