INITIATE PARTITION ANALYSIS | SQL Statements | Teradata Vantage - 17.05 - INITIATE PARTITION ANALYSIS - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

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.

For more information about QCDs, partitioning expression analysis, and partitioned tables and join indexes, see:
  • 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
  • Teradata® Index Wizard User Guide, B035-2506

ANSI Compatibility

INITIATE PARTITION ANALYSIS is a Teradata extension to the ANSI SQL:2011 standard.

Required Privileges

You must have the following privileges on tables in database QCD_name:
  • INSERT and SELECT on PartitionRecommendations
  • INSERT on RangePartExpr

Invocation

Normally invoked using the Teradata Index Wizard utility.

About 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.