Are these ROW_NUMBER caveats still true?

[Katherine Marsden <km...@sbcglobal.net>]

http://db.apache.org/derby/docs/10.9/ref/rreffuncrownumber.html

  * Derby does not currently allow the named or unnamed window
    specification to be specified in the OVER() clause, but requires an
    empty parenthesis. This means the function is evaluated over the
    entire result set.
  * The ROW_NUMBER function cannot currently be used in a WHERE clause.
  * Derby does not currently support ORDER BY in subqueries, so there is
    currently no way to guarantee the order of rows in the SELECT
    subquery. An optimizer override can be used to force the optimizer
    to use an index ordered on the desired column(s) if ordering is a
    firm requirement.


I see these possibly relevant fixes:

https://issues.apache.org/jira/browse/DERBY-3634
https://issues.apache.org/jira/browse/DERBY-4397

Also are there still performance considerations with ROW_NUMBER?
I see:
https://issues.apache.org/jira/browse/DERBY-3505

Are there still other concerns with ROW_NUMBER?

Thanks

Kathey

Re: Are these ROW_NUMBER caveats still true?

[Dag Wanvik <da...@oracle.com>]

Hi Kathey,

please see inlined comments.

On 08.03.2013 03:03, Katherine Marsden wrote:
> http://db.apache.org/derby/docs/10.9/ref/rreffuncrownumber.html
>
>   * Derby does not currently allow the named or unnamed window
>     specification to be specified in the OVER() clause, but requires
>     an empty parenthesis. This means the function is evaluated over
>     the entire result set.
>
The syntax is somewhat more liberal now, but we didn't document it yet
as it doesn't quite carry its weight until we add more features to the
windowing: You can now write for example:

select row_number() over r, b, sum(a) from t5 group by b window r as ()

i.e. the window definition can now be explicit as well as implicit, but
it still needs an empty "()", i.e. the row_number function is evaluated
over the entire result set (no partition or frame clause) and  without
ordering (no ordering clause). Now that we have ordering in subqueries
this latter feature could be implemented though, fairly easily I imagine.

>   * The ROW_NUMBER function cannot currently be used in a WHERE clause.
>   * Derby does not currently support ORDER BY in subqueries, so there
>     is currently no way to guarantee the order of rows in the SELECT
>     subquery. An optimizer override can be used to force the optimizer
>     to use an index ordered on the desired column(s) if ordering is a
>     firm requirement.
>

These are no longer true, as you have seen, this has been fixed. I
notice the documentation here doesn't mention the OFFSET/FETCH clause as
an alternative; I think we ought to do that, as it potentially more
efficient, see below.
>
>
> I see these possibly relevant fixes:
>
> https://issues.apache.org/jira/browse/DERBY-3634
> https://issues.apache.org/jira/browse/DERBY-4397
>
> Also are there still performance considerations with ROW_NUMBER?
> I see:
> https://issues.apache.org/jira/browse/DERBY-3505
>

Yes, I believe so. An alternative will often be to use ORDER BY + OFFSET
FETCH. This combo will not fetch all rows;
if sorting can skipped, it will be quite efficient.

> Are there still other concerns with ROW_NUMBER?
>
Not that I am aware of.

Thanks,
Dag


> Thanks
>
> Kathey