Toad World Blog

Generating more SQL alternatives

May 10, 2013 11:00:00 PM by Steve Hilker

When you optimize a simple SQL statement, Quest SQL Optimizer may only generate a few SQL alternatives using the default settings in the Optimizer options. It is very likely that many more alternatives can be generated, but they did not get generated because of the way the options were set. This tip covers how to generate more SQL alternatives.

The options settings in Quest SQL Optimizer for Oracle enable you to select which Oracle optimization hints are applied, include or exclude a few of the syntax transformation rules that are used, select how many driving paths will be tried, and increase or decrease the number of SQL alternatives generated. We’ll take a look at how you can do this using the Optimizer options. These options apply to the optimization process in both the Tuning Lab and the Batch Optimizer.

  1. To open the Options window, click Options . TT_Optimization_GeneratingMoreAlternatives_Pic1.png-550x0
  2. Select Tuning Lab | Optimizer | Intelligence.
  3. By default, the “intelligence level” of the optimization process is set to Predefined Settings with level 4. Change the intelligence level to 10.

    TT_Optimization_GeneratingMoreAlternatives_Pic2.png-550x0

  4. Level 10 selects all of the Optimization options, all of the Oracle optimization hints, and sets the quotas for the maximum number of SQL statements that will be generated very high.

    TT_Optimization_GeneratingMoreAlternatives_Pic3.png-550x0

Applying Level 10 to your original SQL statement maximizes the possibility of finding a better alternative for you.

Tags: Toad for Oracle SQL Optimizer for Oracle Wiki

Steve Hilker

Written by Steve Hilker

Steve Hilker was a Product Manager for Quest Software. Steve has over 35 years technical experience spanning application development, system administration, database management and various management positions at several software companies. Steve was the founder of RevealNet, best known for its desktop knowledge bases and unique database tools such as PL/Formatter. RevealNet was acquired by Quest Software in 2001. He's had the pleasure of being the product manager for many of Quest's database tools.

In his private life, Steve enjoys oil painting and living on a small farm in a pre-Civil war manor home in Fredericksburg, Virginia.