Main Tutorials

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 Author

author image
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

Subscribe
Notify of
11 Comments
Most Voted
Newest Oldest
Inline Feedbacks
View all comments
Akhtar
13 years ago

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

Randy P
12 years ago

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

NoNameGuy
2 years ago

Hibernate 5 query.addQueryHint(“Query Hint”) works fine, no issues.

mohammad
6 years ago

it’s not work on hibernate 4.3

Mydiscuzzz
8 years ago

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
12 years ago

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.

Senthil
13 years ago

Hi All,

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

Regards
Senthil

Pete
14 years ago

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
14 years ago
Reply to  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.