db-torque-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Edgar González <...@valhallaproject.com>
Subject [PATCH] BasePeer - Offset & Limit handling enhancement for Oracle (best performance)
Date Tue, 09 Mar 2004 16:56:39 GMT
Hi,

 

We improved the query generated for Oracle when Limit and Offset are setted.

 

The original query structure is:

----------------------------------------------------------------------------
-----------------

SELECT B.* FROM (

  SELECT A.*, rownum as TORQUE$ROWNUM FROM (

    query

  ) A

) B WHERE B.TORQUE$ROWNUM > offset AND 

  B.TORQUE$ROWNUM <= offset + limit

----------------------------------------------------------------------------
-----------------

 

And our proposal is:

 

----------------------------------------------------------------------------
-----------------

SELECT B.* FROM (

  SELECT A.*, rownum as TORQUE$ROWNUM FROM (

    query

  ) A WHERE rownum <= offset + limit

) B WHERE B.TORQUE$ROWNUM > offset

----------------------------------------------------------------------------
-----------------

 

 

The tests against a query with 76,000 rows showed reduction from 24.945 sec
to 1.472 sec in a consistent way.

 

We coded a TestCase for this, in the CriteriaTest class and also modified
the src/test/TurbineResources.properties to set an Oracle database available
for the TestCase.

 

The patches are attached.

 

 


Mime
View raw message