不等于(<>)在Oracle SQL中忽略了索引和空值行。

huangapple go评论74阅读模式
英文:

NOT Equal(<>) in Oracle SQL is ignoring Index and NULL valued rows

问题

我有一个查询,在Oracle SQL中需要基于不等于的值进行过滤。无法使用等于条件,因为列中的可能值是未知的。

查询:

SELECT * FROM employee WHERE name <> 'John';

上述查询忽略了name列中的索引,同时不返回name列中为空的行。

尝试处理NULL安全返回的解决方案:

SELECT * FROM employee WHERE name IS NULL OR name <> 'John';

SELECT * FROM employee WHERE NVL(name, 'NOT John') <> 'John';

上述两个查询能够过滤掉空行,但无法使用name列中定义的索引。请提供建议。

英文:

I have one query where need to filter based on NOT Equal values in Oracle SQL. Can not use EQUAL to condition as the possible values fo the column in unknown.

Query:

SELECT * from employee where name &lt;&gt; &#39;John&#39;;

The above query is ignoring the index in name column as well as rows having null in name column is not returned.

Solution Tried for NULL safe return.

SELECT * from employee where name is null OR name &lt;&gt; &#39;John&#39;;

SELECT * from employee where nvl(name, &#39;NOT John&#39;) &lt;&gt; &#39;John&#39;;

The above two queries is able to filter out the null rows.
But not able to use the index defined in name column.

Please advise.

答案1

得分: 2

索引的意图是快速找到特定值。通常情况下,"不等于" 的条件不会通过索引获得好处。NULL 值不会被索引,因此也没有好处。

如果使用 BITMAP INDEX,那么索引将被使用。但是,BITMAP 索引有其他限制,这取决于您的应用程序和数据是否有用。

英文:

Intention of an index is to find quickly a specific value. Conditions on "not equal" typically do not gain by an index. NULL values are not indexed, so there is no benefit either.

It looks different if you use a BITMAP INDEX, there the index would be used. However, BITMAP indexes have other limitations, it depends on your application and data whether it could be useful or not.

huangapple
  • 本文由 发表于 2023年5月11日 13:54:22
  • 转载请务必保留本文链接:https://go.coder-hub.com/76224521.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定