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");
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.
Unfortunately, prepared statements do not seem to allow binding parameters in the hint string. The hint string used doesn't have a value intead of a FIRST_ROWS(n) type hint because the statements need to add the parameter to the SQL string, preventing statement caching and reuse. If you want to use a value, it will mean that it needs to be a hard coded constant, or that you will have a much harder time reusing prepared statements. Also hints added using the setHints() api will go within the inner select, and so might not help the performance of the pagination query.
EclipseLink's pagination use can be turned off/on using the DatabasePlatform's setShouldUseRownumFiltering(boolean) method. This might not be helpful though, as it means that EclipseLink will then not use pagination at all, and instead use JDBC to set the max rows, and the resultste to skip to the first result.
The actual string used and code for the feature exists in the OraclePlatform class, within the printSQLSelectStatement method. The best option might be to extend the OraclePlatform and override this method on a custom platform, so that you can print your own values and hints in the pagination query.
The other option is to not use setFirstResult and setMaxResults, and instead intercept the EclipseLink generated SQL and wrap it in your own pagination SQL (much like the printSQLSelectStatement method does). Ie
String sqlString = query.getSQLString();
query.setString( "SELECT * FROM (SELECT /*+ FIRST_ROWS(n) / a.*, ROWNUM rnum FROM ("+sqlString + ") a WHERE ROWNUM <="+maxvalue+" ) WHERE rnum > "+firstValue);