DWHPro Insights

Maximizing Performance with Teradata Partitioning Strategies

By Roland Wenzlofsky ·

Maximizing Performance with Teradata Partitioning Strategies

Teradata Partitioning and Performance

This article explores various strategies for utilizing Teradata Partitioning to optimize query efficiency. The primary objective of these strategies is to minimize resource utilization. Answering the following questions will aid in determining the optimal approach for implementing row-level partitioning.

Considerations to be made before Partitioning a Table

How big is the Table?

Huge tables are suitable for row-level partitioning. For the Optimizer, selecting individual partitions on small tables is not worthwhile, and a full table scan is often just as efficient.

Nevertheless, partitioning a small table identically to a large table can improve performance for joins.

How often is a Column used in WHERE Conditions?

A column is more suitable as a partition column if WHERE conditions are frequently applied to this column, resulting in only a fraction of the table rows being read.

The opposite statement is also true:
If there is no column after which a WHERE condition is frequently restricted or the selectivity of the WHERE conditions is low, it makes no sense to use Teradata Partitioning.

Should the Partition Column be part of the Primary Index?

Include partition columns in the PI only when the access, join, distribution and loading trade-offs justify it. More partitions can aid elimination but add overhead; they do not universally improve or worsen performance. teradata ppi

Does it make any difference how many Partitions we define?

Partition count trades elimination opportunities against partition probing, join preparation and maintenance. More partitions do not universally worsen performance. Match grain to access patterns and verify the complete plan.

Can Teradata handle all Types of Partitioning equally well, i.e., does the Optimizer recognize Partition Elimination Opportunities?

Although the Optimizer gets better at it, simple partition expressions are preferable—the more complex a partition expression, the greater the risk that the Optimizer overlooks partition elimination opportunities.

Should all Teradata Partitions always contain approximately the same number of Rows?

Include partition columns in the PI only when the access, join, distribution and loading trade-offs justify it. More partitions can aid elimination but add overhead; they do not universally improve or worsen performance.

Teradata Partitioning Strategies

  • Each partition has about the same number of rows
  • Some Partitions have a small number of rows, while other partitions have a large number of rows:
    For example, it makes sense to partition the part of the requested data more often (more partitions with fewer rows).
  • All partitions have the same number of different values, but the number of rows per partition can differ.

Please evaluate when to utilize the appropriate Teradata Partitioning strategy. Feel free to share your thoughts in the comments section, which can benefit other readers.

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

The corrected SalesId INTEGER definition, insert, SELECT and EXPLAIN executed. The 2019-ending partition range is a historical example and must be maintained for newer data, with explicit out-of-range handling.

For the composite-PI example: CREATE TABLE Sales (SalesId INTEGER NOT NULL, SalesDate DATE NOT NULL, Price DECIMAL(18,2) NOT NULL) PRIMARY INDEX (SalesId, SalesDate) PARTITION BY RANGE_N (SalesDate BETWEEN DATE ‘2019-01-01’ AND DATE ‘2019-12-31’ EACH INTERVAL ‘1’ DAY); A predicate only on SalesId cannot specify that complete PI; this illustrates a trade-off, not a recommendation to add SalesDate universally.

Need help with query performance or platform costs?

Diagnose plans, skew, spool pressure and expensive workloads. Agree a baseline and acceptance criteria before making changes.

Explore Teradata performance tuning

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Follow DWHPro in Google to see our articles more often in Search.

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Protected by Security by CleanTalk and CleanTalk Anti-Spam