What-if Analysis - Teradata Index Wizard

Teradata Index Wizard User Guide

Product
Teradata Index Wizard
Release Number
15.10
Language
English (United States)
Last Update
2018-10-07
dita:id
B035-2506
Product Category
Teradata Tools and Utilities

A what-if analysis allows you to suggest indexes for the selected workload and monitor the performance improvement. Statistics in the simulation mode are collected into the QCD and are simulated to the Optimizer during the plan generation. Only the CREATE INDEX and COLLECT STATS are valid in this mode. If DROP INDEX is specified in the simulation mode, the indexes/stats are not provided to the Optimizer and the generated plans do not consider these indexes/statistics.

You can build the required CREATE INDEX and COLLECT STATS DDL statements using Index Wizard. The DDL statements specified are submitted in a simulation mode to the Teradata Database and then the workload statements are submitted to generate the new execution plans. Index Recommendations Report does not display after a What-if analysis.

1 Select Analysis > What-If Analysis

The Select Workload dialog box opens.

2 Enter a QCD Name where Index Wizard will look for SQL statements. You can click to load a list of available QCDs, then select the name of the QCD from the list.

3 Enter a Workload Name, or click to load a list of available workloads, then select the name of the workload from the list.

4 [Optional] Select the Index Tag for the workload if it exists. This allows you to simulate an index recommendation generated by a previous index analysis operation.

5 [Optional] For Teradata Database version 12.0, select the Partition Tag for the workload if it exists.

6 [Optional] Select View Details.

The View Details dialog box opens. For more information, see “View Details Button” on page 96.

7 Click Next.

The What-If Analysis dialog box appears.

Figure 7: What-If Analysis

8 The following table describes the options in the What-If Analysis dialog box.

 

Button

Description

Add System Recommendations

Opens the View Details dialog box.

Note: The Add System Recommendations button is enabled only if an Index Tag is specified in the Select Workload dialog box.

  • The System Recommendations tab displays system-recommended indexes, and allows you to add one ore more of these indexes.
  • The Reports tab enables you to view reports for workload statements, workload analysis, table scan, and existing indexes.

    • The Workload Statements check box is selected by default. Select View to display the Workload Statements Report. This report shows the query IDs of the statements in the workload, the frequency of the statements as defined in the workload, and the text of the statements in the workload.

    You can select any of the check boxes in the Reports tab to display additional reports for workload analysis, table scan, or existing indexes. For more information on these reports, see Chapter 7: “Reports.”

    Add Index DDL

    Opens the What-If Analysis: Add Index dialog box where you can define indexes to simulate. See “To do a what-if analysis” on page 91.

    Save Recommendations

    Select this check box to save the selected recommendations.

    Recommendations Tag

    Use this option to save the selected What-If recommendations in the QCD.

    Remove

    Removes any of the selected statements from the selected statements list.

    If you select the row number to highlight the row, Remove becomes enabled. To select multiple rows, press and hold the Shift key and then click the row number.

    Remove All

    Removes all the statements from the selected statements list.

    Analyze

    Performs the analysis with the generated statements.

    Advanced

    Opens the Analysis Options dialog box that allows you to specify the cost parameters, random AMP samples, and statistics sample percentages used during the analysis. For more information, see “Advanced Button” on page 97.

    9 Click Add Index DDL. The What-If Analysis: Add Index dialog box displays.

    10 Fill in the dialog box. The following table describes the options in the What-If Analysis: Add Index dialog box.

     

    Button

    Description

    DDL Type

    Create Index

    Generates a CREATE INDEX DDL statement on the table and columns selected, depending on the index type selected.

    Drop Index

    Generates a DROP INDEX DDL statement on the columns of the table selected.

    Collect Stats

    Generates a COLLECT STATISTICS statement on the columns of the table selected.

    Index Types

    Index Type

    Select the index type. It can be either a secondary index, such as unique secondary index, non unique secondary index, hash ordered secondary index, or value ordered secondary index, or a join index.

    Simple

    Global

    Aggregate

    Specify if the join index is of this type.

    For definitions of these index types, see the “Glossary” on page 163.

    Note: These buttons are enabled when the index type selected is join index.

    Join Index Name

    Specify the join index name.

    Note: This field is enabled when the index type selected is join index.

    Select Table

     

    Select a table on which the index and statistics will be simulated.

    Select Column

     

    Select the column(s) on which the index and statistics will be simulated. The columns in this table will be dynamically loaded depending on the type of index selected.

  • For Non Unique Secondary Indexes and Unique Secondary Indexes: Select the index columns from the grid.
  • For Value Ordered Secondary Indexes and Hash Ordered Secondary Indexes: Select the index columns and the Order By column. You can select only one Order By column.
  • For Partitioned Primary Index: Select a index column from grid, you can select only a column with data type either INTEGER or DATE, as at present TIWiz supports only these two data types for PPI recommendations.

    • Note: A column selected as an Order By column must also be selected as an index column. If this is not the case, Index Wizard automatically selects and adds that column to the index columns.

  • For SIMPLE and GLOBAL Join Indexes: Select the columns for the join index and specify which of these columns make the primary index.
  • For AGGREGATE Join Indexes: Select the columns for the join index. From the Aggregate Function column, select the aggregate function on the selected column(s) if you would like to have one and specify which of these columns would make the primary index. The columns in the Group By clause are determined by the selected non-aggregate columns. The order in the Group By clause is determined by the order in which they appear in the grid.

    •  

    Note: A column selected as a primary index within a join index must also be selected as a join index column. If this is not the case, it automatically gets selected and is added to the join index columns list.

    Partition Inputs

    From

    Specifies the Start value/ date for range. It can be either integer or date depending on the partition by column. For DATE type columns, the Calendar displays to select a start date.

    To

    Specifies the End value/ date for range. It can be either integer or date depending on the partition by column. For DATE data type columns, the Calendar displays to select an end date.

    Size

    Size of the interval.

    Interval

    Appears if selected Partition By column is of DATE data type other wise it is invisible. Supports three types of interval for DATE, 1. Day, 2. Month.3.Year.

    Index DDL

    Index DDL

    The text in this box gets updated depending on the index type and columns selected. After you click Add, the index type and columns selected will be added to the parent dialog. Any missing value is represented by question marks (??).

    Add

    Click to add the Index and Statistics DDL to the What-If Analysis dialog box for simulation.

    Note: This button is enabled only after you have completed all the necessary actions in this dialog box, including any missing values. Refer to the Index DDL box if you have any missing values.

    11 Click Add.

    A confirmation message displays indicating the specified DDL has been added.

    When the analysis is complete, the Index Recommendation Report appears. For more information, see “Index Recommendation Report” on page 116.

    If Collect Statistics statements are present in the DDL statement, the Statistics Information dialog box opens during the analysis.

    1 [Optional] Set the dialog box as desired.

    2 Click Apply to apply the statistics.

    3 Close the dialog box to continue the analysis.

    For more information on how to make changes in the analysis using this window, see “Statistics Information Window” on page 99.