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
patch
Description: Binary data
-- To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]> For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
