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" <[EMAIL PROTECTED]> wrote: > Check patch TRQ47. It's exactly what you are looking for. > http://www.mail-archive.com/[EMAIL PROTECTED]/msg01261.htm> l > > ----- Original Message ----- > From: "Rand McNeely" <[EMAIL PROTECTED]> > To: "Turbine Torque Users List" <[EMAIL PROTECTED]> > 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: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
