INITIATE PARTITION ANALYSIS - Teradata Database

SQL Data Manipulation Language

Product
Teradata Database
Release Number
15.00
Language
English (United States)
Last Update
2018-09-28
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata® Database

INITIATE PARTITION ANALYSIS

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.

Syntax  

where:

 

Syntax element …

Specifies …

ON table_name

ON database_name.table_name

ON user_name.table_name

a 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 PPI 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.

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 (see SQL Request and Transaction Processing for the definitions of PartitionRecommendations and its columns). 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  

    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  

    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  

    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;

    For More Information

    For more information about QCDs, partitioning expression analysis, and partitioned tables and join indexes, see:

  • “CREATE TABLE (Index Definition Clause)” in SQL Data Manipulation Language
  • “INITIATE INDEX ANALYSIS” on page 579
  • SQL Request and Transaction Processing
  • Database Design
  • Teradata Index Wizard User Guide