Oracle database does not index a row if all the indexed columns are
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
NULL, it does not add the created row to the index. Hence, the index is not useful when querying with
Use a constant expression that can never be
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.