[OPENJPA-759] Query pagination on DB2 - an alternative way

• Type: Improvement
• Status: Open
• Priority: Minor
• Resolution: Unresolved
• Affects Version/s: 2.0.0-M2
• Component/s: sql
• Environment: DB2 UDB
• Assignee: Unassigned
• Reporter: Milosz Tylenda
• Created: 31/Oct/08 18:02
• Updated: 27/Aug/15 06:56

Description

The attached patch provides an alternative way of doing paging in DB2.
I made it hoping that it would improve the performance of paging queries
but it didn't, at least not when the database and OpenJPA are co-located.
There is probably no point in committing this patch unless someone
proves it gives any advantages.
I am however presenting it here, maybe some users will find it useful
(it would be nice if someone could check timings with remote database).

Attached are:

• a patch which modifies DB2Dictionary and also adds a few tests to TestQueryPagination.
• a standalone modified DB2Dictionary class.
• a timer program which was supposed to prove the superior performance but failed

Some characteristics:

• The idea is to limit the result set returned by SQL query instead of skipping rows
  when traversing the result set in OpenJPA. A similar approach exists
  in OracleDictionary.
• If setMaxResulsts and setFirstResult were called on Query, the SQL query
  [QUERY] is modified as follows:

SELECT * FROM (
SELECT rr.*, ROW_NUMBER() OVER(ORDER BY ORDER OF rr) AS rn FROM (
[QUERY]
FETCH FIRST [m] ROWS ONLY
) AS rr
) AS r WHERE rn > [n] ORDER BY rn

• The modified SQL query adds one column to the end of column list in the
  result set. Luckily, I couldn't find any side effects of doing this.
• If only setMaxResults was called on Query, only FETCH FIRST [m] ROWS ONLY
  is appended to SQL query - this is how it works currently.
• The new way of paging will be used only if the database is a UDB 8.1 or later
  because of ORDER OF construct and FETCH FIRST [m] ROWS ONLY in a subselect.
  Maybe some other DB2 flavours could also handle it but I have no access.
• User can fall back to the old behaviour by setting supportsSelectStartIndex
  Dictionary property to false.

Issue Links

• is related to: OPENJPA-2356 Use new Offset/Limit/ROWNUM features in DB2 family of databases for range queries
• relates to: OPENJPA-378 DB2 SQL incorrectly generating "FETCH FIRST n ROWS ONLY" in subselects
• relates to: OPENJPA-447 Query pagination broken for non-DB2 databases
• relates to: OPENJPA-476 Query.setFirstResult and Query.setMaxResults don't take effect
• relates to: OPENJPA-632 setFirstResult and setMaxResults not working for Oracle.