db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Thomas Nielsen (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-2998) Add support for ROW_NUMBER() window function
Date Wed, 28 Nov 2007 09:54:43 GMT

    [ https://issues.apache.org/jira/browse/DERBY-2998?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12546185

Thomas Nielsen commented on DERBY-2998:

I started comparing
   select * from (select row_number() as r, t.* from t) as tr where r < 4;
   select * from (select a*b as r, t.* from t) as tr where r < 4;

i.e change row_number() for a*b as the expression in the inner select.

In both these cases the top of the QueryTree looks like


where the SelectNode fromList point to the below ProjectRestrictNode (PRN), and the PRNs childResult
point to the second SelectNode and so on.

Even in the a*b scenario, the wherePredicate in the top SelectNode is not pushed below the
first PRN, since it is referencing an expression. This means as of now the inner 'select a*b
as r ...' results in a full table scan, returning all rows to the outer select, and the restriction
is checked at the outer select which in the end return only a few rows.

It would seem like a reasonable/good/necessary optimization to push the where predicate down
into the second select?
I can't remember seeing any jiras relating to this off the top of my head. In the general
case one could possibly argue that it does not really matter where it is restricted as long
as it's done in the engine, but in the row_number() case and possibly with indexes and sorted
inner results, you will take a (potentially severe) performance hit?

> Add support for ROW_NUMBER() window function
> --------------------------------------------
>                 Key: DERBY-2998
>                 URL: https://issues.apache.org/jira/browse/DERBY-2998
>             Project: Derby
>          Issue Type: Sub-task
>          Components: SQL
>            Reporter: Thomas Nielsen
>            Assignee: Thomas Nielsen
>            Priority: Minor
>         Attachments: d2998-4.diff, d2998-4.stat, d2998-5.diff, d2998-5.stat
> As part of implementing the overall OLAP Operations features of SQL (DERBY-581), implement
the ROW_NUMBER() window function.
> More information about this feature is available at http://wiki.apache.org/db-derby/OLAPRowNumber

This message is automatically generated by JIRA.
You can reply to this email to add a comment to the issue online.

View raw message