Uploaded image for project: 'Apache Trafodion'
  1. Apache Trafodion
  2. TRAFODION-2793

pagination function as mysql

Attach filesAttach ScreenshotAdd voteVotersWatch issueWatchersCreate sub-taskLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments


    • Type: Improvement
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: any
    • Fix Version/s: any
    • Component/s: sql-general
    • Labels:


      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~

      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




            • Assignee:
              ovis_poly liu ming
              Yuan_Liu Yuan Liu


              • Created:

                Issue deployment