Simplifying Hibernate's Query

I've just looked at the explain plan for queries similar to yours and the plan is exactly the same for both queries, so I'm not sure what performance reasons your DBA is suggesting.

Wrapping the query with select * from ( ... ) where rownum = 1 introduces a STOPKEY that stops the inner query after one row. Oracle knows that you do not actually want to get all results from the subquery and then only take the first row.

Changing the query produced by Hibernate is going to be impossible without modifying the hibernate source code itself.

Note, the reason why this nesting is necessary becomes obvious when you try to introduce an ORDER BY clause:

select ID, FIRSTNAME, LASTNAME 
  from PERSONS 
 where lower(FIRSTNAME) = 'john' 
   and rownum <= 1
 order by LASTNAME

produces different results to

select * from (
    select ID, FIRSTNAME, LASTNAME 
      from PERSONS 
     where lower(FIRSTNAME) = 'john' 
     order by LASTNAME)
  where rownum <= 1

as the where rownum is applied before the order by clause....

EDIT:

For reference here's the output of the explain plan, and that's exactly the same for both queries:

---------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |     1 |   112 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY     |            |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| TABLE_NAME |     1 |   112 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------

The performance can be improved by putting a functional index on lower(FIRST_NAME) but that would be used by both queries exactly the same.

I strongly suggest, that you use query parameters:

Query query = entityManager.createQuery("SELECT a FROM "
    + Person.class.getSimpleName() 
    + " a WHERE lower(a.firstName) = :name");
query.setParameter("name", firstName);
return query.getSingleResult();

This has two important reasons:

• You protect against SQL-injection
• You allow SQL-server to cache parsed query improving consequent executions performance

Considering

select * from (...) where rownum <= ?

wrapper: this costs no performance at all. You can just ignore it.