How to embed Oracle hints in Hibernate query
With Oracle hints, you can alter the Oracle execution plans to affect the way how Oracle retrieve the data from database. Go here for more detail about Oracle optimizer hints.
In Hibernate, is this possible to embed the Oracle hint into the Hibernate query?
Hibernate setComment()?
Can you embed the Oracle hint into HQL with Hibernate custom comment “setComment()” function? Let’s see an example here
1. Original Hibernate Query
This is a simple select HQL to retrieve a Stock with a stock code.
String hql = "from Stock s where s.stockCode = :stockCode";
List result = session.createQuery(hql)
.setString("stockCode", "7277")
.list();
Output
Hibernate:
select
stock0_.STOCK_ID as STOCK1_0_,
stock0_.STOCK_CODE as STOCK2_0_,
stock0_.STOCK_NAME as STOCK3_0_
from mkyong.stock stock0_
where stock0_.STOCK_CODE=?
2. Try Hibernate setComment()
Enable the hibernate.use_sql_comments in Hibernate’s configuration file (hibernate.cfg.xml) in order to output the custom comment to your log file or console.
<!-- hibernate.cfg.xml -->
<?xml version="1.0" encoding="utf-8"?>
...
<hibernate-configuration>
<session-factory>
...
<property name="show_sql">true</property>
<property name="format_sql">true</property>
<property name="use_sql_comments">true</property>
<mapping class="com.mkyong.common.Stock" />
</session-factory>
</hibernate-configuration>
Using Hibernate setComment() to insert a custom comment to your query.
String hql = "from Stock s where s.stockCode = :stockCode";
List result = session.createQuery(hql)
.setString("stockCode", "7277")
.setComment("+ INDEX(stock idx_stock_code)")
.list();
Output
Hibernate:
/* + INDEX(stock idx_stock_code) */ select
stock0_.STOCK_ID as STOCK1_0_,
stock0_.STOCK_CODE as STOCK2_0_,
stock0_.STOCK_NAME as STOCK3_0_
from mkyong.stock stock0_
where stock0_.STOCK_CODE=?
3. Is this work?
It’s not, there are two problem with Hibernate custom comments.
1. The Oracle hint have to append after the ‘select’, not before.
Hibernate generated query
/* + INDEX(stock idx_stock_code) */ select
The correct way should be…
select /*+ INDEX(stock idx_stock_code) */
2. Hibernate will add an extra space in between “/* +” automatically.
In Hibernate, there are still no official way to embed the Oracle hints into Hibernate query langueges (HQL).
P.S Thanks Pete contribute on this.
Working solution
The only solution is using the Hibernate createSQLQuery method to execute the native SQL statement.
String hql = "/*+ INDEX(stock idx_stock_code) */
select * from stock s where s.stock_code = :stockCode";
List result = session.createQuery(hql)
.setString("stockCode", "7277")
.list();
output
Hibernate:
/*+ INDEX(stock idx_stock_code) */ select *
from stock s where s.stock_code = ?
The example within Working Solution is still wrong as it has hint before select statement.
You can use a Criteria ProjectionList with a sqlProjection to insert a hint into a hibernate query works well. See this post
http://stackoverflow.com/questions/1327503/how-to-insert-an-optimizer-hint-to-hibernate-criteria-api-query
Thanks for your idea, I learn new stuff today 🙂
Hibernate 5 query.addQueryHint(“Query Hint”) works fine, no issues.
it’s not work on hibernate 4.3
How to implement Index Hints in Hibernate Jpa Vendor Adapter (i.e. org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter)? I can’t find the “useSqlComments” property in HibernateJpaVendorAdapter.
I have been meaning to ask a database specific question myself and is analogous to the problem that is trying to be addressed here, i.e. providing a “hint” to the database to do something different and interesting in the name of performance.
I work in an environment where the database is SQL Server and all installations are optimized for batch processing rather than transaction processing. What this means for us is the in SQL we right we have to say NOLOCK after each table name. For example…
SELECT * FROM Employee WITH NOLOCK WHERE name = ‘Tom Sawyer’
If we forget to put NOLOCK after each table, we suffer from inadvertent spurious database locks. Again, this becomes necessary only because SQL Server has been installed for efficient batch processing.
So my question is, when using Hibernate with SQL Server, how do I tell it to put NOLOCK after each table in the SQL it generates behinds the scenes?
Thanks in advance.
Hi All,
Pls vote or support for this feature implemented in hibernate:
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2736
Regards
Senthil
I’ve tried this as well unfortunately it does not work. Oracle hints need to be appended after the SELECT statement. In your example you have:
/* + INDEX(stock idx_stock_code) */ select
stock0_.STOCK_ID as STOCK1_0_,
stock0_.STOCK_CODE as STOCK2_0_,
stock0_.STOCK_NAME as STOCK3_0_
from mkyong.stock stock0_
where stock0_.STOCK_CODE=?
but Oracle won’t pick up that hint. It needs to be:
select
/* + INDEX(stock idx_stock_code) */
stock0_.STOCK_ID as STOCK1_0_,
stock0_.STOCK_CODE as STOCK2_0_,
stock0_.STOCK_NAME as STOCK3_0_
from mkyong.stock stock0_
where stock0_.STOCK_CODE=?
Currently Hibernate doesn’t support hints and you need to do them natively (in your example for native query you have it wrong as well).
Thanks for contribute on this, really appreciated your comment.
Article updated accordingly.
And I made a mistake of course. The hint also can’t have a space after the /* which Hibernate puts out as well.
Hints need to be /*+ INDEX(stock idx_stock_code) */
No space.