Oracle nested correlated subquery limitation combined with LIMIT clause

[François Devémy]

Hello everyone,


I have a problem in Oracle:
I am using a correlated subquery with a limit clause.
But two things collade to create a bug:
1) limit clause is not supported in Oracle 11, so the limit is rendered (currently) as a nested select
2) there is a limitation to the depth of the correlation: according to the aofficial doc
(at http://docs.oracle.com/cd/E11882_01/server.112/e41084/queries007.htm#SQLRF52357)
"Oracle performs a correlated subquery when a nested subquery references a column from a table referred to a parent statement one level above the subquery"
So you can reference only one level deep. (See also http://stackoverflow.com/questions/1233910/is-there-a-nesting-limit-for-correlated-subqueries-in-oracle)

So the limit clause adds one level of depth to the subquery, and now Oracle cannot recognize the correlation.

For an idea, this is the query I use:

final SelectQuery<Record1<Integer>> instanceQuery = using.selectCount().getQuery();
instanceQuery.addFrom(INST);
instanceQuery.addJoin(ACTIVITY, INST.DBID_.equal(ACTIVITY.INSTANCE_DBID_));
instanceQuery.addConditions(ACTIVITY.DBID_.equal(
      select(ACTIVITY_1.DBID_)
            .from(ACTIVITY_1)
            .where(INST.DBID_.equal(ACTIVITY_1.INSTANCE_DBID_))

            .orderBy(ACTIVITY_1.ACTIVITY_ID_.desc())
            .limit(1)));

This is what is generated:

select count(*)
from NOVA_PROCESS_INST INST
  join NOVA_ACTIVITY_INST ACTIVITY
    on INST.DBID_ = ACTIVITY.INSTANCE_DBID_
where (
  ACTIVITY.DBID_ = (
    select V0 DBID_
    from (
      select
        X.V0,
        rownum RN
      from (
             select ACTIVITY_1.DBID_ V0
             from NOVA_ACTIVITY_INST ACTIVITY_1
             where (
               INST.DBID_ = ACTIVITY_1.INSTANCE_DBID_
             )
             order by ACTIVITY_1.ACTIVITY_ID_ desc

           ) X
      where rownum <= (0 + 1)
    )
    where RN > 0
  )
);

          INST.DBID_ = ACTIVITY_1.INSTANCE_DBID_

          *

ERROR at line 16:

ORA-00904: "INST"."DBID_": invalid identifier

And this query works fine in MYSQL and PostgreSQL.

What can I do?

François

[Lukas Eder]

Hello,

I'm sorry for the delay in this matter.

It's true that the emulation of the LIMIT clause introduces its own set of caveats in Oracle, prior to 12c when OFFSET .. FETCH is finally supported.

In your case, the issue is easily resolved by avoiding the unnecessary correlated subquery and moving the "TOP" clause into a derived table, instead. So, rather than this:

select count(*)
from NOVA_PROCESS_INST INST
  join NOVA_ACTIVITY_INST ACTIVITY
    on INST.DBID_ = ACTIVITY.INSTANCE_DBID_
where (
  ACTIVITY.DBID_ = (
    select V0 DBID_
    from (
      select
        X.V0,
        rownum RN
      from (
             select ACTIVITY_1.DBID_ V0
             from NOVA_ACTIVITY_INST ACTIVITY_1
             where (
               INST.DBID_ = ACTIVITY_1.INSTANCE_DBID_
             )
             order by ACTIVITY_1.ACTIVITY_ID_ desc

           ) X
      where rownum <= (0 + 1)
    )
    where RN > 0
  )
);

You could write this:

select count(*)
from NOVA_PROCESS_INST INST
  join (
    

select V0 DBID_
    from (
      select
        X.V0,
        rownum RN
      from (
             select ACTIVITY_1.DBID_ V0
             from NOVA_ACTIVITY_INST ACTIVITY_1

             order by ACTIVITY_1.ACTIVITY_ID_ desc
           ) X
      where rownum <= (0 + 1)
    )
    where RN > 0

  ) ACTIVITY
    on INST.DBID_ = ACTIVITY.INSTANCE_DBID_

Or simpler, without the Oracle-specific emulated SQL

select count(*)
from NOVA_PROCESS_INST INST
  join (
    

select ACTIVITY_1.DBID_ V0
    from NOVA_ACTIVITY_INST ACTIVITY_1

    order by ACTIVITY_1.ACTIVITY_ID_ desc
    limit 1
  ) ACTIVITY
    on INST.DBID_ = ACTIVITY.INSTANCE_DBID_

I 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 jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[François Devémy]

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).

François

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 ?

[Lukas Eder]

Hi François,

2015-07-15 15:54 GMT+02:00 François Devémy <francoi...@gmail.com>:

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

[François Devémy]

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_ DESC
LIMIT 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