Multiple choice technology databases

The primary key on table EMP is the EMPNO column. Which of the following statements will not use the associated index on EMPNO?

  1. select * from EMP where nvl(EMPNO, '00000') = '59384';

  2. select * from EMP where EMPNO = '59384';

  3. select EMPNO, LASTNAME from EMP where EMPNO = '59384';

  4. select 1 from EMP where EMPNO = '59834';
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

When a column is wrapped in a function like NVL(), the Oracle optimizer cannot use the index on that column because the function modifies the column value before comparison. For options B, C, and D, EMPNO is compared directly to a literal value, allowing index access. Option A applies NVL() to EMPNO first, making the index unusable and forcing a full table scan. This is a common performance anti-pattern.

Keep practicing — related questions