We have a rather complex HQL query we're currently running and our DBA is wondering if there is a way he can have me embed Oracle query hints into the query. FYI, you can use query hints to give the oracle query engine information about the best way to execute your query. The format is something like:

{DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */

I would only be doing SELECTs, obviously. So is there any way I can embed that "/*+ hint [text] [hint[text]]... */" into an HQL query? I've tried (on a much simpler query) and I get the following exception:

net.sf.hibernate.QueryException: undefined alias: / [
select /*+ INDEX (STORE_LOCATION) */ distinct business
from com.sa.go.bto.Business as business
inner join business.locations as location
where (business.nationalFlag = YES or
location.class = com.sa.go.bto.OnlineBusinessLocation)
]

Thanks in advance!
James

I'd like us to be able to do this, but I suspect that its probably just going to be too different b/w different dbs.

Like, in this case it would be very easy to have Hibernate pass though a comment after the SELECT. But presumably query hints can also appear elsewhere.


If you need it desperately, use a native SQL query (which is very nicely implemented by Max in 2.1).

If you need it less desperately, get your DBA to give me some advice on how best to approach this.

the native SQL query will work excellently. i'll only need to use it in this one place, also, which means it won't be so bad.

it would still be nice to embed it in the HQL, however. according to my DBA, the *only* place the optimizer hint can go is right after the select, something like:

SELECT /*+ hint [text] [hint[text]]... */ *
FROM FOO_TABLE

maybe have some special identifier (braces?) that would allow you to embed static text that will propogate directly through to the final query. it could be useful in other cases as well:

select {/*+ ....... */} business.id
from foo.bar.Business business
where business.nationalFlag = {'Y'}

thanks!
james

Hmmmm if its only after the select thats fine. just wondering about other DBs though. See the trouble is we don't want to let you embed stuff in {} in some place that Hibernate might need to restructure (like the where clause).


Still, I have often considered this, and it might be about time to tackle it....

Hi,
can anyone tell me if this change went ahead? I have tried 'select {...} ...' with 2.1.3 and it gives the same exception.
Cheers

has this been implemented yet ?

See Query.setComment

I tried query.setComment() to add an Oracle index hint to my select query. It was really really close, but not quite there: Oracle requires a comment like this: /*+ index (a IDX_MY_INDEX) */. But the generated SQL has a space after the opening comment: /* + index (a IDX_MY_INDEX */. Is there any way to change this behavior?

Thanks,
Mike

query.setComment("bogus comment */ /*+ index (a IDX_MY_INDEX) ");

I haven't tried it, but I would think Hibernate would translate that to:
/* bogus comment */ /*+ index (a IDX_MY_INDEX) */

which becomes a comment and a Hint...

Yes, but doesn't the hint need to be in a particular place in the SQL query, like anywhere between 'select' and 'from'? I'm guessing the comment will be appended to the end or start of the SQL, thus this method of hinting will not work :/

Yes, for Oracle to accept a hint it needs to go AFTER the SELECT, but BEFORE any selected fields. Again, I have not tested, so I don't know where Hibernate puts it...

Just ran a quick test, Hibernate sticks the comment at the beginning of the query, so it doesn't help...

/* bogusComment */ /*+ INDEX(this_) */ /* criteria query */ select

Yup, tis a pity.

So at the moment it's not possible to add a hint with hibernate if we work with oracle...
Does anybody knows a way to do it?

It's not possible using HQL (at least not that anyone has found); it is possible using Native SQL, as you're formulating the query yourself - which is easy to do without issue, providing your data/inheritence model isn't too complicated.

When using HQL, Hibernate uses pseudo-columns (clazz_) to determine the subclass type. When you instead use Native SQL, Hibernate does not have this information and so cannot map to the correct inheritance. This will only be an issue for you if you have a complicated model, like table A extends table B, etc.