Viewing Statistics for Tables Used in the Execution Plan - Visual Explain

Teradata Visual Explain User Guide

Product
Visual Explain
Release Number
15.00
Language
English (United States)
Last Update
2018-09-27
dita:id
B035-2504
lifecycle
previous
Product Category
Teradata Tools and Utilities

Viewing Statistics for Tables Used in the Execution Plan

To compare current table statistics to captured‑table statistics

Load an execution plan before viewing statistics. For more information, see “Loading and Viewing an Execution Plan” on page 149.

1 Click .

The Display Object Information window opens. The left pane displays a hierarchical tree of the objects referenced in the query.

Figure 54: Display Object Information

 

2 To view the statistics that existed at the time the execution plan was created, highlight a table in the database list (left pane). If the table still exists, the current statistics appear in the right pane.

 

 

Description

Statistics Information

Database Name

Displays the name of the database on which the table is present.

Table Name

Displays the name of the table on which the column is present.

Column Name

Displays the name of the column on which the interval statistics are displayed.

Interval Type

Indicates the type of interval:

  • High‑Biased Intervals
  • Equal‑Height Intervals
  • By default, ALL is selected.

    Version

    Displays the version number of the statistics structure in effect when the statistics were collected.

    Timestamp

    Displays the time the statistics were collected.

    Summary Information

    Min Value

    Displays an estimate of the smallest value for the specified column or index in the specified table.

    Note: The description relates to the columns listed under the Sampled Stats tab as a whole. This is known as interval 0 information. Interval 0 contains global statistics for the column or index.

    Mode Value

    Displays an estimate of the most frequently occurring value or values for the column or index in the specified table.

    Note: The description relates to the columns listed under the Sampled Stats tab as a whole. This is known as interval 0 information. Interval 0 contains global statistics for the column or index.

    Sampled

    Indicates whether the statistics are sampled.

    Mode Frequency

    Displays an estimate of the number of rows in the interval that have a modal value for the column or index.

    Note: The description relates to the columns listed under the Sampled Stats tab as a whole. This is known as interval 0 information. Interval 0 contains global statistics for the column or index.

    Number of Nulls

    Displays the number of rows for which one of the stats collected fields is null.

    Sample Percent

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

    Number of Rows

    Displays the number of rows in the table.

    Note: The description relates to the columns listed under the Sampled Stats tab as a whole. This is known as interval 0 information. Interval 0 contains global statistics for the column or index.

    Number of Uniques

    Displays the number of unique values for the column on which statistics were collected.

    Note: The description relates to the columns listed under the Sampled Stats tab as a whole. This is known as interval 0 information. Interval 0 contains global statistics for the column or index.

    Number Of Intervals

    Displays the number of intervals in the frequency distribution for the column or index. For Teradata Database versions earlier than 12.00.00, the maximum number of intervals is 100. For Teradata Database versions 12.00.00 or later, the maximum number of intervals is 200.

    Number of All Nulls

    Displays the number of rows having all the stats collected fields being nulls.

    Average AMP RPV

    Displays the average AMP‑local RPV obtained by averaging the average RPV from each AMP.

    Number of Amps

    Displays the number of AMPs in the connected Teradata Database.

    One AMP Sample Est

    Displays one AMP sampling estimate of the table cardinality at the statistics collection time.

    All AMP Sample Est

    Displays all AMP sampling estimates of the table cardinality at the statistics collection time.

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

    Interval

    A bounded, non‑overlapping set of attribute values.

    Mode Value

    Displays an estimate of the most frequently occurring value or values for the column or index in the specified table.

    Note: The description relates to the columns listed under the Sampled Stats tab as a whole. This is known as interval 0 information. Interval 0 contains global statistics for the column or index.

    Max Value

    Displays an estimate of the largest value for the column or index in the interval.

    Note: The description relates to the columns listed under the Sampled Stats tab as a whole. This is known as interval 0 information. Interval 0 contains global statistics for the column or index.

    Mode Frequency

    Displays an estimate of the number of rows in the interval that have a modal value for the column or index.

    Note: The description relates to the columns listed under the Sampled Stats tab as a whole. This is known as interval 0 information. Interval 0 contains global statistics for the column or index.

    Non‑Modal Value

    Displays 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, there is one loner in the interval. If the non-modal value is -2, there are two loners in the interval. The description relates to the columns listed under the Sampled Stats tab as a whole. This is known as interval 0 information. Interval 0 contains global statistics for the column or index.

    Non‑Modal Rows

    Displays the total number of rows for all the non‑modal values in the interval.

    Note: The description relates to the columns listed under the Sampled Stats tab as a whole. This is known as interval 0 information. Interval 0 contains global statistics for the column or index.

    Original Version

    Displays previous statistics version.

    DBS Version

    Displays database version statistics collected on.

    Usage Type

    Displays Usage Type of the statistics. Valued values are ‘S’ for Summary mode and ‘D’ for Detailed mode.

    Complex Stat Information

    Displays the information for Statistics on Complex Types such as Geospatial.

    Number of History Records

    Displays the Number of History Records.

    Number of Partial Null Values

    Displays the Number of Partial Null Values.

    Partial Null HMF

    Displays the highest frequency of values having partial nulls of the Expression List.

    IO Usage

    Displays the amount of IO consumed.

    Number of Biased Values

    Displays the number of Biased Values.

    Number of EH Intervals

    Displays the number of EH Intervals.

    Number of Distinct Values

    Displays the number of Distinct Values in the table.

    CPU Usage

    Displays the amount of CPU Consumed.