How to generate fewer SQL alternatives

When you are optimizing a complicated SQL statement, Quest SQL Optimizer is capable of generating hundred to even thousands of SQL alternatives. While theoretical this may be nice, it is not practical to review, select, and test hundreds of SQL statements, especially if it is a long running SQL statement. This tip covers how to limit the number of SQL alternatives generated by the optimization process.

The options settings in Quest SQL Optimizer for Oracle enable you to control the maximum number of SQL alternatives generated, select which Oracle optimization hints are applied, exclude a few of the transformation rules that are used, and limit the maximum number of SQL statements 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.  

2. Select Tuning Lab | Optimizer | Intelligence. 

3. By default, the “intelligence level” of the optimization process is set to Predefined Settings with level 4 selected. In order to customize the number of alternative statements that are generated and other options, set the intelligence level to Custom Settings.

   

4. Select 

   Tuning Lab | Optimizer | Optimization. 

5. If the Transform view to inline view or Transform query to inline view apply to your SQL statement, fewer SQL alternatives will be generated if you unselect these options. 

   1. The Transform view to inline view option inserts the SELECT statement from each VIEW used in the SQL statement and then transforms the syntax of the original SQL statement and the SQL statements from the VIEWs.

   2. The Transform query to inline view option transforms the subquery to an inline view (a subquery used as a table in a FROM clause).

      

   3. Select Tuning Lab | Optimizer | Hints. 

   4. The Oracle optimization hints are displayed on six pages. Select the hints you want to use for optimizing your SQL statements. The fewer hints selected the fewer alternative SQL statements will be generated by the SQL Optimizer.

      

   5. Select Tuning Lab | Optimizer | Quota. 

   6. Set the quotas to reduce the number of SQL statements generated during the optimization process. 

      1. The Syntax transformation quota limits the number of SQL alternatives generated during the SQL syntax transformation phase of the optimization process. What this means is that the SQL Optimizer will stop finding syntactically different SQL statements after the quota has been reached. 

         Note: Although the SQL statements are syntactically different, they still produce the same result. The Table join permutation quota limits the number of ways the order of the tables is rearranged. This table order rearrangement is actually one of the many rules that comprise the syntax transformation rules. 

      2. The Table join permutation quota is used to control how many SQL syntax transformations are created using only this one rule. 

      3. The Hints quota is the number of SQL alternatives that are generated by applying the Oracle optimization hints to the original SQL statement and the SQL alternatives that were created by transforming the SQL syntax. 

         

By changing the options above, fewer or more SQL alternatives can be generated for your original SQL statement.