Statistics Information Window - 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

The What-if analysis starts after you click Analyze. During the analysis, the Statistics Information window opens if:

  • Collect Statistics statements are present in the DDL statement, and
  • The View/Modify Statistics Information Samples option is set to “Yes” in the Analysis Options - Set Statistics Samples dialog box.

    • 1 When the Statistics Information window opens, use the navigation pane to drill down to the specific table to view.

    The following table describes the information in the Statistics Information window.

    2 Make the changes using the editable fields.

    3 Click Apply Modifications to apply the changes to the current analysis.

    4 Close the Statistics Information window to continue the analysis.

    Note: The headers shown in the example screen above change when a column containing partition statistics is present. Refer to the following tables.

    Summary Information Data

     

    Normal Column Statistics Data

    Column Partition Statistics Data

    NumOfBiasedValues

    NumOfCPPartitions

    SamplePercent

    Reserved

    NumOfNulls

    Reserved

    NumOfAllNulls

    Reserved

    NumOfPartialNullVals

    Reserved

    PartialNullHMF

    Reserved

    AvgAmpRPV

    Reserved

    MinVal

    Min Partition Number

    MaxVal

    Max Partition Number

    ModeVal

    Mode Partition

    HighModeFreq

    Compression Ratio

    CPUUsage

    Reserved

    IOUsage

    Reserved

    Statistics Values Information under Biased Values and Frequencies

     

    Interval Number

    Column Partition Number

    BiasedValue

    CP Partition Number

    BiasedValueFreq

    CP Compression Ratio

    Note: The labels for Equal Height Intervals do not undergo position switching.

     

    Group/Option

    Option

    Description

    Statistics Information

     

     

     

     

     

     

     

     

    Database

    Displays the name of database where the table exists.

    Table Name

    Displays the name of table where the column exists.

    Column Name

    Displays the name of column associated with the interval statistics.

    Interval Type

    Select the type of interval from the list:

  • All (Default)
  • High-Biased Intervals
  • Equal-Height Intervals
    		•

    Version

    For internal use.

    Summary Information

    Min Value

    Minimum value for the entire column/index.
    For example, if entering a date value, it must be in the form of MMDDYYYY.

    Note: This information is about the column as a whole. This is also known as interval 0 data.

    Mode Value

    The most frequently used (popular) value in the interval.

    Note: This information is about the column as a whole. This is also known as interval 0 data.

    Sampled

    Displays whether the statistics are sampled or not.

    Mode Frequency

    Displays the number of rows having the Mode Value.

    Note: This information is about the column as a whole. This is also known as interval 0 data.

    Number of Nulls

    Displays the number of rows with one of the statistics fields being null.

    Note: This information is about the column as a whole. This is also known as interval 0 data.

    Sample Percent

    Displays the percentage of statistics sampled if statistics were collected on the column.

    Number of Rows

    Displays the number of rows in the table.

    Note: This information is about the column as a whole. This is also known as interval 0 data.

    Number of Uniques

    Number of distinct Non-Mode values in the interval.

    Note: This information is about the column as a whole. This is also known as interval 0 data.

    Number of Intervals

    Number of intervals in the frequency distribution for the column/index. The maximum number of intervals for Teradata Database version 12.0 is 200. For Teradata Database versions earlier than 12.0, the maximum number of intervals is 100.

    Number of All Nulls

    Displays the number of rows with all of the statistics fields being nulls. This option is only available when connected to Teradata Database 13.00 and higher.

    Average AMP RPV

    Displays the average AMP-local RPV obtained by averaging the average RPV from each AMP. This option is only available when connected to Teradata Database 13.00 and higher.

    Number of Amps

    Displays the number of Amps in the connected Teradata Database. This option is only available when connected to Teradata Database 13.00 and higher.

    One AMP Sample Est

    Displays one AMP sampling estimate of the table cardinality during statistics collection. This option is only available when connected to Teradata Database 13.00 and higher.

    All AMP Sample Est

    Displays all AMP sampling estimate of the table cardinality during statistics collection. This option is only available when connected to Teradata Database 13.00 and higher.

    Note: The following values are displayed in the spreadsheet for all intervals.

    Max Value

     

    The highest value in the interval.

    Mode Value

     

    The most frequently used value in the interval.

    Mode Frequency

     

    The number of rows having the Mode Value.

    Non-Modal Value

     

    The number of distinct non-modal values (values that are not the most frequently used) in the interval.

    Note: If the Non-Modal Value is “-1”, it means there is one loner in the interval. If the Non-Modal Value is “-2”, it means there are two loners in the interval.

    To learn more about loner values, refer to SQL Reference Statement and Transaction Processing. Chapter 2 contains information on loner values and interval histograms.

    Non-Modal Rows

     

    The total number of rows for all the Non-Modal values in the interval.