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 = ?

More Native SQL queries examples.

About the Author

author image
mkyong
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter. If you like my tutorials, consider make a donation to these charities.

Comments

avatar
8 Comment threads
3 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
8 Comment authors
mohammadMydiscuzzzRandy PArbyAkhtar Recent comment authors
newest oldest most voted
Akhtar
Guest
Akhtar

The example within Working Solution is still wrong as it has hint before select statement.

Randy P
Guest
Randy P

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

mohammad
Guest
mohammad

it’s not work on hibernate 4.3

Mydiscuzzz
Guest
Mydiscuzzz

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.

Arby
Guest
Arby

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… Read more »

Senthil
Guest
Senthil

Hi All,

Pls vote or support for this feature implemented in hibernate:
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2736

Regards
Senthil

trackback
Hibernate Tutorials | Tutorials

[…] How to embed Oracle hints in Hibernate query A trick to embed the Oracle hints into Hibernate Query to increase the Oracle query performance. […]

Pete
Guest
Pete

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

Pete
Guest
Pete

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.