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

The NVL function wraps the indexed column EMPNO, which prevents Oracle from using the index on EMPNO. When a function is applied to a column in the WHERE clause, the index on that column cannot be used unless a function-based index exists. Options B, C, and D directly compare EMPNO, allowing index usage.

Keep practicing — related questions