15.10 - RangePartExpr - 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

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.