Sub-optimal Pagination with Oracle & Hibernate

There seem to be a bug in Hibernate 3 that results in sub-optimal query if one attempt to fetch one specific portion of the result set, as is the case typcially 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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s