Here's a Way to Code Offset/Limit in Oracle

[Andy Mitchell <an...@oracle.com>]

I've been using Torque for a couple of weeks and noticed that there
isn't offset/limit capability set for Oracle.  It's actually possible to

create these with standard SQL ( rather than using a PL/SQL function to
mimic it ).

The culprit is ROWNUM.  This gets bound to the record before the group
or order phases.  So if you try :

  Example A:
   SELECT letter, rownum
   FROM    alphabet
   ORDER BY letter

you may get results like :
  Letter      Rownum
-----------------------
    A                5
    B                3
    C               23
    D               15
  ...

Also, if you try :
  Example B:
  SELECT letter, rownum
  FROM   alphabet
  WHERE rownum BETWEEN 10 and 20

you'll get back no results.

But it is possible to get what you want if you're bull-headed enough.
The secret is that Oracle allows you to do "inner views", which are just

SQL statements taking the place of tables in the FROM clause.  For
example,

  Example C:
  SELECT *
  FROM   ( SELECT letter from alphabet );

To support offset and limit, you actually have to do two inner views
like so :

  Example D:
  SELECT *
  FROM (
                SELECT vw_$_.*, Rownum AS rownum_$_      -- "OuterView"
               FROM (
                              SELECT letter     -- "InnerView"
                              FROM   alphabet
                            ) vw_$_
              )
  WHERE  rownum BETWEEN 10 AND 20 ;

Why do you need two inner views?  The Oracle parser will work from the
inside out.  So the parser will create a temporary result set with all
of the data from InnerView first.  Then, it'll pass that result set to
OuterView.  Then the result set from that query will be past to the
actual select statement.   InnerView may contain an ORDER BY so you
can't add the ROWNUM here.  If you try to, ROWNUM may not be in
sequential order with order which the records are sent back (like the
example A above).  So, you've got to wait until all the results are back

from the innermost view, and then append the ROWNUM.  You do that by
wrapping it inside OuterView.  OuterView simply selects the result set
from InnerView and adds the ROWNUM field.  Then, the actual select
statement grabs that temporary result set from OuterView and performs
the limit/offset filter.  If you try to skip the OuterView step, you'll
run into a situation like Example B above where you can't actually
select against the ROWNUM.

To summarize :
- the innermost view (InnerView) gets back the data you want
- the second view (OuterView) adds on the ROWNUM in the correct sequence

- the select statement grabs the result set from the second view.  Now,
ROWNUM is not a "magical" Oracle column; it's just a simple number
column that you can use to filter.

Inner views are supported back to version 7.3.4.

This is truly ugly :) but it does work.  It also requires two-plus
passes through the data to get the answer, but it should be faster than
shipping all the records across to Turbine and having the user perform
the filter in the code since it's done on the server.

I modified three Turbine files :

org.apache.turbine.util.db.adapter.DB - added LIMIT_STYLE_ORACLE
org.apache.turbine.util.db.adapter.DBOracle - added a getLimitStyle()
method
org.apache.turbine.om.peer.BasePeer - added some code in
createQueryString to handle Oracle limits/offsets.  Looks like it should

be done in createPreparedStatement too.

There are a couple of bugs with the code.  First, it adds an unexpected
column, ROWNUM, to the final result set.  That'll map correctly in the
xxPeer.populateObject, but it may throw off people using the village
records.  Also, the inner views mask the names of the columns.  If a
user creates a join with two columns of the same name, they'll need to
alias one of them.


---------------------------------------------------------------------
To unsubscribe, e-mail: turbine-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: turbine-dev-help@jakarta.apache.org