Using Oracle build-in UPPER function in WHERE lead to bad performance of SELECT statement?

Question

Our Oracle database application contains a Table called PERSON
This Table contains a column called PERSON_NAME
Also we have an INDEX on this column to speed up SELECT using this column

So when we use following SQL statement performance is fine

SELECT *
FROM PERSON
WHERE 1=1
AND PERSON_NAME = '  Yajli  '
;

But in some business cases
We need to make search by PERSON_NAME is NOT case sensitive

So We try following SQL statement

SELECT *
FROM PERSON
WHERE 1=1
AND UPPER(PERSON_NAME) = UPPER('  YajLi  ')
;

But it lead us to a BAD performance and SELECT query in this case take a lot of time

Any Help How to enhance performance of SELECT on both cases together
* search by PERSON_NAME is NOT case sensitive
* search by PERSON_NAME is case sensitive

Answer 1

You would have bad relative performance because the original query uses an index on PERSON_NAME. However, when you apply a function, Oracle no longer uses the index.

If this is something that you need to do frequently (as you suggest), then you can create an index on the function:

CREATE INDEX idx_person_upper_personname ON PERSON(UPPER(PERSONNAME));

Then Oracle will use this index when you use the function UPPER(PERSON) in the WHERE clause.