db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-4079) Add support for SQL:2008 <result offset clause> and <fetch first clause> to limit result set cardinality
Date Tue, 03 Mar 2009 13:28:56 GMT

    [ https://issues.apache.org/jira/browse/DERBY-4079?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12678313#action_12678313
] 

Knut Anders Hatlen commented on DERBY-4079:
-------------------------------------------

I don't have a copy of SQL 2008 yet, so I can't comment on the syntax, but the docs look good
to me. I did however struggle with understanding this sentence:

> If the underlying result set is not materialized (depends on query), the use of this
clause can give efficiency benefits.

What I don't quite understand, is the implication that we won't see efficiency benefits if
the underlying result set is materialized, but I'm probably just misunderstanding what you
are comparing (or perhaps misunderstanding how you're planning to implement it).

If you are comparing the time it takes to execute SELECT * FROM LARGE_TABLE and SELECT * FROM
LARGE_TABLE FETCH FIRST 10 ROWS ONLY and fetch the full result, I understand that it would
be faster with the FETCH clause than without if the underlying result set is not materialized,
because you just stop after fetching the first 10 rows whereas the other query goes through
the entire table. If the underlying result set is materialized, I understand that it's probably
not going to speed it up significantly in embedded mode, since the same number of rows must
be processed in both queries. In a client/server environment it reduces the number of rows
to send over the wire, so there we should see an efficiency benefit even if the result set
is materialized, I believe.

If you are comparing the time it takes to read out the columns you're interested in (just
quit calling rs.next() once you've fetched N rows), I don't see that it should make much difference
at all whether or not OFFSET/FETCH is used (assuming that the implementation will basically
do the same thing, just on a slightly lower level). Again, in a client/server environment
you may see higher performance with OFFSET/FETCH because the skipped rows don't need to go
over the wire, and because you limit the number of rows that will be pre-fetched. But I don't
see how this benefit will be affected by whether or not the underlying result set is materialized.

Perhaps it suffices to say "The use of this clause can give efficiency benefits for some queries"
and not qualify it further?

> Add support for SQL:2008 <result offset clause> and <fetch first clause>
to limit result set cardinality
> --------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-4079
>                 URL: https://issues.apache.org/jira/browse/DERBY-4079
>             Project: Derby
>          Issue Type: New Feature
>          Components: SQL
>            Reporter: Dag H. Wanvik
>            Assignee: Dag H. Wanvik
>         Attachments: ref.zip
>
>
> SQL 2008 has added new syntax to support a direct way to limit the
> returned set of rows in a result set.  This allows an application to
> retrieve only some rows of an otherwise larger result set, similar to
> the popular LIMIT clauses use in some databases.
> Up till now, in Derby (and SQL) we have had to use the ROW_NUMBER()
> function in a nested subquery to achieve the effect of the <fetch
> first clause>, cf. DERBY-2998, a method which is rather more indirect
> and still not efficient (DERBY-3505), and primarily intended for OLAP
> functionality, perhaps.
> There has been no direct way to achieve the effect of the <result
> offset clause> via SQL.
> Syntax (cf. SQL 2008, section 7.13):
>        <result offset clause> ::= OFFSET <n> {ROW | ROWS}
>        <fetch first clause> ::=      FETCH {FIRST | NEXT} [<n>] {ROW | ROWS}
ONLY
> where <n> is an integer. The two clauses syntactically follow the ORDER BY
> clause in the grammar.
> Note that both ORDER BY and the new clauses above are allowed also in
> subqueries in the new version of the SQL standard (section 7.13). I
> only propose to include this at the top level in DERBY for now. (ORDER
> BY is presently also not allowed in subqueries in Derby since SQL
> didn't allow for this until SQL 2008 either).

-- 
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