Chat now with support
Chat with Support

SQL Optimizer for DB2 LUW 4.4.1 - User Guide

SQL Optimizer for IBM® DB2® LUW
UsageStatistics Getting Started Options SQL Scanner SQL Optimizer SQL Formatter SQL Inspector Database Explorer SQL Repository Index Impact Analyzer Index Usage Analyzer User-Defined Temp Table Editor Functions SQL Functions SQL Information and Functions Activity Log
Tutorials About us Copyright

Index Usage Analyzer Window

ClosedView the Index Usage Analyzer Window

The Index Usage Analyzer window displays the information about the SQL statements that are saved in the Index Usage Analyzer. The display in the right section of the window depends on what is selected in the left pane.

Left pane—Analyzer List

Always displays a tree diagram of the Analyzers and the folders they are stored in.

Right Pane

The right pane displays a variety of different information depending upon what is selected from the tree diagram in the left pane.

You can display information for the following:

Analyzers

Table Analyzed

Table Information

SQL Analyzed Folder

SQL Statements

 

Right Pane for Analyzers

In the Index Usage Analyzer window, when an Analyzer is selected in the left pane, the following information is displayed in the right pane.

Index Usage Chart (Top right pane)

Displays the total number of indexes in the tables used in all the selected SQL statements and the number of used and unused indexes. Charts the percentage of the indexes that are used by the access plans for the selected SQL statements.

Analysis Information (Bottom right pane)

The detailed information about the Analysis is displayed in the button pages. The buttons for displaying specific information are found at the top of this pane.

Properties button

Displays general information about the analysis, information on the connection used to retrieve the access plans and the SQL statements used in the analysis.

Index Summary button

Displays the indexes that are used by the selected SQL statements in black text and highlights in red the indexes that are not used.

To save a list of all the unused indexes

  1. Right-click and select Save.

  2. In the Save Format window, select Text, HTML, or Excel Convertible (Spreadsheet).

To drop an index

Right-click the line with the index you want to drop and select Drop Index.

 

Right Pane for Tables Analyzed

In the Index Usage Analyzer window, when the Tables Analyzed is selected in the left pane, the following information is displayed in the right pane.

Summary button

Provides a summary of the use of indexes for each table. It gives the total number of SQL that use the table and if the SQL statement accesses the table with a full table scan or uses an index to access the table.

Chart button

Charts the number of SQL statements that use the table.

 

Right Pane for Analyzed Table Information

In the Index Usage Analyzer window, when a table is selected under the Tables Analyzed section is selected in the left pane, the following information is displayed in the right pane.

Prognosis Button

Pane

Function

Description

Top

Summary Information

Displays a summary of the indexes in the table that are used or unused.

Middle

Index Used Information

Displays the indexes used by the SQL statement.

Bottom Left

SQL Text

Displays the text of the SQL statement.

Bottom Right

SQL Information

Displays the access plan, SQL Classification, and the temporary table DDL for the selected SQL statement in the SQL Information pane.

Unused Index Button

Lists the indexes and the key for all indexes in the table that are not used.

To save a list of all the unused indexes

  1. Right-click and select Save.

  2. In the Save Format window, select Text, HTML, or Excel Convertible (Spreadsheet).

To drop an index

Right-click the line with the index you want to drop and select Drop Index.

Table Information Button

Tab

Description

Definition

Displays when and where the table was created.

Columns

Displays the column information for the table.

Index

Displays the index information for the table.

DDL

Displays the DDL for creating the table and indexes.

 

Related Documents