Databases
StackOverflow2013
Postsdbo
POBehaviour of sub query wrapped in a pagination select statement and ROWNUM
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POBehaviour of sub query wrapped in a pagination select statement and ROWNUM
    primarykey
    data
    text
    <p>I've used the following "wapper" to implement pagination on a few occassions, and often referred to it as a "standard paging select". So to paginate the results from any select statement, simply wrap the select statement in a "standard paging select".</p> <pre><code> SELECT * FROM (SELECT a.*, ROWNUM rnum FROM (( ( subquery: put your select * from ....... here ..) )) a WHERE ROWNUM &lt;= ${resultEnd} ) WHERE rnum &gt;= ${resultStart} </code></pre> <p>My question is with respect to behaviour of the paging bit and the magic around ROWNUM. From my understanding the subquery does <strong><em>not</em></strong> select the full resultset each time, and was looking for confirmation or otherwise?</p> <p>Is the following, a correct description of the paging behaviour shown above, for example if page size were 50:</p> <p>to select:</p> <ul> <li>Page 1(eg 0-50)subquery selects 0-50 rows</li> <li>Page 2(eg 51-100)subquery selects 0-100 rows(outer query then limits it to 50-100)</li> <li>Page 3(eg 101-150)subquery selects 0-150 rows(outer query thenlimits it to 101-150) </li> <li>Page 4(eg 151-200)subquery selects 0-200 rows(outer query then limits it to 151-200) </li> <li>Page 5(eg 201-250)the subquery selects 0-250 rows(outer query then limits it to 201-250)</li> </ul> <p>... and so on, you can see the pattern.</p> <p>The further you go into the "pages" the more work the subquery has to do. Is this correct? If so how does ROWNUM do this?</p> <p>Thanks in advance. And feed back appreciated</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USjohnm
    1. USjohnm
    plurals
    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. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COGood tom kyte article: http://www.oracle.com/technetwork/issue-archive/2007/07-jan/o17asktom-093877.html
      singulars
      1. POBehaviour of sub query wrapped in a pagination select statement and ROWNUM
      1. UStbone
 

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