Chat now with support
Chat with Support

SQL Optimizer for DB2 LUW 4.4.1 - Release Notes

Index Expert Window

Index Expert Window

ClosedView the Index Expert Window

The Index Expert window is divided into the following sections:

Top Panes

SQL Editor tab

Used to enter the SQL statement you want to analyze for index candidates.

Index List tab

Displays a list of indexes from the Index Sets.

Index Information

Description

Schema

Displays the schema name where the index resides.

Table

Displays the table name for the index.

Columns

Displays the indexed column names.

Index

Displays the index name. If the index is recommended by SQL Optimizer then the name will be prefixed with QIDX_ . You can change the prefixed in the Index Options in the Options window.

Selectivity

Displays the selectivity value. The selectivity is the percentage of rows in the table that the query selects. A query that selects a small percentage of a table's rows has good selectivity, while a query that selects a large percentage of rows has poor selectivity.

Note: A sample block of the actual table data is used to calculate the selectivity value therefore it is possible that the value may vary according to what sample block is used.

Estimated Index Size (MB)

Displays the estimated size of the index.

Context

Displays the index DDL.

Time tab

Item

Description

Index Set

Displays the Used Index and the list of alternative Index Sets.

Virtual Cost

Displays the virtual access plan DB2 LUW Cost.

Actual Cost

Displays the actual access plan DB2 LUW Cost. The actual plan cost is not available until after the Index Set is physically created on the database and the access plan is retrieved using the indexes from the Index Set.

Elapsed Time

(All Records and First n Records)

Display the time that it takes the SQL statement to select all records or the first record using the Index Set.

Times of Improvement

(All Records and First n Records)

Displays the times of improvement for retrieving all records or the first record using an Index Set in comparison with the SQL statement with no alternative Index Set applied.

Records Returned

(All Records and First n Records)

Displays the number of records processed by the SQL statement. This figure should remain constant throughout the original and alternative Index Sets.

Remark

Remarks may be added during the test run.

Note:DB2 LUW cost should be used as the recommended testing order and should not be used as the actual performance indication. The only way to find out which Index Set is the most efficient is to retrieve the run time.

Summary tab

Displays information about the effect each Index Set has on the performance of the SQL statement showing the number of full table scans, which indexes are used, the cost, and the run times.

Bottom Panes

Bottom Left Pane

Displays the index used by the original SQL (Used Index) and a list of generated (SetN), simulated (SimN), or DB2 LUW generated (DB2 LUW) Index Sets.

Bottom Right Pane – SQL Information Pane

The SQL Information pane has buttons at the top of it to select whether to display the virtual access plan graph, virtual access plan tree, virtual DB2 Optimized Text, access plan graph, access plan tree, DB2 Optimized Text, and Information (the classification of the SQL, the location in the file or database object and login information). If the button is enabled, there is information on that page. If the button icon is grayed, then no information is available for that SQL statement.

 

Related Documents