Quest SQL Optimizer can do an extensive transformation of the syntax of a SQL statement while still maintaining the exact same result set. This exhaustive transformation can produce hundreds of SQL alternatives. So it is necessary to have some insight as how to find a SQL alternative that is better than your original SQL statement without spending too much time. This is especially true when you have a SQL statement with complicated syntax.

 

During the syntax transformation process, each section of the syntax of a SQL statement is reviewed and appropriate SQL transformation rules are applied to each section. The more complex the syntax of the SQL statement, the more rules there are that can be applied. Applying more rules naturally takes more time and results in many more SQL alternatives to evaluate.

 

 

When optimizing a SQL statement with complex syntax, the first approach can be to lower the number of alternatives that are generated by Quest SQL Optimizer for Oracle by changing the settings in the Optimizer options. You can do this using the Predefined Intelligence Levels or through customizing the individual Optimizer options. If this does not result in finding a better SQL alternative, then the settings can be changed to generate more alternatives.

 

By lowering the Intelligence Level, fewer optimization hints are applied, fewer Optimization options are selected, and the quotas for the total number of SQL alternatives generated are lowered.
 

 
 

You can also lower the number of SQL alternatives generated by selecting to apply only a specific category of the Oracle optimization hints from the Predefined Settings drop-down list and then select an Intelligence Level.
 

 
 

You can also limit the number of SQL alternatives that are generated through the Custom Settings option. This enables you to select specific Optimization options, Oracle hints, and quotas.
 

 

 

For a SQL statement with very simple syntax, there are likely to be very few ways to transform the syntax or there may be none at all. But the Oracle optimization hints can be applied to the SQL statement to see if Oracle will generate a different execution plan for the SQL statement. Quest SQL Optimizer for Oracle can apply around 50 Oracle optimization hints to a SQL statement and test to see if the hint results in a unique execution plan. These SQL alternatives can then be executed and their run time statistics can be compared to those of the original SQL statement to find the best alternative.

 

To apply all the hints to your SQL statement using Quest SQL Optimizer for Oracle,

 

1.      Click Options.

2.      Select Tuning Lab | Optimizer | Intelligence.

3.      Select Predefined Settings.

4.      From the drop-down list, select Use Oracle optimization hints.

5.      Move the Intelligence Level slider to 10.