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.
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Terms of Use Privacy Cookie Preference Center
