You are viewing a plain text version of this content. The canonical link for it is here.

Posted to torque-dev@db.apache.org by Edgar González <eg...@valhallaproject.com> on 2004/03/09 17:56:39 UTC

[PATCH] BasePeer - Offset & Limit handling enhancement for Oracle (best performance)

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.