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] Created: (DERBY-3505) Current implementation of ROW_NUMBER() window function does not stop execution once criteria is met
Date Thu, 06 Mar 2008 17:54:58 GMT
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.0.0
            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.


Mime
View raw message