In SQL Optimizer's Options, the following options are set.
- In Tuning Lab | Optimizer | Hints, check "Custom Settings"
- In the Hints, select the hints you want to apply
- In the Quota make "Syntax Transformation Quota" to 1 and the Hint Quota to 99,999, since we want to only consider hints
I have noticed that in some cases involving sub-queries, SQL Optimizer does not consider the hints even if they are checked?
Waiting for fix in a future release of Quest SQL Optimizer for Oracle.
The sub-query hints are only applied to IN or EXISTS condition when it should be applied to other conditions as well.
Particularly, if the condition is
AND j.effseq = (SELECT ...
hints will not be applied, but given
AND j.effseq IN (SELECT ...
then you should see the hints apply.