How to insert an "Optimizer hint" to Hibernate criteria api query

Question

i have a hibernate query that is dynamically put together using the criteria api. it generates queries that are unbearably slow, if executed as-is.

but i have noted they are about 1000% faster if I prepend /*+ FIRST_ROWS(10) */ to the query. how can i do this with the criteria api?

i tried criteria.setComment(..), but this seems to be ignored.

in the hibernate docs, 3.4.1.7. Query hints are mentioned, but it clearly states: "Note that these are not SQL query hints"

the result of the query will be paginated, so in 99% of the cases i will display the results 1-10.

Answer 1

I have another generic solution, that should work for every Criteria query :
use a standard comment and an Hibernate Interceptor changing the ultimate SQL to the database.
(I used it with Hibernate 3.3, but should be useable for every version, registering of the Interceptor may be different.)

In your query code use:

criteria.setComment("$HINT$ push_pred(viewAlias)");

Write an Interceptor to change to SQL text (this one uses commons.lang3.StringUtils):

public class HibernateEntityInterceptor extends EmptyInterceptor {

@Override
public String onPrepareStatement(String sql) {
    if (sql.startsWith("/* $HINT$")) {
        String hintText = StringUtils.substringBetween(sql, "/* $HINT$", "*/");
        sql = sql.replaceFirst("select ", "select /*+" + hintText + "*/ ");
    }
    return sql;
}

Above is for Oracle, but should be easily adjustable for every DBMS.
Maybe you can/should create a constant for the hint marker "$HINT$".
Logging should be done, too (so you can easily see the correct calling of the Interceptor), I left it out above for simplicity.

The Interceptor must be registered. In Spring this is done in applicationContext.xml:

<bean id="entityListener" class="your.package.HibernateEntityInterceptor"/>

<bean id="sessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
    <property name="entityInterceptor" ref="entityListener"/>
    [...]

Or (copy from the Hibernate 3.3 docs):

A Session-scoped interceptor is specified when a session is opened using one of the overloaded SessionFactory.openSession() methods accepting an Interceptor.

Session session = sf.openSession( new HibernateEntityInterceptor() );

A SessionFactory-scoped interceptor is registered with the Configuration object prior to building the SessionFactory. Unless a session is opened explicitly specifying the interceptor to use, the supplied interceptor will be applied to all sessions opened from that SessionFactory. SessionFactory-scoped interceptors must be thread safe. Ensure that you do not store session-specific states, since multiple sessions will use this interceptor potentially concurrently.

new Configuration().setInterceptor( new HibernateEntityInterceptor() );

Answer 2

I was able to put in a Oracle hint by a adding a ProjectionList to the criteria.

ProjectionList proList = Projections.projectionList();
proList.add(Projections.sqlProjection("/*+INDEX_DESC(this_ MY_INDEX_NAME)*/ 1 as MYHINT",
    new String[]{},
    new Type[]{}));
//add properties from your class
proList.add(Projections.property("field1"));
proList.add(Projections.property("field2"));
proList.add(Projections.property("field3"));
c.setProjection(proList);

c.list() returns List<Object[]> in order of ProjectionList