Index Expert Window
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. |
|
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. | |
|
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. |
|
Displays the virtual access plan DB2 LUW 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. | |
|
(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.