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

pagination function as mysql

    XMLWordPrintableJSON

    Details

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

      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

        Attachments

          Activity

            People

            • Assignee:
              ovis_poly liu ming
              Reporter:
              Yuan_Liu Yuan Liu
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated: