Oracle中的NULL（七）

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

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

这一篇讨论NULL对SQL使用索引的影响。

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

Oracle中的NULL（六）：http://yangtingkun.itpub.net/post/468/251496

E+cY3p?7590422

最近在论坛上看到很多人从网上找到一些SQL优化的注意点，其中有不少就和NULL的处理有关。遗憾的是，找到的优化文章都是比较老的，一般只适用于RBO下，而对CBO以及不再适用了。

本文关注点仍然是在NULL上面，这篇文章打算通过实例来说明这些文章中和NULL相关的一些观点已经不适用于CBO优化器了。

观点一：判断一个列IS NOT NULL不会使用索引。

其实这个观点从一般意义上也解释不同，因为B树索引本身不存储键值全为NULL的记录，所以通过索引扫描得到的结果一定满足IS NOT NULL的要求。

SQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS;

表已创建。

SQL> CREATE INDEX IND_T_DATAID ON T(DATA_OBJECT_ID);

索引已创建。

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

PL/SQL 过程已成功完成。

SQL> SET AUTOT TRACE
VpjEZ/r7590422SQL> SELECT COUNT(*) FROM T WHERE DATA_OBJECT_ID IS NOT NULL;

:r6b Q s'n7\I7590422Execution Plan
`Y f{2H%`-}~[7590422----------------------------------------------------------ITPUB个人空间^GBGC{
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=2)ITPUB个人空间 t+?"o qa|
1 0 SORT (AGGREGATE)
YOA,w_:f.{H.IT;V8WR7590422 2 1 INDEX (FULL SCAN) OF 'IND_T_DATAID' (NON-UNIQUE) (Cost=26 Card=2946 Bytes=5892)

Os @#g+|$hSG.|9?7590422Statistics
M5y O*tlf
d'D7590422----------------------------------------------------------
s8j7bd,vd`7590422 0 recursive callsITPUB个人空间r TD4N[4M
0 db block getsITPUB个人空间mX+`.i(Rr
5 consistent gets
"yw{a_eE1?~TIp7590422 4 physical reads
?#fU&KHIRJ7590422 0 redo sizeITPUB个人空间4ugu4D${7fS
377 bytes sent via SQL*Net to clientITPUB个人空间&}nn6lrm*_|
503 bytes received via SQL*Net from client
8nf'O"H2? ]k%dK7590422 2 SQL*Net roundtrips to/from clientITPUB个人空间;YrEve6Y
0 sorts (memory)
!Q:vof{8O%|1o7590422 0 sorts (disk)ITPUB个人空间V4i!}&x*yG9Z M
1 rows processed

由于索引的存储特性和IS NOT NULL访问本身没有冲突，因此，这种情况下很容易通过索引来得到相应的结果。

观点二：判断一个列IS NULL不会使用索引。

这里不讨论BITMAP索引。由于BITMAP索引保存NULL值，所以讨论BITMAP索引没有意义。这里仅讨论B树索引。

在上一篇文章里面其实已经讨论过了，B树索引不存储键值全为空的记录。所以对于单列索引而言，确实IS NULL操作是无法使用索引的。但是复合索引是可能存储一部分NULL值的，所以，IS NULL操作也并非不可能使用索引。

SQL> ALTER TABLE T MODIFY OWNER NOT NULL;

表已更改。

SQL> UPDATE T SET OBJECT_ID = NULL WHERE ROWNUM = 1;

已更新 1 行。

SQL> CREATE INDEX IND_T_OBJECT_OWNER ON T (OBJECT_ID, OWNER);

索引已创建。

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

ITPUB个人空间z1C3t,vC)k*\
PL/SQL 过程已成功完成。

SQL> SET AUTOT TRACE
2ni%BC6Xy9{2l7590422SQL> SELECT * FROM T WHERE OBJECT_ID IS NULL;

ITPUB个人空间W,xcp)Q)?XP
Execution PlanITPUB个人空间7d3_'[I:?2dRf
----------------------------------------------------------ITPUB个人空间'h4o"xZlO)nm0r
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=93)ITPUB个人空间G3\QW;z
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=3 Card=1 Bytes=93)ITPUB个人空间e"S-I${-RS:t2y
2 1 INDEX (RANGE SCAN) OF 'IND_T_OBJECT_OWNER' (NON-UNIQUE) (Cost=2 Card=1)

ITPUB个人空间v.Qr
[&Ixu
Statistics
x_9l1[.Qjt` Ire7590422----------------------------------------------------------ITPUB个人空间7peVD6i;U)C]
0 recursive callsITPUB个人空间 B.[ Qg:rOPiQu
0 db block getsITPUB个人空间;Q+? oO'B$HH/T
3 consistent getsITPUB个人空间Y}zHPhA:YJ
0 physical readsITPUB个人空间0T\0D/CJX[
0 redo sizeITPUB个人空间)mr"\P|!E} q
1156 bytes sent via SQL*Net to client
}6t[dJ-p7590422 503 bytes received via SQL*Net from clientITPUB个人空间 m
P"_xm;rS
2 SQL*Net roundtrips to/from clientITPUB个人空间5r;L,`'K(sD'y[
0 sorts (memory)
(]
k#Ea_o7590422 0 sorts (disk)ITPUB个人空间(?u1KK.M4^M\u`g
1 rows processed

从上面的两个例子可以看到，Oracle的CBO并不会因为SQL语句中指定了IS NOT NULL或IS NULL操作就不再使用索引。CBO选择索引的条件只有正确性和代价，只要满足这两个条件就可以了。