Yuan Liu created TRAFODION-2793:
-----------------------------------
Summary: 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
Priority: Normal
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
(v6.4.14#64029)
