Indexing `NULL` in Oracle Database

Oracle database does not index of a row if all the indexed columns are NULL

For instance, let say we have a users table where we index role column to improve the query performance.

SELECT * from users WHERE role IS NULL

The index does not work for such query in Oracle database. This is because when Oracle database inserting the new record of user where the role is NULL, it does not add the created row to the index. Hence, the index is not useful when querying with NULL


Use a constant expression that can never be NULL.

CREATE INDEX users_role ON users (role, 'user')

or we can use a concatenated index (multi-column index) and ensure that the the other column of the index must haveNOT NULL constraint.

CREATE INDEX users_role ON users (role, id)

For more detailed explanation and source, refer to NULL in Oracle Database