15.10 - PartitionRecommendations - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
User Guide
featnum
B035-1142-151K

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.

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);

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 (see “Applying Indexes and Partitioning Expressions” on page 849).

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.

    The following INITIATE PARTITION ANALYSIS request makes the entries in PartitionRecommendations reported by the query that follows it.

         INITIATE PARTITION ANALYSIS ON recent_orders 
         FOR MyWorkload 
         IN MyQCD AS IPA_recent_orders;
     
         SELECT ExpressiontText 
         FROM MyQCD.PartitionRecommendations 
         WHERE ResultNameTag = 'IPA_recent_orders';
     
         ExpressionText
         --------------
         PARTITION BY RANGE_N (order_date 
                               BETWEEN DATE '2004-01-01' 
                               AND     DATE '2005-12-31'  
                               EACH INTERVAL '1' MONTH)

    Note that this is the PartitionRecommendations component of “Example: Recommended Single‑Range Partition” on page 800.