0

JPA, and in my particular case eclipselink, generates /*+ FIRST_ROWS */ in case of using query.setFirstResult()/query.setMaxResults():

SELECT * FROM (
  SELECT /*+ FIRST_ROWS */ a.*, ROWNUM rnum  FROM (
    SELECT * FROM TABLES INCLUDING JOINS, ORDERING, etc.) a
  WHERE ROWNUM <= 10 )
WHERE rnum > 0;

That forces Oracle to use nested loops instead of hash-joins. In general is has seance, but in my particular case it dramatically decrease performance.

Is it possible to disable hint usage/generation for a particular query?

HAYMbl4
  • 1,450
  • 2
  • 15
  • 29
sanpwc
  • 43
  • 1
  • 10
  • why u need to use setFirstResult? – Thomas Apr 25 '16 at 13:05
  • explain plan without /* + FIRST_ROWS*/ really better? – HAYMbl4 Apr 25 '16 at 13:05
  • 1
    nested loop is not a really bad thing, and HASH join is not a really good choice either when u got only a few thousands row – Thomas Apr 25 '16 at 13:08
  • AFAIK Eclipselink has something like setHint method. If you use Hibernate, you should contact Hibernate support. http://www.eclipse.org/eclipselink/documentation/2.4/jpa/extensions/q_sql_hint.htm – ibre5041 Apr 25 '16 at 13:22
  • @Thomas setFirstResult is used for pagination. I agree that in general nested loop might be better then hash join. I believe that's the point, why oracle choose it for my request. – sanpwc Apr 25 '16 at 13:42
  • @HAYMbl4 in my case it's 100 times better. – sanpwc Apr 25 '16 at 13:42
  • @ibre5041 Yes, there's such method, however I need to disable (remove) automatically generated hint. – sanpwc Apr 25 '16 at 13:42
  • What happen then you call this method? Is `first_rows` somehow merged into it? According to this: http://www.celinio.net/techblog/?p=1767 (referring Oracle bug 374136, first_rows should no more be needed for 11g and later). So maybe you should report a bug in Hibernate. (first_rows should no more be used for pagination, generally it still makes sense- of course) – ibre5041 Apr 25 '16 at 13:48
  • Anyway `first_rows` hint is deprecated and should not be used at all. It was replaced by `FIRST_ROWS_n` set of hints. http://stanford.edu/dept/itss/docs/oracle/10g/server.101/b10752/optimops.htm: Note: Using heuristics sometimes leads the query optimizer to generate a plan with a cost that is significantly larger than the cost of a plan without applying the heuristic. FIRST_ROWS is available for backward compatibility and plan stability; use FIRST_ROWS_n instead. – ibre5041 Apr 25 '16 at 14:05
  • @san Could you please paste in both plan: with first_rows and without first_rows hint – Thomas Apr 26 '16 at 08:05

1 Answers1

1

As @ibre5041 told, FIRST_ROWS hint is deprecated, in context of Oracle, FIRST_ROWS(N) should be used instead of it. In my case neither FIRST_ROW nor FIRST_ROW(N) is actually needed, so in order to tell eclipselink not to use outdated stuff, it's possible to specify oracle version within persistence.xml:

<property name="eclipselink.target-database" value="org.eclipse.persistence.platform.database.oracle.Oracle11Platform" />

After adding this, I got strange error: Could not initialize class org.eclipse.persistence.platform.database.oracle.Oracle11Platform However, after I put ojdbcN.jar to domain/lib/ext the error has gone.

As a result, eclipselink generates query without FIRST_ROW hint, and Oracle uses better plan.

sanpwc
  • 43
  • 1
  • 10