Stores the individual details of a recommended range partitioning expression based on the RANGE_N function. The intent of this table is to provide the Index Wizard with additional flexibility for displaying the recommended partitioning expression.
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 “PartitionRecommendations” on page 779 for information about the SQL text for recommended partitioning expressions.
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);
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 |
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';
TestColumn RangeStart RangeEnd RangeSize RangeSequence
---------- ---------- -------- --------- -------------
order_date DATE '2004-01-01' DATE '2005-12-31' INTERVAL '1' MONTH 1
Note that this is the RangePartExpr component of “Example: Recommended Single‑Range Partition” on page 781.