query on where condition - upper(rtrim(column)) = ?

663506

Hi,

I have a table StudentGrades. This table has an index on the column "grade" of type varchar2.
The values of "grade" column contain string values like: 'A', 'a', 'A ', 'a ', 'A ', i.e. mixed cases and containing white spaces at times.

If I run a query below, will it use the index on grade?

select * from StudentGrades where upper(rtrim(grade)) = ?

where ? is an input parameter from the program and the value has been trimmed and converted to uppercase.

Thanks,
Jason

Karthick2003

no the index wont be used. you need to create a function based index for that.

663506

Can you point me to a page that explains function based index and how to. I haven't used this before. Thanks.

Karthick2003

http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/data_acc.htm#i9946

C Patel

You can create a function-based index, but it seems the root of the problem is the database design. Why are the grade values, free-form? It would make more sense that all possible grades are stored in a separate table and referenced by a key (i.e. normalization).