Including Oracle hints in JPA/Hibernate

Support and Announcements for the LDS Tech Java Web Application Platform (setup, configuration, bugs, feedback).
Church Employee
Church Employee
Posts: 12
Joined: Tue Feb 20, 2007 4:19 pm

Including Oracle hints in JPA/Hibernate

Postby ConlinC » Tue Feb 01, 2011 3:03 pm

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

New Member
Posts: 18
Joined: Wed Jan 05, 2011 10:28 am
Location: Kaysville, Utah, USA

Postby campbellbd » Wed Feb 02, 2011 12:30 pm

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.

New Member
Posts: 1
Joined: Thu Apr 14, 2011 4:44 pm

Postby tadesmith » Thu Apr 14, 2011 4:47 pm

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:

I hope others find it useful.

Return to “Java Web Project Support (Stack)”

Who is online

Users browsing this forum: No registered users and 1 guest