Hopbot log for 2009-04-01 - Helma IRC channel: #helma on irc.freenode.net

2009-04-01:

[13:27] <hannesw> anybody willing to get paginated collections working with oracle?
[13:27] <hannesw> http://rifers.org/blogs/gbevin/2004/1/25/limit_and_offset_in_oracle
[13:30] <hannesw> I was considering downloading oracle 10g express edition, but +200 mb still is a lot just for testing one feature
[13:37] <simono> i can help... do you have a testcase? :)
[13:49] <hannesw> hey simono
[13:49] <hannesw> nope...
[13:49] <hannesw> i just need an sql query that works.
[13:49] <hannesw> :-)
[13:53] <hannesw> there are a lot of variants floating on the web, and I don't know what works (best)
[13:54] <hannesw> just read the comments here: http://www.petefreitag.com/item/451.cfm
[13:54] <hannesw> it's madness.
[13:59] <simono> oh i was just about to test this http://dev.helma.org/trac/helma/changeset/9557
[14:00] <hannesw> the HopObject.getCollection() feature? Ok, testing is never wrong.
[14:00] <hannesw> But what I need specific testing for is the new limit/offset feature, which won't work on oracle currently.
[14:00] <simono> no the limit/offset
[14:00] <hannesw> because I need to craft a special query for oracle, and i don't know how to do that exactly.
[14:01] <simono> wanted to test that.. i see. well i'm no oracle guru either. i trust this guy: http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html
[14:01] <hannesw> so what i need is someone who tries a few things from the net and then tells me how to implement limit/offset/pagination functionality for oracle
[14:02] <hannesw> ok, i can implement that.
[14:04] <simono> ok, in our code i see limit/offset queries in two places.. i think robert wrote them. i don't want to pastebin this stuff
[14:04] <hannesw> hm, i implemented limit/offset just yesterday. you sure it's used in your code?
[14:05] <hannesw> you mean in custom queries?
[14:05] <simono> like so http://helma.pastebin.com/d26f2a1e0
[14:05] <simono> yes custom queries
[14:06] <hannesw> thanks
[14:07] <simono> no problem, just forwarding roberts work :)
[14:07] <hannesw> the one thing that confuses me: in the ask tom document you linked, it says:
[14:07] <hannesw> Also confusing to many people is when a ROWNUM value is actually assigned. A ROWNUM value is assigned to a row after it passes the predicate phase of the query but before the query does any sorting or aggregation.
[14:08] <hannesw> so that means rownums are assigned before the inner select is ordered? Doesn#t that mean the result will be wrong?
[14:08] <simono> yes rownum is assigned before the sorting, i think that's why the sorting has to happen in the subquery
[14:09] <hannesw> but rownum gets assigned in the subquery before the sorting???
[14:11] <simono> i'm not sure if every query has it's own rownum, that would explain the behaviour i see. we always do a 'select *" around the real query to get the correct rownums for sorted results
[14:11] <hannesw> ok, i'll try to implement something and you tell me if it's working. is that ok?
[14:11] <simono> yes, sure
[14:30] <hannesw> one thing i don't understand is: why doesn't pagination simply work like this:
[14:30] <hannesw> select * from (select * from foo order by bar) where start < ROWNUM < end
[14:31] <hannesw> the example given in the ask-tom document is much more complex:
[14:31] <hannesw> select *
[14:31] <hannesw> from ( select /*+ FIRST_ROWS(n) */
[14:31] <hannesw> a.*, ROWNUM rnum
[14:31] <hannesw> from ( your_query_goes_here,
[14:31] <hannesw> with order by ) a
[14:31] <hannesw> where ROWNUM <=
[14:31] <hannesw> :MAX_ROW_TO_FETCH )
[14:31] <hannesw> where rnum >= :MIN_ROW_TO_FETCH;
[14:33] * simono thinks about it
[14:36] <simono> maybe that's an optimization... the paragraph below hints towards that " (the query) incorporates a top-N query and hence benefits from the top-N query optimization I just described"
[14:36] * simono starts up sqldeveloper to try it out
[14:44] <simono> makes a difference, see http://helma.pastebin.com/d39e21a1 first query returns correct result. second query returns 0 rows
[14:45] <hannesw> ahhhhh now i understand.
[14:45] <hannesw> it's because rownum is only assigned when the row is "consumed" (whatever)
[14:46] <hannesw> so you have to first cut at the end... and then at the start.
[14:46] <hannesw> *sigh* ok, I'll implement this.
[14:55] <simono> reading the forums i remember: ROWNUM >= X where X > 0 always returns zero rows
[14:56] <simono> that's why the >= comparision has to happen *after* ROWNUM has already been assigned - in the outer query
[14:56] <simono> oh that's what you said, alright then
[15:00] <hannesw> ok, i've got something that i think might work. will test locally to see if queries look like they should first :-)
[15:01] <simono> ok, tell me when to update
[15:32] <hannesw> simono: this is the sql I'm now generating - can you check if it runs correctly on oracle (and returns the correct results)?
[15:32] <hannesw> http://helma.pastebin.com/m242b460c
[15:33] <simono> checking it out now
[15:37] <simono> works, see http://helma.pastebin.com/m237cae54
[15:37] <hannesw> wohoo!
[15:37] <hannesw> thanks.
[15:37] <simono> :) np
[15:37] <hannesw> committing...
[15:48] <hannesw> done: https://dev.helma.org/trac/helma/changeset/9562
[15:50] <simono> ok.. will try it again 2morrow in real code.. gottago.. bye
[15:52] <hannesw> bye

 

 

In the channel now:

Logs by date: