Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Pagination - FIRST_ROWS_N
Pagination - FIRST_ROWS_N [message #984557] Wed, 14 November 2012 15:09 Go to next message
Mauro Flores is currently offline Mauro Flores
Messages: 83
Registered: September 2009
Location: Brasil
Member
How to create a JPA query with pagination parameters (setFirstRows and setMaxRows) and obtain a SQL query like this to run on oracle data base:


SELECT * FROM (SELECT /*+ FIRST_ROWS_10 / a., ROWNUM rnum FROM (...)

Today the SQL query is generated like:

SELECT * FROM (SELECT /*+ FIRST_ROWS / a., ROWNUM rnum FROM (...)

The second way is far slower than the first.

Is there a query hint to make this customization? I'm using eclipselink 2.1.3.

Thanks. Mauro Flores.


[Updated on: Wed, 14 November 2012 15:10]

Report message to a moderator

Re: Pagination - FIRST_ROWS_N [message #984628 is a reply to message #984557] Wed, 14 November 2012 16:24 Go to previous message
Chris Delahunt is currently offline Chris Delahunt
Messages: 995
Registered: July 2009
Senior Member
Depending on the database version you are on, not including the FIRST_ROWS at all might be more efficient as described here:
https://bugs.eclipse.org/bugs/show_bug.cgi?id=374136

If you can use EclipseLink 2.4 or later, with an Oracle10Platform or higher the hint will not be included. If this isn't what you need, please file an enhancement request to have the hint string used in pagination be configurable.

In the mean time, the bug fix mentioned above changed the hint string to a protected class instance, so in EclipseLink 2.4 you would need a custom platform subclass that sets the HINT set in the constructor to something else. In prior versions the HINT string is static and final, and so a platform subclass would need to override the printSQLSelectStatement method defined in the OraclePlatform class to print something different.

Best Regards,
Chris
Previous Topic:How to ise dynamic fetchgroup
Next Topic:Postgres inet type
Goto Forum:
  


Current Time: Sat Apr 19 18:30:31 EDT 2014

Powered by FUDForum. Page generated in 0.03896 seconds