Multiple choice

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

The NVL function wraps the EMPNO column, which prevents the database from using the index on EMPNO. When a column is modified by a function in the WHERE clause, the optimizer cannot perform an index range scan on that column because the indexed values don't match the function-transformed values. The other options (B, C, D) all use direct equality comparisons on EMPNO, which can leverage the index.

Keep practicing — related questions