Hibernate lazy initialized collection with hint for Oracle DB

Question

I have entity with lazy initialized collection:

SomeEntity someEntity = template.findByNamedQuery("queryName", entityId);
if (someEntity != null) {
    Hibernate.initialize(someEntity.getChildCollection());
}

Hibernate generate SQL:

SELECT 
  t.COL1 AS COL1_,
  t.COL2 AS COL2_,
  ...
  t.COLN AS COLN_
FROM SCHEMA.TABLE t
WHERE t.COLX = :1
ORDER BY t.COL1 ASC;

There is index IDX_COLX on column COLX.

But for some unknown reason sometimes Oracle doesnt use this index and use full scan on table. I dont control DB, but I was told (by db admin) that solution to this is to pass hints for Oracle.

Something like this:

SELECT /*+ index(t IDX_COLX) */
  t.COL1 AS COL1_,
  t.COL2 AS COL2_,
  ...
  t.COLN AS COLN_
FROM SCHEMA.TABLE t
WHERE t.COLX = :1
ORDER BY t.COL1 ASC;

Is there any simple way to force hibernate do attach this additional information to generated SQL query? I dont want to rewrite whole application because of some Oracle bug or misconfiguration.

I use hibernate 3.3.2.

EDIT:

I tried solution given by StuPointerException and generated SQL looks like:

/*+ index(t IDX_COLX) */
SELECT
  t.COL1 AS COL1_,
  t.COL2 AS COL2_,
  ...
  t.COLN AS COLN_
FROM SCHEMA.TABLE t
WHERE t.COLX = :1
ORDER BY t.COL1 ASC;

Tested that in Oracle SQL Developer and it looks like Oracle doesnt recognise this hint if placed before SELECT statement.

Answer 1

You can achieve this by enabling the property use_sql_comments on your HibernateSessionFactory:

<property name="use_sql_comments">true</property>

You will then be able to do this:

String hql = "from SomeEntity e where e.COLX = :colx";
List result = session.createQuery(hql)
        .setString("colx", "xyz")
        .setComment("+ index(t IDX_COLX)")
        .list();

It does mean that you have to take more control over the relationship in your code though, which is a bit of a pain.

Good luck!