[TRAFODION-2793] pagination function as mysql

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: any
• Fix Version/s: any
• Component/s: sql-general
• Assignee: liu ming
• Reporter: Yuan Liu
• Created: 03/Nov/17 01:41
• Updated: 21/Jun/18 13:01

Description

[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