0

I know its a repeated question but I am not able to find implementation of Oracle query hints - /*+PARALLEL(4)*/ in my Hibernate session

Below is the simplified version of my Spring boot service 1. DAOClass, 2. ResponseClass, 3. query.sql

Even though I have provided Query hints in query.sql, hibernate doesn't run the query faster. The same query runs faster in sqldeveloper. I don't know why Hibernate doesn't consider the hint /*+PARALLEL(4)*/

Contents of query.sql

SELECT /*+PARALLEL(4)*/
    cust_id, 
    name
FROM cust_table
WHERE is_active='Y'

Contents of DAOClass

@Repository("DAOClass")
@Transactional(value = "JpaTxnManager", propagation = Propagation.REQUIRED)    
public class DAOClass 
{
    EntityManager entityManager;

    String queryString = readFromSqlFile(query.sql)
    Query query = entityManager.createNativeQuery(queryString, ResponseClass.class)

    List<ResponseClass> responseClassList = query.getResultist()
}

Contents of ResponseClass

@Entity
public class ResponseClass 
{
    @Id
    @Column(name = "cust_id")
    private String custId;

    @Column(name = "name")
    private String name;

    //getter-setter methods
}

I read about Projections here but my implementation is totally different. I would like something thats easily implemented in my existing project.

I don't get any option of query.setComment() like here

I have also checked mkyong implementation. But its different than the method that I have

How do I make /*+PARALLEL(4)*/ work in my Hibernate and spring code above?

Rahul
  • 319
  • 2
  • 7
  • 15
  • 1
    What is not working? Your implementatin looks correct except for `ResponseClass responseClass = query.getResultist()`, should be `List responseClassList = query.getResultList()`. Suppose typo – Evgeni Enchev Feb 01 '19 at 10:04
  • That was a typo while explaining my issue. I've added that in code. Thanks! I've changed the title to reflect my problem properly. Oracle Query hint is not working in Hibernate here. – Rahul Feb 02 '19 at 09:18
  • 1
    I have a similar problem, I am trying to force the use of an index (because the Oracle optimiser does not automatically identify it. So far I have not found a direct solution. However, I did discover that if the native query is created WITHOUT specifying the result class, then the query DOES make use of the optimiser hints. For example if you try `Query query = entityManager.createNativeQuery(queryString); List responseClassList = query.getResultist();` you will probably find that the hint is being used. Sorry I can't add anything further... – John C Oct 11 '19 at 16:57

1 Answers1

0

After digging around and trying a bunch of different suggestions I managed to find a working (at least for me) solution. You need to get access to the Hibernate Query class for the native query, and add the query hint at that point. For example, the original example should look something like this:

Query query = entityManager.createNativeQuery(queryString, ResponseClass.class)
                       .unwrap(org.hibernate.query.Query.class)
                       .addQueryHint("PARALLEL(4)");

In my original comment I noted that specifying the class of the resulting objects apparently caused the index to be ignored. I have since determined that this is not the whole story. In my particular case, forcing the use of an index reduced my query to 107ms. However, the query returned so many results that the process of converting each row into an instance of the class I specified took a further 16s! So even if you add the hint, the actual gains may not be as great as expected (or as seen when executing in SQL Developer, for example).

If you add the following hibernate properties to your persistence.xml (or use a hibernate.properties file) and use the setHint() method to add a hibernate comment to your query, you can (much!) more easily see exactly what is being executed and identify your particular select:

hibernate.show_sql=true
hibernate.format_sql=true
hibernate.use_sql_comments=true

Query query = entityManager.createNativeQuery(queryString, ResponseClass.class)
                       .setHint("org.hibernate.comment", "ADDING QUERY HINT")
                       .unwrap(org.hibernate.query.Query.class)
                       .addQueryHint("PARALLEL(4)");
John C
  • 131
  • 1
  • 6