There seem to be a bug in Hibernate 3 that results in a sub-optimal query if one attempts to fetch one specific portion of the result set, as is typically the case with pagination.
The best-practice to extract one specific page out of the complete result set is to use the ROWNUM keyword with Oracle. The ROWNUM keyword is used to truncate the size of the result set after N items. Oracle will optimize the usage of ROWNUM which can result in drastical improvement of some queries. The outline of a paginated query looks like following:
SELECT *
FROM (SELECT row_.*, ROWNUM rownum_
FROM (SELECT this_.ID AS id3_0_, this_.VERSION AS version3_0_,
this_.NAME AS name3_0_, this_.TYPE AS type3_0_,
this_.marketstatus AS marketst5_3_0_
FROM customer this_
ORDER BY this_.ID ASC) row_
WHERE ROWNUM <= ?)
WHERE rownum_ > ?
There seem unfortunately that there is a bug in Hibernate, and the query that is generated will be:
SELECT *
FROM (SELECT row_.*, ROWNUM rownum_
FROM (SELECT this_.ID AS id3_0_, this_.VERSION AS version3_0_,
this_.NAME AS name3_0_, this_.TYPE AS type3_0_,
this_.marketstatus AS marketst5_3_0_
FROM customer this_
ORDER BY this_.ID ASC) row_)
WHERE rownum_ <= ? AND rownum_ > ?
Though they are semantically the same, the second one will not benefit from Oracle’s optimizations.
Here is the corresponding issue I’ve reported. More information can be found about Oracle ROWNUM in AskTom
://ewernli 