I have the following query:
SELECT * FROM USERS WHERE UPPER(FIRST_NAME) = 'JOHN' AND UPPER(LAST_NAME) = 'DOE' AND DOB = '07-SEP-11 00:00:00'
Is there any performance benefit to creating a functional index:
CREATE INDEX functional_idx ON USERS (UPPER(FIRST_NAME), UPPER(LAST_NAME), DOB)
vs the following:
CREATE INDEX dob_name_idx ON USERS (DOB, FIRST_NAME, LAST_NAME)
The first index does a RANGE SCAN on the index with Access Predicates.
The second index does a RANGE SCAN on the index with Access Predicates = DOB and Filter Predicates = UPPER(FIRST_NAME) and UPPER(LAST_NAME).
In my instance both have the same cost. Would the first index create better performance in some instances or would one be better going with the second (non functional) index?
Thanks.