Porting LIMIT and OFFSET SELECT clauses to DB2

Opensource databases such as MySQL and PostgreSQL supports non standard SELECT extension for limiting number of returned rows from query. You can see description in PostgreSQL manual. Look for keywords LIMIT and OFFSET. LIMIT specifies the maximum number of rows to return, while OFFSET specifies the number of rows to skip before starting to return rows. When both are specified, OFFSET rows are skipped before starting to count the LIMIT rows to be returned.

Returning TOP N rows (emulating LIMIT) from query is easy. This functionality is in every database but syntax is database specific. DB2 follows SQL2008 standard and syntax for TOP N rows in DB2 is SELECT * FROM T FETCH FIRST 10 ROWS ONLY.

Emulating nonzero OFFSET feature is more difficult task. DB2 and most of other databases with exception Apache Derby and PostgreSQL 8.4 do not supports SQL2008 syntax SELECT * FROM T OFFSET 10 ROWS. In DB2 you can emulate this functionality by using OLAP Window function: SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY key ASC) AS rownum, * FROM T) AS foo WHERE rownum > skip AND rownum <= (n+skip) or scrollable cursor. Both solutions are not optimal because using scrollable cursor is slow and using OLAP function requires lot of SQL coding.

Luckily for us we got another and elegant solution to this problem. IBM started to hunt for Oracle customers and implement Oracle compatible features for making application porting easier. In DB2 9.5 it added support for Oracle-like ROWNUM pseudo column and in DB2 9.7 ROWNUM processing was optimized. ROWNUM magic column must be first enabled in DB2 configuration and instance restarted. After that you can make queries like SELECT * FROM T WHERE ROWNUM BETWEEN 10 AND 20 and get desired result rows.

Life is not that easy. As i said before IBM aimed at Oracle compatibility. In Oracle you can use ROWNUM only with < or <= operator. Unlike DB2 9.5 Oracle return no rows if > or BETWEEN is used on ROWNUM (read this technote for details). Because ROWNUM > X is never used in Oracle applications, IBM does not bothered with optimizing that use case in DB2. In DB2 9.7 they just optimized Oracle use case ROWNUM < X.

To get decent query performance, you must help DB2 with manual query rewriting:
select * from (select * from T where rownum <= 1000) where rownum > 800.

This will get you best performance from all OFFSET emulating methods in DB2 known to me. It would be really helpful from IBM to teach their SQL query rewrite engine about that in some DB2 9.7 fixpack - its pretty minor change, so we can save typing by using ROWNUM between X and Y. For reference how to do this in other databases see RDBMS comparsion page

Views: 9056


You need to be a member of ChannelDB2 to add comments!

Join ChannelDB2

Comment by Leons Petrazickis on June 24, 2010 at 9:48am
Starting with DB2 9.7.2, LIMIT and OFFSET are supported in DB2:
Comment by Leons Petrazickis on February 28, 2010 at 5:45pm
Thank you for a terrific overview. I did not know about the new ability to use ROWNUM in the WHERE clause.

Featured Downloads

Try BLU Acceleration on Cloud

© 2020   Created by channeldb2.   Powered by

Badges  |  Report an Issue  |  Terms of Service