是否可以针对每个特定查询禁用jpa提示? [英] Is it possible to disable jpa hints per particular query?

问题描述

在使用query.setFirstResult()/query.setMaxResults()的情况下，JPA(在我的特殊情况下为eclipselink)会生成/*+ FIRST_ROWS */:

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;

这迫使Oracle使用嵌套循环而不是哈希联接.一般来说，它具有吸引力，但在我的特定情况下，它会大大降低性能.

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?

推荐答案

正如@ ibre5041所述，不建议使用FIRST_ROWS提示，在Oracle的上下文中，应使用FIRST_ROWS(N)代替它.在我的情况下，实际上既不需要FIRST_ROW也不需要FIRST_ROW(N)，因此为了告诉eclipselink不要使用过时的东西，可以在persistence.xml中指定oracle版本:

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" />

添加此内容后，出现奇怪的错误: 无法初始化org.eclipse.persistence.platform.database.oracle.Oracle11Platform类 但是，将ojdbcN.jar放入domain/lib/ext后，错误消失了.

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.

结果，eclipselink生成了没有FIRST_ROW提示的查询，并且Oracle使用了更好的计划.

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

这篇关于是否可以针对每个特定查询禁用jpa提示?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！