[
https://issues.apache.org/jira/browse/DERBY-3505?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12669532#action_12669532
]
Dag H. Wanvik commented on DERBY-3505:
--------------------------------------
In PredicateList#orderUsefulPredicates, restriction predicates are analyzed and
possibly converted
into start/stop keys for index scans (cf. calls to markStartKey/markStopkey).
I looked at an example where we have a table with two ints, i and j with a
primary key on i giving rise to an index. The query: 'select * from t where i <
5' converted the literal 5 into a stop key when scanning the index.
This technique allows predicates to effectively limit how many rows are read
when applicable.
It seems we need some similar analysis/logic for converting predicates on
ROW_NUMBER to
an efficient "LIMIT". This seems independent of whether the underlying window
is ordered or not;
the logic would be applied the language level, not at the store level.
> Current implementation of ROW_NUMBER() window function does not stop
> execution once criteria is met
> ---------------------------------------------------------------------------------------------------
>
> Key: DERBY-3505
> URL: https://issues.apache.org/jira/browse/DERBY-3505
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 10.4.1.3
> Reporter: Thomas Nielsen
>
> Using ROW_NUMBER() to limit the number of rows returned is typically done
> with the following query:
> SELECT * FROM (
> SELECT row_number() over () as r, t.* FROM T
> ) AS tmp WHERE r <= 3;
> The query plan shows that the restriction is perfomed in the outermost
> ProjectRestrictResultSet, and that it actually sees all rows in the table.
> ******* Project-Restrict ResultSet (1):
> Number of opens = 1
> Rows seen = 1280
> Rows filtered = 1277
> restriction = true
> In this case all 1280 rows are read from disk, and passed up the ResultSet
> chain. 1277 rows are filtered out so that, in the end, we only return 3 rows.
> Ideally the execution should stop after pulling only 3 rows through the chain.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
