[
https://issues.apache.org/jira/browse/TRAFODION-2793?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
liu ming reassigned TRAFODION-2793:
-----------------------------------
Assignee: liu ming
> pagination function as mysql
> ----------------------------
>
> Key: TRAFODION-2793
> URL: https://issues.apache.org/jira/browse/TRAFODION-2793
> Project: Apache Trafodion
> Issue Type: Improvement
> Components: sql-general
> Affects Versions: any
> Reporter: Yuan Liu
> Assignee: liu ming
> Priority: Major
> Fix For: any
>
>
> [Yuan]
> We have been asked several times about pagination function in Trafodion. As I
> know, the only way we can do about pagination is as below,
> select * from (select *,row_number() over() as rn from test) as t where t.rn
> between 1 and 10;
> But MYSQL and DB2 all have more graceful way,
> • MYSQL
> Select * from test limit 0,5;
> • DB2
> Select * from test offset 0 rows fetch next 5 rows;
> I am wondering that is it possible that we also suport such function? I am
> not sure how much work it may take, just a suggestion~
> [Hans]
> We don't have it right now, but it should be fairly easy to support the MySQL
> syntax.
> One thing that EsgynDB users will need to watch is that we have a parallel
> DMBS where the rows don't always get returned in the same order. So, it is
> important to add an ORDER BY to the query.
> It is not too hard to support this syntax, but to really do this well, we
> could add some more performance optimizations that would be harder to do:
> • Favor nested joins for this type of queries
> • Favor indexes that provide a natural order for this type of queries
> • Possibly even cache some results
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
