Simplifying Hibernate's Query

Question

I have the following code which perfroms a simple query from a single table of an Oracle database.

entityManager.createQuery(
        "SELECT a FROM " + Person.class.getSimpleName() 
        + " a WHERE lower(a.firstName) = '" + firstName + "'")
        .getSingleResult();

Hibernate produces the following sql:

select
        * 
    from
        ( select
            person0_.id as id75_,
            person0_.FIRSTNAME as FIRSTNAME75_,
            person0_.LASTNAME as LASTNAME75_
        from
            PERSONS person0_ 
        where
            lower(person0_.FIRSTNAME)='john' ) 
    where
        rownum <= ?

Our DBA suggests that this query should be simpler for performance reasons. How can I make hibernate to simplify the query like this:

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

Thanks

Answer 1

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.