oracle 如何在 Hibernate 条件 api 查询中插入“优化器提示”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1327503/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to insert an "Optimizer hint" to Hibernate criteria api query
提问by Andreas Petersson
i have a hibernate query that is dynamically put together using the criteria api. it generates queries that are unbearably slow, if executed as-is.
我有一个使用标准 api 动态组合在一起的休眠查询。如果按原样执行，它会生成慢得无法忍受的查询。
but i have noted they are about 1000% faster if I prepend /*+ FIRST_ROWS(10) */ to the query. how can i do this with the criteria api?
但我已经注意到，如果我在查询前加上 /*+ FIRST_ROWS(10) */，它们会快 1000%。我怎么能用标准 api 做到这一点？
i tried criteria.setComment(..), but this seems to be ignored.
我试过criteria.setComment(..)，但这似乎被忽略了。
in the hibernate docs, 3.4.1.7. Query hints are mentioned, but it clearly states: "Note that these are not SQL query hints"
在休眠文档中，3.4.1.7。提到了查询提示，但它明确指出：“请注意，这些不是 SQL 查询提示”
the result of the query will be paginated, so in 99% of the cases i will display the results 1-10.
查询的结果将被分页，因此在 99% 的情况下，我将显示结果 1-10。
采纳答案by Vincent Malgrat
You could modify the optimizer mode at the session level:
您可以在会话级别修改优化器模式：
ALTER SESSION SET optimizer_mode = FIRST_ROWS;
Either just before your query and then putting it back to its default value (ALL_ROWS) or in your case since 99% of the queries would benefit from it you could modify it at the schema level (with an ON LOGONtrigger for exemple) or even at the instance level (modify the init parameter).
要么在您的查询之前，然后将其恢复为默认值 ( ALL_ROWS) 要么在您的情况下，因为 99% 的查询将从中受益，您可以在架构级别（例如使用ON LOGON触发器）甚至在实例中修改它级别（修改 init 参数）。
回答by Randy P
I was able to put in a Oracle hint by a adding a ProjectionList to the criteria.
我能够通过向标准添加 ProjectionList 来放入 Oracle 提示。
ProjectionList proList = Projections.projectionList();
proList.add(Projections.sqlProjection("/*+INDEX_DESC(this_ MY_INDEX_NAME)*/ 1 as MYHINT",
new String[]{},
new Type[]{}));
//add properties from your class
proList.add(Projections.property("field1"));
proList.add(Projections.property("field2"));
proList.add(Projections.property("field3"));
c.setProjection(proList);
c.list()returns List<Object[]>in order of ProjectionList
c.list()List<Object[]>按 ProjectionList 的顺序返回
回答by Thies
I have another generic solution, that should work for every Criteria query :
use a standard comment and an Hibernate Interceptor changing the ultimate SQL to the database.
(I used it with Hibernate 3.3, but should be useable for every version, registering of the Interceptor may be different.)
我有另一个通用解决方案，它应该适用于每个 Criteria 查询：
使用标准注释和 Hibernate Interceptor 将最终 SQL 更改为数据库。
（我在 Hibernate 3.3 中使用它，但应该适用于每个版本，拦截器的注册可能不同。）
In your query code use:
在您的查询代码中使用：
criteria.setComment("$HINT$ push_pred(viewAlias)");
Write an Interceptor to change to SQL text (this one uses commons.lang3.StringUtils):
编写一个拦截器来改变 SQL 文本（这个使用的是 commons.lang3.StringUtils）：
public class HibernateEntityInterceptor extends EmptyInterceptor {
@Override
public String onPrepareStatement(String sql) {
if (sql.startsWith("/* $HINT$")) {
String hintText = StringUtils.substringBetween(sql, "/* $HINT$", "*/");
sql = sql.replaceFirst("select ", "select /*+" + hintText + "*/ ");
}
return sql;
}
Above is for Oracle, but should be easily adjustable for every DBMS.
Maybe you can/should create a constant for the hint marker "$HINT$".
Logging should be done, too (so you can easily see the correct calling of the Interceptor), I left it out above for simplicity.
以上是针对 Oracle 的，但对于每个 DBMS 应该很容易调整。
也许您可以/应该为提示标记“$HINT$”创建一个常量。
日志也应该完成（这样你就可以很容易地看到拦截器的正确调用），为了简单起见，我在上面省略了它。
The Interceptor must be registered. In Spring this is done in applicationContext.xml:
拦截器必须注册。在 Spring 中，这是通过以下方式完成的applicationContext.xml：
<bean id="entityListener" class="your.package.HibernateEntityInterceptor"/>
<bean id="sessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
<property name="entityInterceptor" ref="entityListener"/>
[...]
Or (copy from the Hibernate 3.3 docs):
或者（从 Hibernate 3.3 文档中复制）：
A Session-scoped interceptor is specified when a session is opened using one of the overloaded SessionFactory.openSession() methods accepting an Interceptor.
Session session = sf.openSession( new HibernateEntityInterceptor() );A SessionFactory-scoped interceptor is registered with the Configuration object prior to building the SessionFactory. Unless a session is opened explicitly specifying the interceptor to use, the supplied interceptor will be applied to all sessions opened from that SessionFactory. SessionFactory-scoped interceptors must be thread safe. Ensure that you do not store session-specific states, since multiple sessions will use this interceptor potentially concurrently.
new Configuration().setInterceptor( new HibernateEntityInterceptor() );
当使用接受拦截器的重载 SessionFactory.openSession() 方法之一打开会话时，指定会话范围的拦截器。
Session session = sf.openSession( new HibernateEntityInterceptor() );在构建 SessionFactory 之前，SessionFactory 范围的拦截器已注册到 Configuration 对象。除非明确指定要使用的拦截器打开会话，否则提供的拦截器将应用于从该 SessionFactory 打开的所有会话。SessionFactory 范围的拦截器必须是线程安全的。确保您不存储特定于会话的状态，因为多个会话可能会同时使用此拦截器。
new Configuration().setInterceptor( new HibernateEntityInterceptor() );
回答by APC
The problem is that the hint syntax is not a comment, it just looks a bit like one. It really has to go between the SELECTand the selected columns, whereas setComment()prepends the comment before the SELECT.
问题是提示语法不是注释，它只是看起来有点像。它确实必须在SELECT和所选列之间进行，而setComment()在SELECT.
Beyond that, there are no silver bullets. FIRST_ROWSis not a performance enhancing tool. It may end up taking longer to get allthe rows back. Of course, in a user-facing program retrieving the first ten rows may be all we need to do.
除此之外，没有银弹。 FIRST_ROWS不是性能增强工具。取回所有行最终可能需要更长的时间。当然，在面向用户的程序中，我们可能只需要检索前十行。
But, which ever way you bounce it, if you want to use Oracle's hint syntax you'll need to go down the Native SQL route.
但是，无论您以哪种方式反弹，如果您想使用 Oracle 的提示语法，您都需要沿着本机 SQL 路线走下去。
What else can you do? I don't (yet) have much experience tuning Hibernate. The one time I have under such a task the query was grabbing rows from a whole bunch of tables to instantiate an object with lots of sub-types. Each sub-type was a separate table. The query generated by Hibernate had many OUTER JOINs, which confused the heck out the optimizer. Breaking that monster into several focused queries (one per sub-type) which used only INNER JOINs produced a two hundredfold reduction in retrieval time.
你还能做什么？我（还）没有太多调整 Hibernate 的经验。有一次我在执行这样的任务时，查询是从一大堆表中抓取行来实例化一个具有许多子类型的对象。每个子类型是一个单独的表。Hibernate 生成的查询有很多 OUTER JOIN，这让优化器很困惑。将这个怪物分解成几个只使用 INNER JOIN 的重点查询（每个子类型一个），检索时间减少了 200 倍。
This may not be of any immediate use to you. But the principle is, look at the Hibernate query and see whether it can be implemented in a different, more efficient way.
这可能对您没有任何直接用处。但原理是，查看 Hibernate 查询，看看它是否可以以不同的、更有效的方式实现。
