如何插入“优化器提示”以Hibernate标准API查询 [英] How to insert an "Optimizer hint" to Hibernate criteria api query

问题描述

我有一个使用标准api动态组合的hibernate查询。
它会产生难以忍受的缓慢的查询，如果按原样执行的话。

但是我注意到，如果我预先加入/ * + FIRST_ROWS，它们会快1000％ （10）* /查询。我怎样才能做到这一点与标准api？

我试了criteria.setComment（..），但这似乎被忽略。

在hibernate文档中，3.4.1.7。查询提示被提及，但它明确指出：请注意，这些不是SQL查询提示

查询的结果将被分页，因此在99％的我会显示结果1-10。

解决方案

您可以在会话级别修改优化程序模式：

  ALTER SESSION SET optimizer_mode = FIRST_ROWS; 
  

或者在查询之前，然后将其恢复为其默认值（ ALL_ROWS ）或者你的情况，因为99％的查询会从中受益，你可以在模式级别修改它（使用 ON LOGON 触发器例如）甚至在实例级别（修改init参数）。

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.

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?

i tried criteria.setComment(..), but this seems to be ignored.

in the hibernate docs, 3.4.1.7. Query hints are mentioned, but it clearly states: "Note that these are not SQL query hints"

the result of the query will be paginated, so in 99% of the cases i will display the results 1-10.

解决方案

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 LOGON trigger for exemple) or even at the instance level (modify the init parameter).

这篇关于如何插入“优化器提示”以Hibernate标准API查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！