INITIATE PARTITION ANALYSIS - Teradata Database

Purpose

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.

Required Privileges

Syntax

Syntax Elements

ON table_name

ON database_name.table_name

ON user_name.table_name

Set of comma-separated table names to be considered for partitioning recommendations.

The list can include any mixture of tables, including those that already have partitioning expressions.

If you do not specify a table name set, the system analyzes all tables referenced in the workload that do not currently have partitioning by default.

The maximum number of tables that can be analyzed in a given workload is 1,024.

FOR workload_name

The name of the workload whose queries are to be analyzed.

IN QCD_name

The name of the Query Capture Database containing the specified workload and in which the resulting row partitioning recommendations are to be stored.

result_name_tag

A unique name that you assign to the partitioning expression recommendation set.

The system stores the results of the partition analysis under this name in QCD_name.

TIME LIMIT = elapsed_time

The maximum elapsed time in minutes that you want this partitioning expression analysis to take.

The default value is no time limit.

The permitted range of specified values is from 1 to 2880 minutes, for a maximum of 48 hours.

If the partitioning expression analysis does not complete before reaching the specified time limit, the system stops the task and retains the best recommendations found up to the point when the time limit expired.

You must specify the value for elapsed_time as an integer value.

Note that the time limit that you specify is only an approximation because the ongoing partitioning expression analysis task only checks to see if the time has been exceeded periodically.

ANSI Compatibility

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

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 SQL Request and Transaction Processing . 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, Teradata Database aborts the request and returns an error.

Example: Partition Analysis on All Tables

The following example considers partitioning expression recommendations for all tables in a workload; therefore, no ON clause specified:

     INITIATE PARTITION ANALYSIS
     FOR myworkload
     IN myqcd
     AS myppirecs;

Now you can examine the recommended partitioning expressions from this partition analysis using the following SELECT request:

     SELECT tablename, expressiontext
     FROM qcd.partitionrecommendations
     WHERE resultnametag = 'myppirecs';

Example: Partition Analysis on Specified Tables

The following example considers partitioning expression recommendations for specific tables within a workload; therefore, an ON clause is specified:

     INITIATE PARTITION ANALYSIS
     ON tab1, tab2
     FOR myworkload 
     IN myqcd
     AS myppitabrecs;

Example: Partition Analysis with a Time Limit

The following example places a 5 minute time limit on the partitioning expression analysis:

     INITIATE PARTITION ANALYSIS
     FOR myworkload
     IN myqcd
     AS myfastanalysis
     TIME LIMIT = 5;