Function of RangePartExpr
Stores the individual details of a recommended range partitioning expression based on the RANGE_N function.
Each row in this table represents one range of a given RANGE_N function, where the range boundary values and range size are stored as SQL text strings. The set of rows belonging to a given RANGE_N function can be identified by its common values in columns WorkloadID, RecommendationID, and TableID, and can be ordered by a sequence value that is stored in the table.
See Partition Recommendations for information about the SQL text for recommended partitioning expressions.
RangePartExpr Table Definition
The following CREATE TABLE request defines the RangePartExpr table:
CREATE SET TABLE RangePartExpr ( WorkLoadID INTEGER NOT NULL, RecommendationID INTEGER NOT NULL, TableID BYTE(6) NOT NULL, TestColumn VARCHAR128 CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL, RangeStart VARCHAR(18) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL, RangeEnd VARCHAR(18) CHARACTER SET UNICODE NOT CASESPECIFIC, RangeSize VARCHAR(50) CHARACTER SET UNICODE NOT CASESPECIFIC, RangeSequence INTEGER NOT NULL ) PRIMARY INDEX (RecommendationID, TableID);
Attribute Definitions for RangePartExpr
The following table defines the RangePartExpr table attributes.
| Attribute | Definition |
|---|---|
| WorkLoadID | An identifier of the workload that was analyzed. A component of the NUPI on RangePartExpr. |
| RecommendationID | An identifier for the set of partitioning recommendations that this recommendation belongs to. A component of the NUPI on RangePartExpr. |
| TableID | Unique identifier for the table on which the partitioning expression recommendation is being made. |
| TestColumn | Column name serving as the test_value in the RANGE_N function |
| RangeStart | Starting boundary value for current range; stored in string representation |
| RangeEnd | Ending boundary value for current range; stored in string representation |
| RangeSize | Size of the current range as specified by an EACH clause |
| RangeSequence | Sequence number of the current range within the function expression that can be used to define the ascending order of a set of ranges. Values start at 1 |
Example: RangePartExpr
The following INITIATE PARTITION ANALYSIS request makes the entries in RangePartExpr reported by the query that follows it.
INITIATE PARTITION ANALYSIS ON recent_orders FOR MyWorkload IN MyQCD AS IPA_recent_orders; SELECT TestColumn, RangeStart, RangeEnd, RangeSize, RangeSequence FROM MyQCD.RangePartExpr AS rpe, MyQCd.PartitionRecommendations AS pr WHERE rpe.RecommendationId = pr.RecommendationId AND ResultNameTag = 'IPA_recent_orders';
Result:
TestColumn RangeStart RangeEnd RangeSize RangeSequence ---------- ---------- -------- --------- ------------- order_date DATE '2004-01-01' DATE '2005-12-31' INTERVAL '1' MONTH 1
See also Example: PartitionRecommendations for an example that displays the recommended partition expression as text.