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

Question

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.

Accepted Answer

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

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

Solution

Problem