Search code examples
javasqloraclehibernatecriteria

How to insert an "Optimizer hint" to Hibernate criteria api query

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.

Solution

  • 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).