Oracle optimizer query hint 

Oracle optimizer query hint 

  
I've a advanced query with index-hint "/*+ INDEX( <table_name> <index_name> ) */" but it seems like outsystems parses the hint as it was comment instead of passing it to Oracle. How can I pass optimizer hints?

the query looks like
select name
,      amount/time as responsetime
from
(
    select name
    ,      amount
    ,      time
    from (
        select /*+ INDEX( <table_name> <index_name> */ name 
            , max(amount) amount, max(time) time 
        From  {STATISTICS}
        where {STATISTICS}.[DATETIME] > @tDateTime
        group by {STATISTICS}.[Name] , trunc({STATISTICS}.[DATETIME])
    ) Table1
) Table2
group by Table2.[Name] 
 
 
I don't know if it'll work, but I'd try passing that as a parameter.

The parameter value would be /*+ INDEX( <table_name> <index_name> ) */
and the query would then be something like

select name
, amount/time as responsetime
from
(
select name
, amount
, time
from (
select @hint name
, max(amount) amount, max(time) time
From {STATISTICS}
where {STATISTICS}.[DATETIME] > @tDateTime
group by {STATISTICS}.[Name] , trunc({STATISTICS}.[DATETIME])
) Table1
) Table2
group by Table2.[Name]

Oh, don't forget the expand inline flag!
Thank you for your response.

When  I use ' 1 as dummy, ' as @hint-value, I can see it in the executed sql-query. But if I use /* something */ as @hint-value, I don't see it in the executed sql-query.
If I run the query on the database itself, I see in the executed sql-query the /*+ index(..)*/ .
So it doesn't work in this case.

outsystems send me a reply about this case. Until Outsystems fix this bug, I have to use integration studio to query with a oracle query hint. So, for now the challenge is to create such an extension.
I haven't used the integration studio with eclipse yet, I don't know how to query and I haven't found documentation about it.
Well, a bug in the platform isn't good...

Anyway, probably an easier solution is to create a stored procedure and then call it from an advanced query. As long as you set all the permissions correctly (the user OSRuntime must be able to run the SP), it should be pretty straighforward.

Hi

this may sound stupid but I have no where to test it. I've read it somewhere...

could you try something like select --+ index(



It is currently not supported to use oracle optimizer hints in outsystems. I haven't used a integration studio solution. 
The real problem is how oracle derives which index should be used. Oracle 11g does this part better than the Oracle 10.2 version which we still use.

As Carlos mentioned, a stored procedure call is the easiest way to use optimizer hints. A stored procedure can be called using the Oracle Connecter extension which you can find in the Forge. I haven't use it yet, but it should work.
Hi Hans,

Curiously, I've had used this pattern in some queries and it worked (in platform version 7).
Looking at your query, I guess the problem could be in your "where clause".

In a clause like "{STATISTICS}.[DATETIME] > @tDateTime", the Oracle will probably execute a full table scan, no matter the hint you may set.

Best regards,
José Martins
I've created a discussion within the Oracle Connector if they can port it to the java stack.

@José, optimizer hints will always overrule standard behaviour. That's why they exists;-).