如何避免 SQL select 语句中出现 IS [NOT] NULL？
IS [NOT] NULL 会干扰 Oracle 中 SQL 语句的索引，有什么办法可以替换 SQL 语句中的 IS NULL 吗？
根据 Oracle 9i Performance Tuning Tips and Techniques，使用 IS [NOT] NULL 会抑制列的索引。
例如：
select * from users where user_id is not null;
IS [NOT] NULL meddles with the indexes of SQL statements in Oracle, is there any way I can replace IS NULL in an SQL statement?
According to Oracle 9i Performance Tuning Tips and Techniques, having IS [NOT] NULL suppresses the indexes of the columns.
For example:
select * from users where user_id is not null;
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
对于
IS NOT NULL，您的声明不正确。 Oracle将在该列上使用索引（假设索引扫描确实比全表扫描更快 - 但情况并非总是如此）Oracle 不会将以下元组放入所有 NULL 都放入索引中，因此，如果您在单个列上定义索引，例如
CREATE INDEX idx_user_id ON person (user_id)，该索引将不会包含 user_id 为 NULL 的行。因此，索引不能用于IS NULL条件。但是，如果您需要频繁执行
IS NULL选择，则可以使用解决方法，方法是使用索引定义中的常量强制对 null 进行索引：CREATE INDEX idx_user_id ON person (user_id, 1 ）。该索引将用于 IS NULL 条件（同样假设其他访问方法效率不高）。For
IS NOT NULLyour claim is not true. Oracle will use an index on that column (provided an index scan is indeed faster than a full table scan - which is not always the case)Oracle does not put tuples that are all NULL into an index, therefor if you define an index on a single column e.g.
CREATE INDEX idx_user_id ON person (user_id)that index will not contain rows where user_id is NULL. As a result of that, the index cannot be used for aIS NULLcondition.However you can use a workaround if you need to do frequent
IS NULLselections, by forcing the nulls to be indexed using a constant in the index definition:CREATE INDEX idx_user_id ON person (user_id, 1). That index will be used for a IS NULL condition (again provided other access methods are not more efficient).您可以更改数据库结构吗？
如果是，为了在查询中不出现任何
WHERE column IS NULL或WHERE column IS NOT NULL条件，请完全规范化您的表（即 5NF 或 6NF) 并将条件中使用的所有列设置为NOT NULL。我不是 Oracle 专家，但我严重怀疑像 Oracle 这样的严肃的 RDBMS 不能在可为空的列上使用索引。请参阅此问题和支持此观点的答案。也许您遇到的问题（您认为的）不是由列中的 NULL 引起的，而是由它们的选择性引起的：oracle-10g-optimize-where-is-not-null
对于您的简单查询：
优化器将选择不使用索引 - 因为它会为任何做其他查询 - 如果选择性不够高。如果表上的空值很少，那么完全扫描表会更快——或者至少优化器是这么认为的。
Are you allowed to change the db structure?
If yes, in order to not have any
WHERE column IS NULLorWHERE column IS NOT NULLcondition in your queries, then fully normalize your tables (i.e. 5NF or 6NF) and make all columns that are used in conditionsNOT NULL.I'm not an Oracle expert but I seriously doubt that a serious RDBMS such as Oracle cannot use index on a nullable column. See this question and answers that support this opinion. Perhaps the problems (you think) you have are not caused by the
NULLs in your column but by the selectivity of them: oracle-10g-optimize-where-is-not-nullFor your simple query:
the optimizer will choose not to use the index - as it would do for any other query - if the selectivity is not high enough. If the Nulls are few on the table, then fully scanning the table will be faster - or at least the optimizer thinks so.
没有有效的方法可以在不更改数据的情况下完成相同的操作。
您可以使用魔术值代替 null，例如 -1。这将允许您使字段不可为空，这会在一定程度上提高性能，并且可以更好地与索引配合使用。然而，这与避免魔法值的通常建议相冲突，因此这是性能和最佳实践之间的折衷。
There is no efficient way to do the same thing without changing the data.
You can use a magic value instead of null, for example -1. That would allow you to make the field non-nullable, which increases performance somewhat, and works better with indexes. However, this conflicts with the usual recommendation to avoid magic values, so it's a compromise between performance and best practice.