db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Scott Eade <se...@backstagetech.com.au>
Subject Re: Oracle offset/limit
Date Wed, 02 Apr 2003 13:16:33 GMT
Also check out LargeSelect.

HTH,

Scott
-- 
Scott Eade
Backstage Technologies Pty. Ltd.
http://www.backstagetech.com.au
.Mac Chat/AIM: seade at mac dot com


On 1/04/2003 4:38 AM, "Alexandre Kozyrev" <aleks@trimap.com> wrote:

> Check patch TRQ47. It's exactly what you are looking for.
> 
http://www.mail-archive.com/turbine-torque-dev@jakarta.apache.org/msg01261.htm>
l
> 
> ----- Original Message -----
> From: "Rand McNeely" <xmcneely@attbi.com>
> To: "Turbine Torque Users List" <torque-user@db.apache.org>
> Sent: Monday, March 31, 2003 1:25 PM
> Subject: Oracle offset/limit
> 
> 
> I am investigating using Torque/Turbine/Velocity to build a web application.
> One of the requirements is a scrollable record set.  It seems that Torque
> provides this functionality with Criteria.setLimit(int) and
> Criteria.setOffset(int).  The implementation appears to be using Oracle's
> rownum pseudo column.  I see a possible problem with this:
> 
> Oracle applies the pseudo column before ordering the resultset.  For
> example, if I do "select key,rownum from table where rownum < 10 order by
> key" to get the first 10 records, it will grab 10 records and then sort
> those as opposed to grabbing all records, sorting them and returning only
> the first 10.  Usually this leads to the same result if the table has be
> analyzed recently and there are small amounts of inserts and deletes but if
> the index is fragmented, the results will differ and I may not get the first
> 10 rows.
> 
> Here's the method I've used in the past to handle this problem with Oracle:
> 
> 
> PreparedStatement[] sqls = [
>   conn.prepareStatement("select * from my_table where key_field1 = ? and
> key_field2 = ? and key_field3 > ? order by
> key_field1,key_field2,key_field3"),
>   conn.prepareStatement("select * from my_table where key_field1 = ? and
> key_field2 > ? order by key_field1,key_field2,key_field3 "),
>   conn.prepareStatement("select * from my_table where key_field1 > ? order
> by key_field1,key_field2,key_field3")];  //Multiple columns in key
> 
> public List getNextRecords(currentRecord,int recordCount) {
>   List records = new ArrayList(recordCount);
>   buildNextRecordList(currentRecord,recordCount,records,0);
>   return records;
> }
> 
> protected void buildNextRecordList(MyTable currentRecord,int numRecords,List
> records,int currentSql) {
>   if (currentSql == sqls.length) {
>       return; //Out of SQL statements, end of table
>   }
>   PreparedStatement sql = sqls[currentSql]
>   for (int i=1;i < sqls.length - currentSql;i++) {
>       sql.setInt(i,currentRecord.getKeyValues[i]); //Set bind variables
>   }
>   ResultSet res = sql.executeQuery();
>   while (res.next()) {
>       MyTable myTable = (MyTable) buildObj(res);
>       records.add(myTable);
>       if(records.size() == numRecords) {
>           res.close(); //Done
>           return;
>       }
>   }
>   res.close(); //This record set is out of data
>   //Still need more records, try the next SQL statement
>   buildNextRecordList(currentRecord,numRecords -
> records.size(),records,++currentSql);
> }
> 
> If the prepared statements are cached, this is actually quite fast depending
> on the uniqness of the key fields.  I can do this in Torque but coding it
> for each object/table would result in quite a bit of work.  Is there a
> prefered approach for handling this situation?  Is there anyone else out
> there with this problem?
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
> 


Mime
View raw message