Hello Lukas,
I'm curious, what's that hack, and why aren't you happy with it?
>
I need only the first activity (ordered by date) for each instance that
fulfills some criteria.
So I tried something like this (again, simplified, because it normally it
involves some other joined tables):
*SELECT *count(*)
*FROM *NOVA_PROCESS_INST *AS *INST
*JOIN *NOVA_ACTIVITY_INST *AS *ACTIVITY
*ON *INST.*DBID_ *= ACTIVITY.
*INSTANCE_DBID_ **JOIN *(
*SELECT **max*(ACTIVITY_1.READYDATE_) *AS *MAX,
ACTIVITY_1.*INSTANCE_DBID_ **AS *ACTIVITYINSTANCE_DBID_
*FROM *NOVA_ACTIVITY_INST *AS *ACTIVITY_1
*GROUP BY *ACTIVITY_1.
*INSTANCE_DBID_ *) *AS *ACTIVITYQUERY
*ON *(
INST.*DBID_ *= ACTIVITYQUERY.ACTIVITYINSTANCE_DBID_
*AND *ACTIVITY.READYDATE_ = ACTIVITYQUERY.MAX
)
*ORDER BY *ACTIVITY.READYDATE_
*DESCLIMIT *10;
However, it turns out that the date criteria is not specific enough,
multiple rows can have the same date. So I had to have more specific data
in max(…). So I did this “hack”:
*SELECT *count(*)*FROM *NOVA_PROCESS_INST *AS *INST
*JOIN *NOVA_ACTIVITY_INST *AS *ACTIVITY
*ON *INST.*DBID_ *= ACTIVITY.*INSTANCE_DBID_
**JOIN *(
*SELECT
**max*((*cast*(ACTIVITY_1.READYDATE_ *AS VARCHAR*) ||
*cast*(ACTIVITY_1.*DBID_ **AS VARCHAR*))) *AS *MAX,
ACTIVITY_1.*INSTANCE_DBID_
**AS *ACTIVITYINSTANCE_DBID_
*FROM *NOVA_ACTIVITY_INST *AS *ACTIVITY_1
*GROUP BY *ACTIVITY_1.*INSTANCE_DBID_
*) *AS *ACTIVITYQUERY
*ON *(
INST.*DBID_ *= ACTIVITYQUERY.ACTIVITYINSTANCE_DBID_
*AND *(*cast*(ACTIVITY.READYDATE_ *AS VARCHAR*) || *cast*(ACTIVITY.*DBID_
**AS VARCHAR*)) = ACTIVITYQUERY.MAX
)*ORDER BY *ACTIVITY.READYDATE_ *DESC
LIMIT *10;
> 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
>
Thanks for the reference. Very interesting indeed. I hope Oracle fixed all
this in 12c.
François
--
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.
