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. Do not execute these SQL requests without first verifying that they are compliant with your system requirements.
|
| 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.
|
| AnalysisData | Reserved for future use. |
Example: PartitionRecommendations
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';
Result:
ExpressionText -------------- PARTITION BY RANGE_N (order_date BETWEEN DATE '2004-01-01' AND DATE '2005-12-31' EACH INTERVAL '1' MONTH)
See also RangePartExpr for an example that displays the recommended partitioning expression component parts.