You can optimize a single SELECT, DELETE, UPDATE, and INSERT SQL statement in the SQL Optimizer window. This optimization process includes running two major functions—SQL Rewrite and Generate Indexes—on this original statement to obtain alternatives on which you can test performance.
The following procedures describe how to obtain or edit this original SQL statement.
To enter the original SQL statement
Enter the SQL statement in the SQL Text pane on the SQL tab:
Type or paste the statement from a another location.
Open an existing file.
Note: When you use either of these functions, the SQL Rewrite or Generate Indexes function automatically runs on the SQL once the SQL Optimizer window opens.
From Toad, open a SQL Optimizer session on the SQL in the Toad Editor or the SQL used in view, trigger, or MQ table DDL.
Perform any of these functions:
To edit the original SQL statement
Tip: To help construct a SQL statement, use the Editor functions: member lookup, argument lookup, auto correction, indent, outdent, comment, and uncomment functions.
Use the SQL Rewrite function to generate alternatives to an SQL statement that you enter in the SQL Optimizer window or that you sent to SQL Optimizer from another tool, such the Scanned SQL Viewer, Toad Database Explorer, or SQL Formatter.
To rewrite an SQL statement
Note: In Toad, if you open a SQL Optimizer session on the SQL used in a view, trigger, or MQ table, the statement automatically displays in the SQL Optimizer window.
Click to rewrite the original SQL. The rewrite time is dependent on the complexity of the original SQL statement, the SQL Options applied, and the quota values set in the Options window. When the rewrite completes, each rewritten SQL version displays as a SQL alternative in the Run Time pane.
Warning: When you define the optimization quota values that, note that the higher the quota, the longer it may take to rewrite a complicated SQL statement.
Note: The SQL Rewrite process executes multiple transformation rules to produce a list of semantically equivalent SQL alternatives, each with a unique access plan. SQL options defined in the Options window are also applied to produce the list of SQL alternatives.
To abort the rewrite process
Allow a few seconds to terminate all processes.
If you have selected the Automatically start Batch Run after rewriting option, the Batch Run Criteria window automatically opens before the rewrite process begins so you can select the settings for the Batch Run.
The Rewrite Details dialog is optional and can be displayed after the SQL Rewrite process completes. This dialog displays the number of semantically equivalent SQL statements investigated and the number of valid SQL alternatives produced. The dialog displays the following:
Time calculations for the rewrite process.
Number of semantically equivalent SQL statements investigated.
Number of alternative access plans produced.
How many rewrites were eliminated because they have identical access plans or DB2 LUW costs.
Warning message if the Syntax Transformation Quota, Total SQL Options Quota, or Table Join Permutation Quota is reached.
Note: Use the Optimization (2) page on the SQL Rewrite tab in Options to define whether to eliminate alternative SQL statements when they have identical access plans or identical DB2 LUW costs. Eliminating the SQL based on identical access plan is more accurate but it can take longer to determine.
The Rewrite Details dialog is shown every time the rewrite process completes. To disable this window, clear the Show details on next optimization option in the dialog.
To view the Rewrite Details dialog at anytime
Select View | Show Optimization Details in the SQL Optimizer window.
If no alternatives are available, the Generate Indexes button is provided to start the Index Expert process of determining virtual index sets that might improve performance. See Generate Index-Set Alternatives for more information.
Once you have provided the original SQL statement in the SQL Optimizer window, you can create your own tweaked version of the statement and add it as a SQL alternative. You can do this either before or after you have rewritten the original SQL statement. You can then test run the user-defined SQL alternatives with the alternatives created by the SQL Rewrite and Generate Indexes functions. Or, you can simply test your alternative against the original SQL statement.
To insert your own SQL alternative
In the Run Time Pane on the SQL tab in the SQL Optimizer window, select a SQL alternative (or the original SQL statement) to use as a template.
In the SQL Text Pane, edit the SQL syntax to create your own version.
Click to retrieve the access plan for your SQL alternative. The plan is automatically parsed to determine whether its cost is identical to the cost of another alternative or the original SQL. If identical costs are found, SQL Optimizer displays a message.
Note: User-defined SQL alternatives are not checked to determine whether they are semantically equivalent to the original SQL. However, after you perform a Batch Run on the alternative, you can check the Records Returned and Remarks columns in Run Time pane to see whether the alternative's record count matches the record count for the original SQL.
Select the alternative in the Run Time pane.
Select SQL | Delete User-Defined SQL.