3

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

Ahmed Nabil
  • 17,392
  • 11
  • 61
  • 88
  • 5
    You could create an index on `upper(person_name)` –  Feb 08 '17 at 12:30
  • 3
    The second query is slow because it cannot use the existing index. Crete a function-based index on `UPPER(PERSON_NAME)` –  Feb 08 '17 at 12:30

1 Answers1

11

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786