ok, so it's just a small (but probably important) optimization. thanks for the heads up.
-----Original Message----- From: Ebersole, Steven [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 25, 2003 11:52 AM To: 'OJB Users List' Subject: RE: Using setStartAtIndex with Oracle 8i The problem is that you can only bound the upper end of the results, there is no way (that I know of) to specify a "start row". I think the best your going to be able to do is to set the Statement.maxRows = endAtIndex, which will append a ROWNUM constraint to the query. But as you get towards the end of the result set, this will become pointless. In short, this is simply not possible in Oracle (8i or earlier). Becuase the server cursors cannot be scrolled you would always have to scroll through the first n-1 rows in order to get to row n as the startAtIndex. |-----Original Message----- |From: Matthew Baird [mailto:[EMAIL PROTECTED] |Sent: Tuesday, March 25, 2003 1:27 PM |To: OJB Users List |Subject: RE: Using setStartAtIndex with Oracle 8i | | |sounds like there is a chance to do a platform specific |optimization that starts at and rturns a limited number of |rows for Oracle. Anyone know the specifics of how to do |this? I'm thinking something along the lines of the |.size() vs COUNT(*) method of finding the number of |returned results. | |-----Original Message----- |From: Ebersole, Steven [mailto:[EMAIL PROTECTED] |Sent: Tuesday, March 25, 2003 11:13 AM |To: 'OJB Users List' |Subject: RE: Using setStartAtIndex with Oracle 8i | | |Not sure how it actually gets filled (all at once vs |asynchronously). | |FYI, 9i does support scrollable cursors and I believe that |is then extended |into the JDBC drivers. Might want to check out upgrading |if this is a big |concern. | | | | |-----Original Message----- | |From: Andrew Gilbert [mailto:[EMAIL PROTECTED] | |Sent: Tuesday, March 25, 2003 12:47 PM | |To: OJB Users List | |Subject: RE: Using setStartAtIndex with Oracle 8i | | | | | |Learn something new every day... | | | |http://otn.oracle.co.kr/docs/Oracle817/java.817/a83724/resl | |tse1.htm#1017614 | | | |Steven is correct, in that the db doesn't implement | |scrollable cursors, so the Oracle solution is dependent on | |client side caching. | | | |It isn't clear to me how fast the cache gets filled, ie | |all at once or in response to movement. In our case we are | |paging and seldom ever iterate more than a few percentage | |points of a large set. Perhaps that explains our ignorant | |happiness? | | | |So we obviously need to look at OracleResultSetCache as well. | | | |Thanks Steven. | | | |> -----Original Message----- | |> From: Ebersole, Steven [mailto:[EMAIL PROTECTED] | |> Sent: Tuesday, March 25, 2003 12:57 PM | |> To: 'OJB Users List' | |> Subject: RE: Using setStartAtIndex with Oracle 8i | |> | |> | |> Oracle 8i (and previous) does _not_ support scrollable | |> cursors. Thus, the | |> entire result set is transferred into a client-side memory | |> cache for result | |> sets declared as scrollable. All result set methods are | |then executed | |> against this memory cache. Obviously, for very |large cursors | |> this will be | |> very inefficient. | |> | |> Your best options are: | |> 1) Don't use scrollable result sets; | |> 2) Implement a better client-side cursor cache ( see the | |> javadocs for the | |> OracleResultSetCache interface for more details) | |> | |> | |> | |> | |> |-----Original Message----- | |> |From: Matthew Baird [mailto:[EMAIL PROTECTED] | |> |Sent: Tuesday, March 25, 2003 11:40 AM | |> |To: OJB Users List | |> |Subject: RE: Using setStartAtIndex with Oracle 8i | |> | | |> | | |> |are there any oracle experts that could shed some | |light on this. | |> | | |> |the actual code should not bring back all the |results and | |> |only return a subset, but rather it should use the | |> |ResultSet.absolute method to bop around and |only retrieve | |> |the objects it needs. This code is in RsIterator and | |> |PersistenceBrokerImpl | |> | | |> |anyone? | |> | | |> |-----Original Message----- | |> |From: [EMAIL PROTECTED] | |> |[mailto:[EMAIL PROTECTED] | |> |Sent: Tuesday, March 25, 2003 9:29 AM | |> |To: OJB Users List | |> |Subject: RE: Using setStartAtIndex with Oracle 8i | |> | | |> | | |> | | |> |Hi, | |> | | |> |I think when you use setStartIndex method, all |the results | |> |are retrieved | |> |from DB and then your query returns only the |subset that | |> |you specified. | |> |I ran into that problem too and I had to add |an additional | |> |criteria to my | |> |query : | |> | | |> |Criteria crit = new Criteria(); | |> |crit.addSql("rownum < 100"); | |> |Query q = QueryFactory.newQuery(Foo.class, crit); | |> |Collection c = broker.getCollectionByQuery(q); | |> | | |> |The problem is that it uses oracle specific stuff. | |> |But at least, performance is much better ! | |> |Hope that helps, | |> | | |> |Thomas | |> | | |> | | |> | | |> ||----------------------------------------------------------- | |> |---------- | |> |To unsubscribe, e-mail: |[EMAIL PROTECTED] | |> |For additional commands, e-mail: |[EMAIL PROTECTED] | |> | | |> | | |> ||----------------------------------------------------------- | |> |---------- | |> |To unsubscribe, e-mail: |[EMAIL PROTECTED] | |> |For additional commands, e-mail: |[EMAIL PROTECTED] | |> | | |> | |> | |----------------------------------------------------------- | |---------- | |> To unsubscribe, e-mail: [EMAIL PROTECTED] | |> For additional commands, e-mail: [EMAIL PROTECTED] | |> | |> | | | |----------------------------------------------------------- | |---------- | |To unsubscribe, e-mail: [EMAIL PROTECTED] | |For additional commands, e-mail: [EMAIL PROTECTED] | | | |----------------------------------------------------------- |---------- |To unsubscribe, e-mail: [EMAIL PROTECTED] |For additional commands, e-mail: [EMAIL PROTECTED] | | |----------------------------------------------------------- |---------- |To unsubscribe, e-mail: [EMAIL PROTECTED] |For additional commands, e-mail: [EMAIL PROTECTED] | --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
