does optimizer re-write query or only applies hints ?

zambie · February 25, 2009, 8:23am

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

Bert_Scalzo · February 25, 2009, 8:28am

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

zambie · February 25, 2009, 8:47am

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 ?

Rene · February 25, 2009, 10:13am

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**.

Rene