1

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.

JonathanSK
  • 81
  • 2
  • 6
  • I think this depends on how the data is going to be accessed. In either case, I would have last_name before first_name. But, if it is going to be accessed via UPPER(name), then you will be best served using the functional index in both alternatives. As for the order, you'll have to test both, but also test with two indexes, one with DOB only and one with last/first functional index. – unleashed Apr 10 '17 at 19:44
  • Thanks for the feeback. Could you elaborate on why you would have last name before first name in the index? – JonathanSK Apr 10 '17 at 20:06
  • If you're always going to be searching on first name and last name, then it doesn't matter. But Oracle will use the first column in a composite index, so it makes sense to index last name first if you're going to be searching on last name alone. – David Faber Apr 11 '17 at 02:53
  • @JonathanSK I was thinking about the phone book when I saw this. Most name listings or name searches are centered on last name first. But, as mentioned above, it depends on how you/your application is searching the data. – unleashed Apr 11 '17 at 12:59

0 Answers0