Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <2146597426.1204826098472.JavaMail.jira@brutus>
Date: Thu, 6 Mar 2008 09:54:58 -0800 (PST)
From: "Thomas Nielsen (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Created: (DERBY-3505) Current implementation of ROW_NUMBER()
 window function does not stop execution once criteria is met
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

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.