Databases
StackOverflow2013
Postsdbo
POHow to explicitly do paging (or to do sub-query in the from clause) in HQL
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to explicitly do paging (or to do sub-query in the from clause) in HQL
    primarykey
    data
    text
    <p>(I have read all the questions referenced in <a href="https://stackoverflow.com/questions/9009570/hibernate-pagination-using-hql">Hibernate Pagination using HQL</a> but none of them is a possible solution in my case.)</p> <p>I have a very complicated query written in HQL that has an <code>order by</code> on an indexed column. When Hibernate's built-in paging facilities (<code>setFirstResult</code>, etc.) are used, any page expect the first page takes several seconds to return where as the first page returns in &lt;100ms.</p> <p>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.</p> <p>For example, for the Hibernate generated query:</p> <pre><code>SELECT * FROM (SELECT row_.*, ROWNUM rownum_ FROM (&lt;very complicated queries&gt;) row_ WHERE ROWNUM &lt;= 100000) row2_ WHERE rownum_ &gt; 99990 </code></pre> <p>The DBMS would run the <code>row2_</code> sub-query to really fetch 100k rows and discard the first 99990 to give 10.</p> <p>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 <code>from</code> clause). Unfortunately HQL does not support this, so I am stuck.</p> <p>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?</p> <p>Details:</p> <p>Database is Oracle 11g. Hibernate version is 3.5.</p> <p>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.</p> <p>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:</p> <pre><code>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) </code></pre> <p>This successfully tricked the query optimizer to apply the row number limits before computing the complicated main query.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USbillc.cn
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. POHow to explicitly do paging (or to do sub-query in the from clause) in HQL
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload