How does one use Oracle hints with Hibernate? For example:

SELECT /*+ use_nl (ct ec du) */ ct.CNTCT_TYPE_CD, ct.CNTCT_ID, ct.LOC_CD, ct.PARTY_ID, ct.PROF_TYPE_CD, ct.DEV_SEQ_ID, ec.CNTCT_ID, ec.EMAIL_ADR
FROM DEV_USER du, EMAIL_CNTCT ec, CNTCT ct
WHERE ct.DEV_SEQ_ID = du.DEV_SEQ_ID)
AND du.AD_DOMAIN_USER_ID IN ('usr1', 'usr2', 'usr3', 'usr4', 'usr5', 'usr6', 'usr7')
AND ct.CNTCT_ID = ec.CNTCT_ID AND ct.CNTCT_TYPE_CD IN ('E');


The /*+ use_nl (ct ec du) */ needs to be added to tell Oracle to use nested loops.

You could probably try session.createSQLQuery()

If someone can give me a list of what query hints generally look like in all the major databases, I am not adverse to adding something like:

Query.setHint(String)

or something.

Well, SQL Server 2000 (not sure about earlier versions) uses the OPTION clause at the end of the query:



But it also has table hints and join hints using a different syntax. Not sure if other databases do something similar...