Analyzes a given workload to make recommendations for partitioning expressions.
INITIATE PARTITION ANALYSIS also considers the potential performance benefits of partitioning one or more tables in a given workload. The resulting recommendations, if any, are stored in the PartitionRecommendations table in the specified Query Capture Database.
ANSI Compatibility
INITIATE PARTITION ANALYSIS is a Teradata extension to the ANSI SQL:2011 standard.
Required Privileges
- INSERT and SELECT on PartitionRecommendations
- INSERT on RangePartExpr
INITIATE PARTITION ANALYSIS
INITIATE PARTITION ANALYSIS does not recommend the removal of any defined partitioning expressions, but can recommend changing an existing partitioning expression if you include a row-partitioned table in the specified table_name list.
The recommended partitioning expressions (if any) minimize the cost of requests in the specified workload. Recommendations typically involve a RANGE_N function on an INTEGER or DATE column. Because of the inherent nature of partitioning, following the recommendations may degrade the performance of one or more individual requests, but workload cost is reduced.
The partitioning expression analysis process stores its final recommendations in the QCD table PartitionRecommendations. INITIATE PARTITION ANALYSIS stores a separate row in this table for each workload request that is affected by the recommendation.
Depending on the size and complexity of the defined workload, the operations undertaken by an INITIATE PARTITION ANALYSIS request can be resource intensive. This is because the process of repeatedly calling the Optimizer with different sets of candidate partitioning expressions incurs heavy CPU usage in the Parsing Engine. Therefore, run your partitioning expression analyses on a test system rather than a production system.
Partition Analysis and Multilevel Partitioning
INITIATE PARTITION ANALYSIS does not recommend multilevel partitioning or column partitioning. However, for analyses that report more than one recommendation for a partitioning expression, consider experimenting with combining recommendations to create a multilevel partitioning (with or without column partitioning).
INITIATE PARTITION ANALYSIS Not Supported from Macros
You cannot specify an INITIATE PARTITION ANALYSIS request from a macro. If you run a macro that contains an INITIATE PARTITION ANALYSIS request, the database aborts the request and returns an error.