does optimizer re-write query or only applies hints ?

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 :slight_smile:

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:

  1.   Click the **Options** button.
  2.   Select **Tuning Lab | Optimizer | Intelligence**.
  3.   Select **Predefined** **Settings**.
  4.   From the list, select **Do not use Oracle optimization hints**.
  5.   Notice the slider for the Intelligence Level is set to 3. For the possibility of generating more SQL rewrites, move the slider to **5**.