Oracle limit/offset

[Rand McNeely <ra...@attbi.com>]

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?