Scott,
> 2. Using a database that provides offset and limit mechanisms that are
> supported by torque will produce the best results - i.e. MySql, PostgreSQL
> currently and Oracle when someone gets around to completing the support.
I had submitted patch a while back to add support for the limit mechanism in
Oracle (... where rownum < N). I don't know if Oracle has an offset
mechanism at all, though. "... where rownum > N1 and rownum < N2" doesn't
work the way you expect it to, since the rownum pseudocolumn is not assigned
a value until after all the WHERE clauses get evaluated.
the following hack for Oracle gives you a limit AND offset mechanism, and
may or may not be difficult to implement in Torque:
SELECT * FROM
(select <original column set>, ROWNUM as __actual_rownum ...
... rest of original query ...)
WHERE __actual_rownum >= offset AND __actual_rownum < (limit + offset)
This isn't perfect -- Oracle might still have to return all the rows in the
original query before the outer query filters them. (I've never looked at a
query like this with EXPLAIN PLAN; YMMV.) But at least you save pushing all
those excess rows over the JDBC connection and into temporary objects that
would just be garbage collected.
-- Bill
--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
