Add User-Defined SQL Alternatives
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.
-
Click
.
-
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.
To delete a user-defined SQL alternative
-
Select the alternative in the Run Time pane.
-
Select SQL | Delete User-Defined SQL.
Add Virtual Index Alternatives
Quest SQL Optimizer for IBM® DB2® LUW maximizes SQL performance by automating the manual, time-intensive and uncertain process of ensuring that SQL statements are performing as fast as possible. SQL Optimizer analyzes, rewrites, and evaluates SQL statements within multiple database objects, files, or SQL statements captured by the DB2 Event Monitor. With SQL Optimizer, you can analyze and optimize all your problem SQL from multiple sources. SQL Optimizer also provides you a complete index optimization and plan change analysis solution, from index recommendations to simulated index impact analysis, through comparison of multiple SQL access plans.
SQL Optimizer provides you with the following main modules.
SQL Optimizer (including SQL Rewrite and Generate Indexes functions)
About the Generate Indexes Function
The Generate Indexes function in your SQL Optimizer session generates virtual index sets that can potentially improve the performance of your SQL. This function is part of Index Expert, an internal component that facilitates the management and testing of generated and user-defined virtual indexes in your SQL Optimizer session.
When you run the Generate Indexes function, Index Expert analyzes the tables referenced in the original SQL and generates index candidates. It groups these candidates into virtual index sets, each of which generates a unique plan that accesses the virtual indexes in the set.
When Generate Indexes completes, the generated virtual index sets display as alternatives, along with SQL alternatives generated during SQL Rewrite, on the SQL Optimizer window. From the window, you can view the virtual DB2 access plan and cost for any index-set alternative. The Batch Run process executes the index-set alternatives along with SQL alternatives, allowing you to compare run-time results among all alternatives to determine the best-performing version of your SQL. (During Batch Run, Index Expert physically creates the indexes to retrieve run times and then drops them.)
Additionally, you can use the Index Impact Analyzer to perform an impact analysis of a virtual index set on other SQL statements stored in the database system. This feature determines which SQL statements are impacted by the index set and identifies the index-set alternative that yields the highest performance gain with the least impact on the database system.
Index Expert Database Requirements
The Index Expert function requires DB2 LUW 7 or later to retrieve the indexes recommended by DB2. It requires DB2 LUW 8 or later to generate its own index-set recommendations (using an Artificial Intelligence engine), along with the DB2-recommended indexes. Additionally, Index Expert requires that the statistics be run in order to be able to estimate the size of the index.