ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle中的NULL(六)

Oracle中的NULL(六)

原创 Linux操作系统 作者:yangtingkun 时间:2007-01-15 00:00:00 0 删除 编辑

最近在论坛上经常看到,很多人提出和NULL有关的问题。NULL其实是数据库中特有的类型,Oracle中很多容易出现的错误都是和NULL有关的。

打算简单的总结一下NULL的相关知识。

这篇文档讨论一下NULL和索引的关系。

Oracle中的NULL(一):http://yangtingkun.itpub.net/post/468/244434

Oracle中的NULL(二):http://yangtingkun.itpub.net/post/468/245107

Oracle中的NULL(三):http://yangtingkun.itpub.net/post/468/245259

Oracle中的NULL(四):http://yangtingkun.itpub.net/post/468/245697

Oracle中的NULL(五):http://yangtingkun.itpub.net/post/468/247492


前面几篇文章讨论了NULL的数据类型和NULL的运算特点。这里打算简单描述NULL和索引的关系。

如果说NULL类型已经比较容易出错了,那么索引问题就让NULL又一次成为问题的焦点。

大多数人都听说过这样一句话,索引不存储NULL值。这句话其实比不严谨。如果采用比较严谨的方式来说:B树索引不存储索引列全为空的记录。如果把这句话用在单列索引上,就是前面提到的B树索引不存储NULL

首先索引分为BTREEBITMAP两种,对于BTREE索引,是不存储NULL值的,而对于BITMAP索引,是存储NULL值的。

而从索引列的个数来划分,索引非为单列索引和复合索引,对于单列索引来说很简单,如果一条记录中这个索引字段为空,那么索引不会保存这条记录的信息。但是对于复合索引,由于存在着多个列,如果某一个索引列不为空,那么索引就会包括这条记录,即使其他所有的所有列都是NULL值。

SQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS;

表已创建。

SQL> DESC T
名称 是否为空? 类型
-------------------------------------------- -------- ------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)

SQL> CREATE INDEX IND_T_OBJECT_ID ON T (OBJECT_ID);

索引已创建。

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T', CASCADE => TRUE)

PL/SQL 过程已成功完成。

SQL> SET AUTOT ON EXP
SQL> SELECT COUNT(*) FROM T;

COUNT(*)
----------
50297

执行计划
----------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 41 (3)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 50297 | 41 (3)| 00:00:01 |
-------------------------------------------------------------------

SQL> SELECT /*+ INDEX(T IND_T_OBJECT_ID) */ COUNT(*) FROM T;

COUNT(*)
----------
50297

执行计划
----------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 41 (3)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 50297 | 41 (3)| 00:00:01 |
-------------------------------------------------------------------

Oracle的优化器在确定是否使用索引的时候,第一标准是能否得到一个正确的结果。由于OBJECT_ID是可以为空的,而索引列不包含为空的记录。因此通过索引扫描无法得到一个正确的结果,这就是SELECT COUNT(*) FROM T不会使用OBJECT_ID上的索引的原因。

而对于BITMAP索引,则是另外的情况:

SQL> DROP INDEX IND_T_OBJECT_ID;

索引已删除。

SQL> CREATE BITMAP INDEX IND_B_T_DATA_ID ON T (DATA_OBJECT_ID);

索引已创建。

SQL> SELECT COUNT(*) FROM T;

COUNT(*)
----------
50297

执行计划
----------------------------------------------------------
Plan hash value: 3051411170

-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)|
| 1 | SORT AGGREGATE | | 1 | |
| 2 | BITMAP CONVERSION COUNT| | 50297 | 2 (0)|
| 3 | BITMAP INDEX FULL SCAN| IND_B_T_DATA_ID | | |
-------------------------------------------------------------------------

SQL> SELECT COUNT(*) FROM T WHERE DATA_OBJECT_ID IS NULL;

COUNT(*)
----------
46452

执行计划
----------------------------------------------------------
Plan hash value: 2587852253

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1| 2| 2 (0)|
| 1 | SORT AGGREGATE | | 1| 2| |
| 2 | BITMAP CONVERSION COUNT | | 46452| 92904| 2 (0)|
|* 3 | BITMAP INDEX SINGLE VALUE| IND_B_T_DATA_ID| | | |
------------------------------------------------------------------------------

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

3 - access("DATA_OBJECT_ID" IS NULL)

从上面的结果不难看出BITMAP索引中是包含NULL的。

下面看看复合索引的情况:

SQL> DROP INDEX IND_B_T_DATA_ID;

索引已删除。

SQL> CREATE INDEX IND_T_OBJECT_DATA ON T(OBJECT_ID, DATA_OBJECT_ID);

索引已创建。

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T', METHOD_OPT => 'FOR ALL INDEXED COLUMNS')

PL/SQL 过程已成功完成。

SQL> SELECT OBJECT_ID, DATA_OBJECT_ID FROM T WHERE OBJECT_ID = 135;

OBJECT_ID DATA_OBJECT_ID
---------- --------------
135

执行计划
----------------------------------------------------------
Plan hash value: 1726226519

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 1 (0)|
|* 1 | INDEX RANGE SCAN| IND_T_OBJECT_DATA | 1 | 7 | 1 (0)|
---------------------------------------------------------------------------

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

1 - access("OBJECT_ID"=135)

虽然结果中包含了NULL值,但是Oracle并没有读取表,而仅仅通过索引扫描就返回了结果,这说明复合索引中是可能包含NULL值的。

本文简单说明了索引和NULL值的关系。这里并没有对反键索引(reverse)、逆序索引(desc)、函数索引(FBI)和CLUSTER索引进行说明。

原因是这些索引其实都属于离不开BTREE索引和BITMAP索引的范畴。不必关心索引是否倒序或反键,只要是BTREE索引,就不会存储全NULL记录,反之,只要是BITMAP索引就会存储NULL值。

唯一需要注意的是函数索引,函数索引的真正索引列是函数的计算结果而不是行记录中的数据,清楚了这一点函数索引其实和普通索引就没有什么区别了。

最后说明一下域索引。由于域索引的实现本身可能会很复杂,Oracle可能在内部是用一套表和过程来实现的,因此对于域索引是否存储NULL,要根据域索引的实现去进行具体的分析了。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-69139/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10487728