does sql optimizer re-write the query or does it just applies hints to make the query faster ?? If it re-write the query, how am i suppose to be avoiding the hints. Our organization dont let us allow using hints for some of there wierd reasons. So wanted to see if sql optimizer re-write the query without using hints, if so how can it be done. As everytime i use it, all the alternatives are using hints. Thanks
It can do both - which you can control via options. SQL rewrites can often yield large gains. Suppose query is correlated or non-correlated subquery that has a WHERE condition applied at the wrong level. A simple correction like this via a rewrite can yield substantial improvements. There are many, many such scenarios. So it does not have to rely on hints to perform its magic
but how do i get that to work ? As everytime i use it, i only uses hints…is there a intelegent level that i need to check to avoid using hints ?
To set the SQL optimization process to not generate SQL alternatives with Oracle hints:
Click the **Options** button.
Select **Tuning Lab | Optimizer | Intelligence**.
Select **Predefined** **Settings**.
From the list, select **Do not use Oracle optimization hints**.
Notice the slider for the Intelligence Level is set to 3. For the possibility of generating more SQL rewrites, move the slider to **5**.