What are the best techniques for partitioning and distributing data in Teradata?

2 How to partition data in Teradata?

Teradata offers two types of partitioning: primary index partitioning and column partitioning. Primary index partitioning splits a table based on the values of its primary index column or columns, which are also used for distribution. Column partitioning splits a table based on the values of one or more non-primary index columns, which are not used for distribution. You can combine both types of partitioning to create a multi-level partitioned primary index (MLPPI) table. When choosing a partitioning method, there are several factors to consider, such as the size and cardinality of the table, the frequency and type of queries, the join and aggregation operations, and the data loading and maintenance requirements. Generally, it is best to partition large and frequently queried tables on columns that are used for filtering, grouping, or ordering; however, it is important to avoid partitioning on columns with low cardinality, high skew, or high update activity.

3 How to distribute data in Teradata?

When selecting a primary index for a Teradata table, several factors should be taken into account. These include the uniqueness and cardinality of the column or columns, the distribution and skew of the column or columns, the join and access patterns of the table, and the data loading and modification patterns of the table. Generally, you should aim to choose a primary index that ensures a uniform and balanced distribution of data across the amps, minimizes data redistribution and duplication during queries, and supports the most common and critical queries on the table. Additionally, it is important to avoid selecting a primary index that has high skew, low cardinality, or frequent changes.

4 How to optimize queries in Teradata?

To optimize your queries in Teradata, you must understand how the optimizer works and how it chooses the best execution plan based on the statistics, indexes, and partitions of your tables. Additionally, following best practices for writing and tuning your queries is essential. These include using appropriate data types and formats for columns, collecting and maintaining accurate statistics for tables and columns, using primary index access whenever possible to avoid full table scans, leveraging secondary indexes, join indexes, and aggregate indexes to enhance query performance and reduce data redistribution, utilizing partition elimination and partitioned join to take advantage of partitioning and prevent unnecessary data movement, applying compression and encoding techniques to reduce data size and improve I/O efficiency, as well as using explain and explain analyze to analyze and compare query plans and costs.

5 How to monitor and troubleshoot performance issues in Teradata?

In order to effectively monitor and troubleshoot performance issues in Teradata, you need to use the tools and utilities provided by Teradata. For example, Viewpoint is a web-based portal that enables you to monitor and manage your Teradata system, sessions, queries, workloads, alerts, and reports. DBQL is a database query logging feature that records detailed information about the queries executed on your Teradata system. PMON is a performance monitor utility that displays current status and activity of your Teradata system, amps, sessions, and queries. TSET is a Teradata system emulator tool that allows you to simulate and test the performance of your queries and workloads on different configurations and settings. Lastly, TDSTATS is a Teradata statistics manager utility that automates and schedules the collection and maintenance of statistics for tables and columns.

6 Here’s what else to consider

This is a space to share examples, stories, or insights that don’t fit into any of the previous sections. What else would you like to add?