Rafal, You're absolutely right. I had submitted a patch a while back to add basic limit support to Oracle using "where rownum ..." but I forgot that doesn't work if you combine it with order by. Wrapping the whole query as a subquery is a better solution.
-- Bill ----- Original Message ----- From: "Rafal Maczewski" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, September 23, 2002 6:03 AM Subject: [patch] limit and offset on Oracle databases > Hello, > > There is a problem when using Criteria limit and offset with Oracle > database. For example: > Criteria criteria = new Criteria(); > criteria.addAscendingOrderByColumn( Tab1Peer.COL2 ); > criteria.setLimit( 10 ); > List list = Tab1Peer.doSelect( criteria ); > > Now list does not contain correct results. This is caused by the way SQL > query is created and by the way Oracle uses the rownum pseudocolumn. > The query created by Torque lookes like this: > > SELECT tab1.col1, tab2.col2 FROM tab1 WHERE rownum <= 10 ORDER BY tab1.col2 > > Now Oracle gets 10 rows from the database in any order and then sorts them > using the ORDER BY column. > > Things get even worse when using limit and offset at the same time. > Torque uses rownum for limiting number of rows and manually skips "offset" > results. So on Oracle using: > criteria.setLimit( 10 ); > criteria.setOffset( 100 ); > will not return any rows. > > What I suggest to solve this problem is to rewrite the original query (using > Oracle rownum pseudocolumn and subqueries) so that the results returned > would be correct and we would not have to scroll through the results when > using criteria.setOffset(); > > So instead of the following query: > > SELECT tab1.col1, tab1.col2 FROM tab1 ORDER BY tab1.col2 > > we can build query: > > SELECT B.* FROM ( > SELECT A.*, rownum as TORQUE$ROWNUM FROM ( > SELECT tab1.col1, tab1.col2 FROM tab1 ORDER BY tab1.col2 > ) A > ) B WHERE B.TORQUE$ROWNUM > offset > AND B.TORQUE$ROWNUM <= offset + limit > > This query correctly sorts the results and solves the "rownum problem" and > it runs much faster with large offsets, since the rows that are skipped are > processed on the server and are not sent to the client. > > I have attached a patch for BasePeer.java that creates the above query. > Also, I think that something should be done about the way the function > BasePeer.createQueryString works. It takes criteria object and creates query > string for that object. The problem is that when database supports native > limit and/or offset, this function will modify passed criteria object and > reset limit and/or offset to default value. So the criteria object passed to > the doSelect function is modified inside that function. If you do two > selects using the same function you may get different results. And even > worse, if you call criteria.toString(), the BasePeer.createQueryString > function is called, which resets the limit and offset for the criteria > object. > > > Regards, > > Rafal > > > ---------------------------------------------------------------------------- ---- > -- > To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]> > For additional commands, e-mail: <mailto:[EMAIL PROTECTED]> -- To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]> For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
