Which query would use the UPPER_NAME_IDX index?

Evaluate the following SQL statement:
CREATE INDEX upper_name_idx
ON product_information(UPPER(product_name));
Which query would use the UPPER_NAME_IDX index?

Evaluate the following SQL statement:
CREATE INDEX upper_name_idx
ON product_information(UPPER(product_name));

Which query would use the UPPER_NAME_IDX index?

A.
SELECT UPPER(product_name)
FROM product_information
WHERE product_id = 2254;

B.
SELECT UPPER(product_name)
FROM product_information;

C.
SELECT product_id
FROM product_information
WHERE UPPER(product_name) IN (‘LASERPRO’, ‘Cable’);

D.
SELECT product_id, UPPER(product_name)
FROM product_information
WHERE UPPER(product_name)=’LASERPRO’ OR list_price > 1000;



Leave a Reply 3

Your email address will not be published. Required fields are marked *


miriam

miriam

I think answer D also uses the function index UPPER_NAME_IDX. C is correct. But why D is not correct? Please help. thanks.

Eddie Chang

Eddie Chang

Because “OR” this word.
if the condition “UPPER(product_name)=’LASERPRO’ ” is not matched,
the sql statement should find another condition “list_price”.

That will cause full table scan.

miriam

miriam

oh yes I see. thank you for good help.