Eclipse Community Forums: EclipseLink » Pagination

Help

Home

Home » Eclipse Projects » EclipseLink » Pagination - FIRST_ROWS_N

Show: Today's Messages :: Show Polls :: Message Navigator

Pagination - FIRST_ROWS_N [message #984557]

Wed, 14 November 2012 20:09

Mauro Flores

Messages: 84
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 20:10]

Report message to a moderator

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

Wed, 14 November 2012 21:24

Chris Delahunt

Messages: 1389
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

Report message to a moderator

Previous Topic:	How to ise dynamic fetchgroup
Next Topic:	Postgres inet type

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 21:29:33 GMT 2024

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter