Hi François, 2015-07-15 15:54 GMT+02:00 François Devémy <[email protected]>:
> Hello Lukas, > > > > Thanks for your answer. > > It seems that I oversimplified my query to present reproduction steps. > > I need to have the first activity for each instance that fulfills some > criteria. > > I did work around the bug using a derived table like you suggested, but > inside the derived table I GROUP BY ACTIVITY.INSTANCE_DBID_ and try to > select a unique row for each group (using a pretty bad hack that I am not > happy with). > I'm curious, what's that hack, and why aren't you happy with it? > PS : Just out of curiosity (this might not solve this particular problem), > but why don’t you emulate limit offset in Oracle with a window function > like you do in DB2 and SQL SERVER 2008 ? > Good question. At some point, we did emulate Oracle's limit offset using window functions as this made things simpler in jOOQ's internals. But it turns out that ROW_NUMBER() OVER() is much less easily optimisable than ROWNUM, especially when queries get very nasty. Some details can be seen in this interesting benchmark here: http://www.inf.unideb.hu/~gabora/pagination/results.html And the relevant discussion here on the user group: https://groups.google.com/forum/#!msg/jooq-user/G9Op6cQwMkY/xC3DBtrefGMJ In fact, I think it would be worth referencing the above benchmark also from the jOOQ manual. It's really interesting to see the substantial performance impact of the different approaches to pagination. Hope this helps, Lukas -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
