how to speed up hibernate criteria 'ilike queries' with oracle database

Question

We are using hibernate with oracle (11) and have severe performance problems due to the usage of 'ilike', the case insensitive 'like'.

The java / hibernate code looks like:

 c1.add( Restrictions.ilike("address", address) );

which results in a sql statement like

 select * from ACCOUNT where lower(ADDRESS_1) = ?

The 'lower' function makes it impossible for oracle to use an index which results in a full table scan.

I was thinking about introducing a new column to the db table that contains the lower case content of address. I could then use :

c1.add( Restrictions.ilike("addressLCase", address.toLowerCase()) );

... but I really don't like the idea to store the content twice...

Then I thought of creating an index with lower case

CREATE INDEX ADDRESS_1_IDX ON ACCOUNT  lower(  ADDRESS_1   )  ;

but this didn't work, since I cannot convince the optimizer to use this index...

So what can I do to create a fast query with hibernate criteria API and 'ilike' ?

Answer 1

The function based index would be my choice. Just remember:

• Use the Cost Based Optimizer. Function based indexes are only visible to the Cost Based Optimizer and will not be used by the Rule Based Optimizer ever.

Excerpt from this article.