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.
- CREATE TABLE (Index Definition Clause) in Teradata Vantage™ - SQL Data Definition Language Detailed Topics, B035-1184
- INITIATE INDEX ANALYSIS
- Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142
- Teradata Vantage™ - Database Design, B035-1094
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 currently defined partitioning expressions. It does consider and can recommend changing an existing partitioning expression if you include a row-partitioned table in the specified table_name list.
The set of partitioning expression recommendations, if any, is the one that minimizes the total cost of all requests in the specified workload. Recommendations made by INITIATE PARTITION ANALYSIS typically involve a simple RANGE_N function on an INTEGER or DATE column. Because of the inherent nature of partitioning, it is possible that by following the recommendations INITIATE PARTITION ANALYSIS provides, the performance of one or more individual requests can be degraded, even though the total workload cost is reduced.
The partitioning expression analysis process stores its final recommendations in the QCD table PartitionRecommendations. For the definitions of PartitionRecommendations and its columns, see Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142. 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 very 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. Because of this, you should 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, you might consider experimenting with combining some or all of the 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 execute a macro that contains an INITIATE PARTITION ANALYSIS request, the database aborts the request and returns an error.