Eclipse Community Forums: EclipseLink » Pagination

Home » Eclipse Projects » EclipseLink » Pagination - FIRST_ROWS_N

Pagination - FIRST_ROWS_N [message #984557]

Wed, 14 November 2012 15:09

Eclipse User

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] by Moderator

Re: Pagination - FIRST_ROWS_N [message #984628 is a reply to message #984557]

Wed, 14 November 2012 16:24

Eclipse User

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:

-=] Back to Top [=-

Current Time: Tue Jul 22 19:57:19 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter