英文:
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 <> 'John';
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 <> 'John';
SELECT * from employee where nvl(name, 'NOT John') <> 'John';
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论