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

technology databases

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

  2. select 1 from EMP where EMPNO = '59834';

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

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

Show answer
Correct Option: A
Explanation:

To answer this question, the user needs to understand what a primary key and an index are, and how they are related.

A primary key is a column or set of columns that uniquely identifies each row in a table. An index is a data structure that stores a copy of selected columns of a table to improve the speed of data retrieval operations on that table.

Now, let's go through each option and determine whether or not it will use the associated index on EMPNO:

A. select * from EMP where nvl(EMPNO, '00000') = '59384'; This statement will not use the associated index on EMPNO because the NVL function applied to EMPNO will turn an indexed column into an unindexed one. Using functions on indexed columns can result in the optimizer not choosing to use the index.

B. select 1 from EMP where EMPNO = '59834'; This statement will use the associated index on EMPNO because it is directly comparing the EMPNO to a value, and that is what the index is built on.

C. select EMPNO, LASTNAME from EMP where EMPNO = '59384' This statement will use the associated index on EMPNO because it is directly comparing the EMPNO to a value, and that is what the index is built on.

D. select * from EMP where EMPNO = '59384'; This statement will use the associated index on EMPNO because it is directly comparing the EMPNO to a value, and that is what the index is built on.

Therefore, the statement that will not use the associated index on EMPNO is option A.

The Answer is: A

Find more quizzes: