indexing question 1

[inunix]

Hi

If I use UPPER in my where clause for the indexed column, will it take the index?

I mean..

assume my query is something like this..

select COL1, COL2, COL3 from table1
where UPPER(COL4) like UPPER('text%')

Pls advice. Thx.

 

[carp]

It depends. If you have a function-based index that is formed on the UPPER function, then it will be used. If you are using an ordinary index, it will not be used.

 

[inunix]

Thanks, Carp.

Asssume this is not a function based index, so if the where clause looks like these shown below, which one will use indexes and which one not.
1. where UPPER(COL4) like UPPER('text%')
2. where COL4 like UPPER('text%')
3. where COL4 = UPPER('text')
4. where UPPER(COL4) = UPPER('text')

 

[carp]

2 and 3 should consider using the index, since the column itself is not being transformed.

re: #2. While the LIKE operator can potentially disable an index, if you provide the leading edge of the target string, the index is not excluded.