When Indexes Aren't Used in the Database?

NULL values cause indexes to be suppressed

When you use IS NULL or IS NOT NULL in the WHERE clauses in your SQL statement, index usage will be suppressed, since the value of NULL is undefined. There is no value in the database that will equal a NULL value; not even NULL equals a NULL.

NULL values pose several difficulties for SQL statements. Indexed columns that have rows containing a NULL value will not have an entry in the index (except for bitmapped indexes—which is why bitmap indexes are usually fast for NULL searches). Under normal circumstances the following statement would cause a full table scan to be performed, even if the Sal column is indexed:

select empno, ename, deptno
from emp
where sal is null;

To disallow NULL values for the columns, use NOT NULL when creating or altering the table. Note that if the table already contains data, you can only set a column to NOT NULL if it has a non-NULL value for every row or if you use the DEFAULT clause of the ALTER TABLE command.

The following listing shows the modification of the EMP table’s Sal column to disallow NULL values:

alter table emp modify
(sal not null);

Note that an error will be returned if insertion of a NULL value is attempted for the Sal column.

Using Functions in WHERE Clause May Cause Indexes To Be Suppressed

Unless you are using function-based indexes, using functions on indexed columns in the WHERE clause of a SQL statement will cause the database optimizer to bypass indexes. Some of the most common functions in Oracle are TRUNC, SUBSTR, TO_DATE, TO_CHAR, and INSTR. All of these functions will cause the value of the column to be altered. Therefore, the indexes and the columns being referenced will not be used.

The following statement would cause a full table scan to be performed, even if there is an index on the hire_date column (as long as it wasn’t a function-based index):

select empno, ename, deptno
from emp
where trunc(hiredate) = '01-MAY-01';

Changing the statement to the following would allow for an index lookup:

select empno, ename, deptno
from emp
where hiredate > '01-MAY-01'
and hiredate < (TO_DATE('01-MAY-01') + 0.99999);

By altering the values being compared to the column, and not the columns themselves, the indexes become available. This is used to eliminate full table scans.

Nice to know.