Re: Question and suggestion regarding rownum

Dave,

you got a JDBC example (or a link to one)?

--mcs

"Dave Hau" <davehau_nospam_123_at_nospam_netscape.net> wrote in message news:3FB40F1F.6010206_at_nospam_netscape.net...
> 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 - 21:41:01 CST