最近在办公室里，听见这么一段对话：

Bob: Alice，我看了下你昨天告诉我的那个慢查询，我已经把你想要的那个索引给加上去。现在肯定OK了。

Alice：谢谢你，Bob。我马上确认一下…不对啊，还是很慢，看起来没起作用啊

Bob：还真是。看起来Oracle没有用上这个索引，你那个查询我加了/+INDEX(...)/索引提示也不行。真是不知道怎么回事了。

然后，问题仍然没有解决。Alice很头疼，因为她要加的特性没有按时发现，Bob也很发愁，因为他觉得Oracle居然没有正常工作。

这是个真事。

Bob忘了Oralce和NULL值的问题了

可怜的Bob忘了，Oralce是不会把NULL放到普通索引里的。你想一下这种情况：

CREATE TABLE person (
id NUMBER(38) NOT NULL PRIMARY KEY,
first_name VARCHAR2(50) NOT NULL,
last_name VARCHAR2(50) NOT NULL,
date_of_birth DATE NULL
);
CREATE INDEX i_person_dob ON person(date_of_birth);

现在Bob认为有了这个索引什么事都解决了，因为他用这个查询验证了下，这个索引确实是好使的：

SELECT *
FROM person
WHERE date_of_birth > DATE '1980-01-01’;

(当然了，你不应该使用select *)

这个查询的执行计划看起来很正常：

----------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| PERSON |
|* 2 | INDEX RANGE SCAN | I_PERSON_DOB |
----------------------------------------------------

这是因为Bob的查询并不需要NULL作为IPERSONDOB索引的一部分。不幸的是，Alice的查询看起来大概是这样的：

SELECT 1
FROM dual
WHERE DATE '1980-01-01' NOT IN (
SELECT date_of_birth FROM person
);

实际上，Alice的查询是判断是不是有人是这天生日的。她的执行计划看起来会是这样的：

-------------------------------------
| Id | Operation | Name |
-------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | FILTER | |
| 2 | FAST DUAL | |
|* 3 | TABLE ACCESS FULL| PERSON |
-------------------------------------

可以看到，她的查询进行了一个TABLE ACCESS FULL操作，索引被忽略了。为什么呢？很简单：

Oracle不会把NULL值放到索引里。

NOT IN(a, b, NULL, c, d)的结果是NULL。

不管你的这个日期’1980-01-01’在没在索引里，我们都得查看整个表来确认dateofbirth列中是否饮食一个NULL值。因为如果存在NULL值的话，Alice查询中这个NOT IN谓词的结果不是TRUE或FALSE，而是NULL。

Alice可以用NOT EXISTS来解决这个问题

这个问题其实Alice自己就可以很容易搞定，她只需要把NOT IN换成NOT EXISTS就好了，这个谓词能够绕过SQL的特殊的三值逻辑。

SELECT 1
FROM dual
WHERE NOT EXISTS (
SELECT 1
FROM person
WHERE date_of_birth = DATE '1980-01-01'
);

现在新的查询语句的确能够得到一个最优的执行计划：

------------------------------------------
| Id | Operation | Name |
------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | FILTER | |
| 2 | FAST DUAL | |
|* 3 | INDEX RANGE SCAN| I_PERSON_DOB |
------------------------------------------

但问题仍然存在，因为该来的迟早还是会来的。Alice必须在写每条查询说一句的时候都时刻谨记这次教训。

对于Bob只需把这列设置成NOT NULL就好了

最佳的解决方案，其实就是把这列设置成NOT NULL就好了：

ALTER TABLE person
MODIFY date_of_birth DATE NOT NULL;

有了这个约束后，NOT IN查询就和NOT EXISTS查询是一样的了，Bob和Alice又可以一起快乐地玩耍了。

如何找出这些捣乱的列?

很简单。下面这个查询可以列出所有存在一个NULL值的索引列。

SELECT
i.table_name,
i.index_name,
LISTAGG(
LPAD(i.column_position, 2) || ': ' ||
RPAD(i.column_name , 30) || ' ' ||
DECODE(t.nullable, 'Y', '(NULL)', '(NOT NULL)'),
', '
) WITHIN GROUP (ORDER BY i.column_position)
AS "NULLABLE columns in indexes"
FROM user_ind_columns i
JOIN user_tab_cols t
ON (t.table_name, t.column_name) =
((i.table_name, i.column_name))
WHERE EXISTS (
SELECT 1
FROM user_tab_cols t
WHERE (t.table_name, t.column_name, t.nullable) =
((i.table_name, i.column_name, 'Y' ))
)
GROUP BY i.table_name, i.index_name
ORDER BY i.index_name ASC;

你现在再用Bob和Alice的schema来执行下，上述查询的结果是：

TABLE_NAME | INDEX_NAME | NULLABLE columns in indexes
-----------+--------------+----------------------------
PERSON | I_PERSON_DOB | 1: DATE_OF_BIRTH (NULL)

现在你可以在你自己的schema上运行下这条查询语句，仔细地看一下结果中的那些列有没有必要允许NULL值的出现。应该有半数的情况下是不该出现NULL值的。加上一个NOT NULL约束后，你的程序的性能可能会得到质的提升!