Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Database pagination on mySql and Oracle

DZone's Guide to

Database pagination on mySql and Oracle

· Performance Zone
Free Resource

Transform incident management with machine learning and analytics to help you maintain optimal performance and availability while keeping pace with the growing demands of digital business with this eBook, brought to you in partnership with BMC.

Having studiously avoided Oracle for over 20 years, I'm now working in a shop that uses it almost exclusively. Aside from the general overall expense of the product I'm routinely amazed at how many features other DBMS's I've used (DB2, MSSQL, MySQL, PostGres) are either missing or syntactically difficult to understand.

The most recent example is server side pagination… or more specifically, having the DBMS limit the results returned to for specific subsets of rows. In oracle to do this, one must run a query something like this:

select * from (select name, rownum rn from 
        (select name
          from users order by name)
      where rownum <= 10) where rn > 5;

I realize that this is a legacy syntax, but I personally find the new way just as obtuse. The new way (I guess) is supposed to be:

select * from (select name,
        row_number() over
        (order by name) rn
  FROM users) where rn between 5 and 10 order by rn

Compare this with the syntax for mySql (also now I guess technically part of the Oracle corporation):

select * from users order by name limit 5,5;

I find mySql's syntax to be more concise and don't really understand why Oracle's syntax is so convoluted other than perhaps some dogmatic insistence on following some sort of standard or an internal engineering group who was all hopped up on set theory drugs of some sort ;)



Evolve your approach to Application Performance Monitoring by adopting five best practices that are outlined and explored in this e-book, brought to you in partnership with BMC.

Topics:

Published at DZone with permission of Michael Mainguy, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}