(I have read all the questions referenced in Hibernate Pagination using HQL but none of them is a possible solution in my case.)
I have a very complicated query written in HQL that has an order by on an indexed column. When Hibernate's built-in paging facilities (setFirstResult, etc.) are used, any page expect the first page takes several seconds to return where as the first page returns in <100ms.
Analysis of the explain plan by our DBA seems to suggest the DBMS decides to run the complicated query completely for all rows up to the upper bound of the page and then return the range specified.
For example, for the Hibernate generated query:
SELECT *
FROM (SELECT row_.*, ROWNUM rownum_
FROM (<very complicated queries>) row_
WHERE ROWNUM <= 100000) row2_
WHERE rownum_ > 99990
The DBMS would run the row2_ sub-query to really fetch 100k rows and discard the first 99990 to give 10.
Obvious, the "very complicated query" is too complicated to be optimized by machine, so I decided to manually write a query that takes paging into account. The query is able to load any page within 100ms, but requires selecting from a sub-query (in the from clause). Unfortunately HQL does not support this, so I am stuck.
So does anyone know any alternative ways to do paging in HQL or ways to work around the no sub-query in from clause limitation?
Details:
Database is Oracle 11g. Hibernate version is 3.5.
The "very complicated query" selects about 10 different sub-queries in the select part from a single drive table with no joins in the main query. (This is to work around HQL's limitation on unrelated left joins.) Sorting can only happen on columns on the driving table.
The query I wrote basically selected all the IDs (of the driving table entity) that can appear in the "page" in a sub-query and then limited the main query to these IDs:
driverTable.id in
(SELECT x.i
FROM (SELECT z.i, ROWNUM r
FROM (SELECT A.ID i
FROM DrivingTable a
ORDER BY A.ID DESC) z) x
WHERE x.r BETWEEN :page * 100 + 1 AND :page * 100 + 100)
This successfully tricked the query optimizer to apply the row number limits before computing the complicated main query.
