Including Oracle hints in JPA/Hibernate

[ConlinC]

Has anyone found a good way to include Oracle optimizer hints in HQL queries? I'd like to include the /*+ FIRST_ROWS */ hint in a query, but it looks like Hibernate doesn't support this.

The other options I see from my google searches are:
- rewrite the query to be a native sql query, or
- just before the query, do an
ALTER SESSION SET optimizer_mode = FIRST_ROWS;
and then do another ALTER SESSION after the query, setting the optimizer mode back to ALL_ROWS.

Just wondering if anyone has experience with this and could offer a recommendation.

[CampbellBD]

I don't know of any way to apply an oracle hint other than what you've already listed (native query and alter session). In the past we used Oracle hints frequently. The Oracle optimizer is very good these days and I don't find myself needing hints very often. You might want to take a look at the query; did hibernate generate a reasonable query? If not maybe the JPA mappings need to change. If the query is reasonable but performance stinks, a database person might be able to help by checking for missing foreign keys, indexing opportunities, i.e. standard tuning stuff. Sometimes tuning solutions include changing the query but other times it includes changing the database so it's worth getting database eyes on it.

http://docs.jboss.org/hibernate/stable/entitymanager/reference/en/html/objectstate.html

[tadesmith]

I've actually modified Hibernate to enable this. I thought I'd share it so I created a JIRA Patch on the Hibernate site with the uploaded source code. You can find it here:
http://opensource.atlassian.com/projects/hibernate/browse/HHH-6123

I hope others find it useful.