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

Question

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.

Answer 1

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.