Re: Question and suggestion regarding rownum

From: Dave Hau <davehau_nospam_123_at_nospam_netscape.net>
Date: Fri, 14 Nov 2003 06:05:25 GMT
Message-ID: <3FB470A3.2070501@nospam_netscape.net>

Scrollable result set is in the JDBC 2.0 core API, which the Oracle JDBC driver supports. Here's a good tutorial:

http://developer.java.sun.com/developer/Books/JDBCTutorial/

Regards,
Dave

mcstock wrote:

> 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 Fri Nov 14 2003 - 00:05:25 CST