16.10 - Partition Recommendations - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
16.10
created_date
June 2017
category
Programming Reference
User Guide
featnum
B035-1142-161K

Function of PartitionRecommendations

Captures the recommended partitioning expressions generated by an INITIATE PARTITION ANALYSIS statement and identified by a WorkLoadID value.

PartitionRecommendations contains one row for each combination of query and table row partitioning where the new Optimizer plan from the recommended row partitioning either benefits or degrades workload performance as a result of the new row partitioning.

If a particular row partitioning impacts more than one query, INITIATE PARTITION ANALYSIS writes multiple rows in the table to identify each affected query. The recommended PARTITION BY expression is stored as SQL text in the ExpressionText column.

PartitionRecommendations Table Definition

The following CREATE TABLE request defines PartitionRecommendations:

   CREATE SET TABLE qcd.partitionrecommendations (
     WorkLoadID        INTEGER NOT NULL,
     UserName          VARCHAR128 CHARACTER SET UNICODE
                       NOT CASESPECIFIC NOT NULL,
     TimeOfAnalysis    TIMESTAMP(6) NOT NULL,
     RecommendationID  INTEGER NOT NULL,
     QueryID           INTEGER NOT NULL,
     ResultNameTag     VARCHAR128 CHARACTER SET UNICODE
                       NOT CASESPECIFIC NOT NULL,
     TableName         VARCHAR128 CHARACTER SET UNICODE
                       NOT CASESPECIFIC NOT NULL,
     DatabaseName      VARCHAR128 CHARACTER SET UNICODE
                       NOT CASESPECIFIC NOT NULL,
     TableID           BYTE(6) NOT NULL,
     ExpressionText    VARCHAR(10000) CHARACTER SET UNICODE
                       NOT CASESPECIFIC,
     ExpressionType    CHARACTER(1) CHARACTER SET LATIN
                       NOT CASESPECIFIC,
     RecreateText      VARCHAR(15000) CHARACTER SET UNICODE
                       NOT CASESPECIFIC,
     OriginalCost      FLOAT,
     NewCost           FLOAT,
     SpaceEstimate     FLOAT,
     TimeEstimate      FLOAT,
     StatsDDL          VARCHAR(10000) CHARACTER SET UNICODE
                       NOT CASESPECIFIC,
     Remarks           VARCHAR(1024) CHARACTER SET UNICODE
                       NOT CASESPECIFIC,
     TimeLimitExceeded CHARACTER(1) CHARACTER SET LATIN NOT 
                       CASESPECIFIC
     AnalysisTimeLimit INTEGER,
     AnalysisData      VARCHAR(2048) CHARACTER SET LATIN
                       NOT CASESPECIFIC )
   PRIMARY INDEX (WorkloadID);

Attribute Definitions for PartitionRecommendations

The following table defines the PartitionRecommendations table attributes:

Attribute Definition
WorkLoadID An identifier of the workload that was analyzed.
UserName Name of the user performing the partition analysis.
TimeOfAnalysis A timestamp recording the time at which this analysis completed.
RecommendationID An identifier for the set of row partitioning recommendations this recommendation belongs to.
QueryID Unique identifier for the query that benefits from this recommendation.
ResultNameTag User-assigned name for the set of recommendations identified by RecommendationID.
TableName Name of the table on which the row partitioning recommendation is being made.
DatabaseName Name of the database containing TableName.
TableID Unique internal identifier for TableName.
ExpressionText SQL text of the recommended PARTITION BY expression.
ExpressionType The form of partitioning used by ExpressionText.

If R, the partitioning expression is built from a RANGE_N function.

RecreateText SQL text of statements that will recreate the table with the recommended partitioning expression.

The statements assume the table is populated and so require the current rows to be temporarily stored in another table.

Notice: Do not execute these SQL requests without first verifying that they are compliant with your system requirements. For more information, see Teradata Index Wizard User Guide.

OriginalCost The estimated cost in milliseconds to execute the query without the recommendation.
NewCost The estimated cost in milliseconds to execute the query with the recommendation.

If this value is higher than OriginalCost, the recommendation has negatively impacted this query.

SpaceEstimate The additional incremental space, in bytes, needed to store the table with the recommended row partitioning.
TimeEstimate Reserved for future use.

Estimated time, in milliseconds, to recreate the table with the recommended row partitioning.

StatsDDL SQL text for collecting statistics on the recommended row partitioning expression.
Remarks Additional details regarding the recommendation.
AnalysisTimeLimit User-specified time limit, in whole minutes, for the duration of the analysis.
TimeLimitExceeded Indicates whether or not the recommendation was generated by an INITIATE PARTITION ANALYSIS request whose specified time limit expired.
  • If F, the row partition analysis was not interrupted because the specified INITIATE PARTITION ANALYSIS time limit was exceeded.

    This is the default.

  • If T, the row partition analysis was interrupted because the specified INITIATE PARTITION ANALYSIS time limit was exceeded.

    This means that the final recommendations might have been affected.

AnalysisData Reserved for future use.