Integration

Hi,

We're using JEE5/JPA/Eclipselink 1.0.2 with Oracle DB 11g. We are using pagination features in eclipselink on many of our JPA entities. We have code like:

Query query = em.createQuery("SELECT o FROM myTable e ORDER BY o.rowId ASC, o.someotherField ASC");
query.setFirstResult(0);
query.setMaxResults(20);
List<MyTable> mtbl = query.getResultList();

The sql generated by eclipselink looks like this:
SELECT * FROM (SELECT /*+ FIRST_ROWS */ a.*, ROWNUM rnum FROM (
SELECT t0.ROW_ID ...
FROM MYTABLE t0
ORDER BY t0.ROW_ID ASC, t0.SOMEOTHERFIELD ASC) a WHERE ROWNUM <= ?) WHERE rnum > ?

bind => [0, 20]

The problem is that with DB 11g, we sometimes got slow performance for some queries because of the SQL hint ---- /*+ FIRST_ROWS */ ---- being inserted by eclipselink. We experimented and found that if we specified a number in the hint (such as FIRST_ROWS(10) or FIRST_ROW(20) or FIRST_ROWS(100)...), the query performed reasonably well. For the slow queries, they typically have ORDER BY clause in the SELECT statement, and when the order by clause is removed, the query performed reasonably well even with just FIRST_ROWS only (no size needed). But this is not an ideal solution for our scenarios. We are reluctant to use native SQL as a workaround.

So is there a way to explicitly set the SQL hint through JPA Query API (using setHints(), I guess) so that FIRST_ROWS(n) could be inserted into the SQL like the following?
SELECT * FROM (SELECT /*+ FIRST_ROWS(10) */ a.*, ROWNUM rnum FROM (...)

If setHints() method is the right one to use, can someone show an example of setting both the input parameters required by the method?

Thx for any pointer.