Chat now with support
Chat with Support

SQL Optimizer for DB2 ZOS 5.6.1 - User Guide

Welcome Optimize SQL Options Tutorial About us Third-party contributions Copyright

Generate Index Alternatives

The Optimize SQL function analyzes the input SQL statement and uses an Artificial Intelligence Engine to produce a group of semantically equivalent versions of the statement, known as SQL alternatives. You can then test run these alternatives in the SQL Optimizer window to determine the best-performing version of the SQL.

Note: The Intelligence Level setting you select affects the duration of the optimization process and the number of alternatives SQL Optimizer generates.

About Generating Index Alternatives

SQL Optimizer analyzes the following in your original SQL statement and table references to generate index alternatives:

  • SQL statement syntax
  • Related tables and indexes
  • Search arguments
  • Table join conditions

Once SQL Optimizer generates alternatives, you can test them to evaluate improvements in database performance.

Note: SQL Optimizer does not physically create indexes on your database when generating alternatives.

 

Related Topics

Generate Index Alternatives

Test Run Index Alternatives

After SQL Optimizer generates index alternatives, you can test each alternative. When SQL Optimizer tests indexes, the indexes are physically created on the database and dropped after SQL Optimizer executes the statement. When you test run index alternatives, you must select a storage group in which to store the created indexes.

(missing or bad snippet)

To execute index alternatives

  1. (missing or bad snippet)
  2. Select a SQL Rewrite session.
  3. Select an index set in the Alternatives pane.

  4. Click .

    Tip: Click to test run all index alternatives.

  5. Select a storage group in which to store the created indexes. (missing or bad snippet)

    Index Information Description
    Index Shows SQL text of index selected.
    Storage group

    Storage group to store indexes created.

    Note: Once you select the storage group for the first index, SQL Optimizer automatically selects the same storage group for the remaining indexes.

    Note: Review the information in the DDL Script pane.

 

Related Topic

Generate Index Alternatives

Generate Index Alternatives

This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.

To generate index alternatives for a SQL statement

  1. Select the SQL Details tab in the main window.
  2. Enter the SQL statement in the Alternative Details pane.
  3. Click Index button.

Note: Delete Remaining Indexes Window may appear if it has been detected that some virtual/real indexes exist in the database and these might affect index searching result. It is recommended to remove the unused indexes before starting the search.

  1. Select Index Details in the SQL Information pane to view index generation information.

    Tip: Click to stop the index generation process.

Note: When you generate virtual index alternatives, the execution plan is not retrieved for the SQL statement because SQL Optimizer does not physically create indexes on the database. Fill Missing Execution Plans

 

Related Topics

Test Run Index Alternatives

Related Documents