Re: Question and suggestion regarding rownum

Or you can use a scrollable cursor in your client app. Pro*C, OCI, JDBC 2.0 all support scrollable cursors. This way you do the query only once, store the entire result set in a server-side cursor, and fetch batches of the rows from the server to the client on demand, using relative or absolute positioning of rows.

HTH,
Dave

mcstock wrote:
> tom's example is good, but for this to really work, it needs an ORDER BY
> clause, and hence needs to be double nested, ie:
>
> SELECT ee.*
> FROM (SELECT ROWNUM AS the_rownum
> ,e.*
> FROM (SELECT *
> FROM emp
> ORDER BY ename) e
> WHERE ROWNUM <= 15) ee
> WHERE the_rownum > 10
> /
>
> this preserves the row order
> 1) the order by is in the inner most query (without which the row order is
> not guaranteed repeatable)
> 2) the rownum is assigned in the first enclosing query
> 3) the max rownum is set in the first enclosing query, based on the ROWNUM
> pseudo-column
> 4) the min rownum is set in the 2nd enclosing query, based on the aliased
> ROWNUM value actually returned from the 1st enclosing query
>
> HOWEVER.... depending on usage patterns there could be a pretty significant
> performance implications of this approach -vs- 1st fetching all rownums (or
> PKs) then using these to page thru the data:
>
> testing on a 14,336 row emp table indexed on deptno with 3,072 records for
> deptno = 10:
> 1) 87 gets to select all rowids or empnos for deptno = 10
> 2) 10 gets (no surprise) to select all data for 10 rows, by rowid
> 3) 10 gets for each subsequent page
> -vs-
> 1) 87 gets to select all rowids or empnos for deptno = 10
> 2) 31 gets (no surprise) to select all data for 10 rows, by primary key
> 3) 31 gets for each subsequent page
> -vs-
> 1) 87 gets to select the 'first' page
> 2) 87 gets to select the 'last' page
> 3) 87 gets to select any page
>
> comparing gets per page for the 3 techniques
>
> ROWID PK ROWNUM
> 1st page 87 87 87
> Next page 10 31 87
> Per Page Averages
> 1 87.00 87.00 87.00
> 2 48.50 59.00 87.00
> 3 35.67 49.67 87.00
> 4 29.25 45.00 87.00
> 5 25.40 42.20 87.00
> 6 22.83 40.33 87.00
>
>
Received on Thu Nov 13 2003 - 17:09:20 CST