Feb 18, 2019

TIL: Indexing `NULL` in Oracle Database

Estimated Reading Time: 1 minutes (141 words)

Oracle database does not index 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 is 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 have a NOT NULL constraint.

CREATE INDEX users_role ON users (role, id)

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

This post was first appeared in my TIL web application at here.