Oracle SQL中<>操作符忽略索引且不返回NULL行的解决方案咨询

针对你遇到的问题——既要返回name为NULL的行，又要让查询使用name列的索引，提供以下几种可行方案：

方案1：创建基于函数的索引 #

针对你使用的nvl(name, 'NOT John')函数表达式创建索引，让查询可以直接命中该索引：

CREATE INDEX idx_employee_name_nvl ON employee(nvl(name, 'NOT John'));

创建完成后，执行SELECT * from employee where nvl(name, 'NOT John') <> 'John';时，Oracle会自动使用这个基于函数的索引，同时返回所有非'John'及NULL的行。

方案2：用UNION ALL拆分查询 #

将查询拆分为两个独立的条件分支，每个分支都能单独使用name列的普通索引：

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

这种写法利用了Oracle对简单等值/非等值条件的索引优化能力，name <> 'John'和name IS NULL各自都可以匹配name列的普通B树索引，同时合并结果时不会产生重复数据（NULL和非NULL行天然不重复）。

方案3：位图索引（适用于低基数列） #

如果name列的重复值较多（基数低，比如只有少数几个不同的姓名），可以创建位图索引：

CREATE BITMAP INDEX idx_employee_name_bitmap ON employee(name);

位图索引在处理包含NULL的过滤条件时效率较高，但注意不适合OLTP（在线事务处理）场景——因为位图索引会在DML操作时锁定大量行，影响并发性能，更适合数据仓库等只读/少写场景。

方案4：调整表结构为索引组织表（IOT） #

如果employee表的查询大多围绕name列进行，可以将表改为索引组织表，把name作为主键或主键的一部分：

CREATE TABLE employee (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(50),
    -- 其他列
) ORGANIZATION INDEX;

索引组织表将表数据直接存储在索引结构中，针对name列的过滤查询会天然高效，同时也能正确返回NULL行。

内容的提问来源于stack exchange，提问作者atanu2destiny