[
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;
to
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
SELECT
|
PRN
|
SELECT
|
PRN
...
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.
