> From: Bill Schneider <[EMAIL PROTECTED]> > > 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
I saw the recently added support for limit in Oracle, but I could not see why rownum could not also be used to implement an offset. A friend of mine who is an Oracle DBA replied to a query with regards to offset/limit: > or do you mean adding ....... where rownum <=11; to get the first 10 rows. > or where rownum between 1 and 11. > > this is not the normal way we'd do this, pl/sql would be more ideal for the > > and between functions > > rownum is a pseudo column that oracle applies to a resultant set of data ie > if you change the order by clause, row 1 would become a different row. I took this to mean that using rownum for an offset would work, but other methods of achieving this (not implemented as straight SQL) were preferred (although the last sentence would appear to lean towards what you are saying - i.e. The full query is executed before any process involving rownum can be applied). I see what you are saying about rownum not being assigned until after all of the WHERE clauses have been evaluated, but surely the same would apply to the other RDBMS? In any case, if the database is just as inefficient as Village in performing these functions then I would push this through to the database as there is certainly more chance that an enhancement to the RDBMS will make this work quicker than anything that might be done to Village. Your suggested workaround looks interesting and it would certainly be interesting to compare and EXPLAIN of various queries using these two methods. I would imagine that this could be added to BasePeer relatively easily. I don't even have Oracle installed here - I only looked into it because there is a possibility that I will need to port my application over to Oracle in the future and I wanted to ensure that LargeSelect would operate efficiently in that environment. Until that happens it will be up to yourself or some other interested party to attempt to provide this feature (although I understand in this context you are simply informing me of the probable limitation rather than of a requirement for it or an offer to implement it). Thanks for the feedback - good stuff to know. Cheers, Scott -- Scott Eade Backstage Technologies Pty. Ltd. Web: http://www.backstagetech.com.au -- To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]> For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
