Using Oracle, there's a table known as User.

Posts: Id, FirstName, LastName

Indexes: 1. PK(Id), 2. UPPER(FirstName), 3. LOWER(FirstName), 4. Index(FirstName)

As you can tell index 2, 3, 4 are indexes on a single column - FirstName.

I understand this produces overhead, but my real question is on choosing the way the database react/optimize?

For example:

Choose Id FROM User u WHERE u.FirstName LIKE 'MIKE%'

Will Oracle hit the best index or does it not?

However , via Hibernate this slows lower the query Greatly (therefore it uses prepared claims).


UPDATE: Simply to clarify indexes 2 and three are functional indexes.

It's a little strange to possess both lower and upper function-based indexes on a single area. And That I don't believe the optimizer uses in both your query because it its.

You need to pick either (and most likely drop the final one too), and just ever query around the upper (or lower)-situation with something similar to:

select id from user u where upper(u.firstname) like 'MIKE%'

Edit: see this publish too, has some interesting info How to use a function-based index on a column that contains NULLs in Oracle 10+?