如何避免在SQL select语句中出现IS [NOT] NULL？

Question

IS [NOT] NULL干扰了Oracle中SQL语句的索引，有没有办法在SQL语句中替换IS NULL？

根据Oracle 9i性能调优提示和技术，使用IS NOT NULL会抑制列的索引。

例如：

select * from users where user_id is not null;

Answer 1

没有一种有效的方法可以在不更改数据的情况下做同样的事情。

您可以使用魔术值而不是null，例如-1。这将允许您使该字段不可为空，这在一定程度上提高了性能，并且可以更好地处理索引。然而，这与避免魔术值的通常建议相冲突，因此它是性能和最佳实践之间的折衷。

Answer 2

允许您更改数据库结构吗？

如果是，为了在查询中不包含任何WHERE column IS NULL或WHERE column IS NOT NULL条件，请完全规范化您的表(即5NF或6NF)，并将条件中使用的所有列设置为NOT NULL。

我不是Oracle专家，但我非常怀疑像Oracle这样严肃的RDBMS不能在可为空的列上使用索引。查看支持此观点的问题和答案。也许您所遇到的问题(您认为)不是由您的专栏中的NULL引起的，而是由它们的选择性引起的：oracle-10g-optimize-where-is-not-null

对于您的简单查询：

select * from users where user_id is not null;

如果选择性不够高，优化器将选择不使用索引-就像它对任何其他查询所做的那样。如果表上的Nulls很少，那么完全扫描表将会更快--至少优化器是这样认为的。

Answer 3

对于IS NOT NULL来说，你的说法是不正确的。Oracle将对该列使用索引(假设索引扫描确实比全表扫描快-事实并非如此)

Oracle不会将全部为NULL的元组放入索引，因此，如果您在单个列上定义索引，例如CREATE INDEX idx_user_id ON person (user_id)，该索引将不包含user_id为NULL的行。因此，索引不能用于IS NULL条件。

但是，如果您需要频繁地进行空值选择，则可以通过使用索引定义中的常量CREATE INDEX idx_user_id ON person (user_id, 1)强制对IS NULL进行索引，从而使用一种变通方法。该索引将用于IS NULL条件(同样，假设其他访问方法不是更有效)。