Teradata PPI: Understanding Partitioned Primary Index for Improved Performance

Introduction to the Teradata PPI

Solid-state disks (SSDs) have been replacing hard disks, yet mass storage access remains the slowest process for a database system. Database providers seek methods to decrease I/Os. Teradata offers I/O reduction functions, including column storage, block-level compression, and Teradata Intelligent Memory as of Release 14.10.

While these new features will enhance performance, we recommend a more established method to meet your improvement requirements equally.

What is a Partitioned Primary Index?

Frequently requested rows are grouped in partitions to limit data retrieval to only those containing the necessary data for your SQL query. Unrequested partitions are not accessed.

Netezza’s database system uses a technique called “Data Skipping” where information about the data blocks excluding the requested rows is stored.

Partitioning does not prevent table scans, but it minimizes the amount of data blocks that each AMP needs to move to its local memory (FSG cache) because only pertinent partitions must be accessed.

How is Teradata Partitioning implemented?

The Teradata Partitioned Primary Index (PPI) optimizes data organization in a table by segmenting records into distinct containers. This technique minimizes the number of containers required to store the entire table. Consequently, partitioning represents an alternative approach to storing data on disks.

In PPI tables, the primary index assigns responsibility for each row to an AMP. The method of storing rows in data blocks distinguishes them from non-partitioned tables. PPI tables first store rows within their designated partitions before sorting them by rowid.

When accessing a data record, Teradata queries the AMP’s cylinder index to determine the location of the first data block in the accessed partition. Once positioned at the first data block, all remaining partition data blocks (or subpartitions in multi-level partitioning) can be fetched into the FSG cache by scanning in a sequence with minimal disk IOs.

For nonpartitioned tables, current guidance recommends SUMMARY statistics rather than universally collecting PARTITION statistics. Relevant PARTITION statistics remain useful for partitioned-table demographics.

Accessing data based on partitioning attributes is known as partition elimination. The term may suggest exclusion, but it actually involves selecting partitions.

How does partitioning differ from indexing in Teradata?

Secondary and join indexes consume separate storage structures. A Primary Index is not a separate subtable; it controls distribution and access within the base table.

Although the impact on the occupied permanent disk space persists, it is not due to the redundant storage of columns. Rather, it results from the overhead of storing records in a partitioned manner. PPI table rows are wider by 2 to 8 bytes compared to their non-partitioned counterparts, as they store the internal partition number of each row.

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.

Email address I agree to receive the free book, Teradata updates and occasional promotions from DWHPro. I can unsubscribe at any time.

Email me the free book

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

About the free book

Partitioning by date is a common use for a PPI. Reports commonly include a date dimension in their filters. For instance, a telecommunication company’s call detail table stores extensive call information within a specific timeframe. This large table serves as the primary source for analytics, and query results may not require joins with other tables.

Consider a call detail table with a year’s worth of data, but you only require one day of information for your report. By partitioning the table by date, each AMP can limit access to that particular date. Hence, Teradata will only need to transfer a fraction (1/365) of the rows from the disks to the AMP’s memory (FSG cache).

If there is a need to access data records within specific ranges, partitioning should be prioritized over other index types. This is especially true for workloads that involve aggregations, such as summing up the account balances of a business segment, which are commonly found in reporting needs.

In contrast, particular indexing methods, such as primary index, unique secondary index, and join index with matching primary index definition, are typically appropriate for OLTP applications when seeking direct access to data blocks, such as retrieving a customer’s name for a specific client ID.

Merging partitioned tables with additional index types enhances usability to a greater extent.

Before Teradata Release 12, partitioning a table was subject to various restrictions, particularly concerning data types. It was not possible to partition tables based on character columns or timestamps. However, it was feasible to find a workaround for character columns.

-- Historical hashing illustration: supply the actual character column.
PARTITION BY RANGE_N((HASHBUCKET(HASHROW(YourColumn))) MOD 5 BETWEEN 0 AND 4 EACH 1)

Teradata Release 13.10 enables direct partitioning of character and timestamp columns.

The partitioning statement for timestamps can utilize the system variable CURRENT_DATE.

PARTITION BY RANGE_N(CALENDAR_DT BETWEEN CURRENT_DATE-365 AND CURRENT_DATE EACH INTERVAL '1' DAY );

CURRENT_DATE partition boundaries do not roll forward automatically. An explicit supported maintenance operation is needed; review the treatment of rows leaving the range before using TO CURRENT.

-- Delete the rows not matching any partition
ALTER TABLE <TABLE> TO CURRENT WITH DELETE;

-- Insert the rows not matching into a backup table
ALTER TABLE <TABLE> TO CURRENT WITH INSERT INTO <BACKUP_TABLE>;

The software supports three types of partitioning: basic column partitioning, the CASE_N syntax, and the RANGE_N syntax.

RANGE_N creates row ranges for partitioning, while CASE_N allows for CASE…WHEN statements.

Here is one example of an often-used RANGE_N partitioning by date:

CREATE TABLE <TABLE>
(
PK INTEGER, MyDate DATE)
PRIMARY INDEX (MyDate)
PARTITION BY RANGE_N (MyDate BETWEEN DATE '2014-01-01' AND DATE '2020-12-31' EACH INTERVAL '1' DAY, NO RANGE, UNKNOWN);

The above example illustrates two separate partitions that can be added to every table.

NO RANGE handles non-NULL values outside the defined ranges; UNKNOWN handles NULL/unknown partition results. Inserting a row without an applicable partition can fail. The audited RANGE_N fixture placed out-of-range and NULL rows into separate partitions.

The UNKNOWN partition contains rows that have NULL values in the partition expression.

One example of partitioning using CASE_N is as follows:

CREATE TABLE YourTable
(PK INTEGER, MyValue INTEGER)
PRIMARY INDEX (PK)
PARTITION BY CASE_N(MyValue < 1000, MyValue < 2000,
 MyValue < 3000, NO CASE, UNKNOWN);

We can add the necessary partitions.

Use CASE_N partitioning to group values into partitions.

Partitioning can occur at multiple levels, such as with multi-level partitioning for tables. An example of this is:

CREATE TABLE YourTable
(PK INTEGER, MyValue INTEGER, MyDate DATE)
PRIMARY INDEX (PK)
PARTITION BY (CASE_N(MyValue < 1000, MyValue < 2000,
 MyValue < 3000, NO CASE, UNKNOWN),
RANGE_N(MyDate BETWEEN DATE '2014-01-01' AND DATE '2020-12-31'
 EACH INTERVAL '1' DAY, NO RANGE, UNKNOWN));

Partition-count limits and partition-number storage depend on release and partition definition. Include NO RANGE/UNKNOWN or NO CASE/UNKNOWN partitions when calculating multilevel combinations, and check current documented limits rather than treating historical maxima as universal.

At what point does Teradata conduct partition elimination?

Numerous misconceptions surround partition elimination and its timing.

To clarify, it is important to differentiate between partition elimination and primary index access. Partition elimination can occur in a query without involvement from the Primary Index.

The selection of the primary index significantly influences partition decisions. However, the elimination of partitions results in a significant reduction in IO.

The potential performance patterns for accessing a partitioned primary index depend on the referencing of the PI and partitions.

Multi-level partitioning in Teradata offers the benefit of addressing partitions independently without requiring the inclusion of all partitions in the WHERE condition to cut partitions.

Let’s discuss the limitations of selecting a primary index for partitioning.

We have likely heard about the performance disparity between tables that include all partition columns in the primary index and those that do not. Furthermore, tables without partitioning columns cannot feature a unique primary index. What is the reason for this discrepancy?

To comprehend the correlation between the uniqueness of the primary index and partitioning, we must revisit how data rows are stored on disks.

Initially, the rows undergo hashing to determine the AMPs responsible for them. Subsequently, they are placed into their corresponding partitions and sorted based on the ROWID.

Consider a table that has a primary index that is not unique. As a result, multiple rows may share the same primary index and hash to the same AMP. Despite this, each row may belong to a distinct partition.

For a non-partitioned table, rows with the same hash are stored in proximity, whereas a partitioned table spreads out the rows among various partitions.

Imagine if a partitioned table could have a unique primary index without including the partition column in the index. In this scenario, any update or insert statement would require Teradata to search each partition to ensure duplicates are not created. However, this process is inefficient and negatively impacts performance.

Including the partitioning columns in the primary index can prevent the need for scanning all partitions every time primary index access is required. Failure to do so will result in the responsible AMP scanning all partitions for the primary index. While this issue is minor, it is primarily relevant to OLTP.

Table partitioning can affect performance in various ways. When joining a partitioned table with a non-partitioned one, Teradata employs distinct join techniques for non-partitioned tables. Partitioning can involve a Sliding Window merge join. Another possibility is to “de-partition” the partitioned table and perform a regular merge join, or we can partition the non-partitioned table.

To optimize performance, it’s ideal for non-partitioned tables to have matching primary indexes when joined. The join is executed on the primary index and partition columns with other less efficient methods.

Therefore, although eliminating partitions can greatly decrease disk input/output operations, monitoring its influence on join performance is imperative.

Range maintenance can remove whole partitions efficiently, but it changes the table definition and may remove data. Use the complete ALTER TABLE syntax and an explicit retention decision; this is not a general no-logging guarantee.

-- Illustrative maintenance syntax; match the existing partition expression.
ALTER TABLE YourDatabase.YourTable
MODIFY PRIMARY INDEX
DROP RANGE BETWEEN DATE '2014-01-01' AND DATE '2014-06-30'
EACH INTERVAL '1' DAY WITH DELETE;

Below are the recommended statistics “best practices” for partitioned tables:

• Table PI
• Partitioning column(s)
• Columns in any not partitioned table that are equated to the partitioning column of the partitioned table
• The system-derived PARTITION dummy column of all partitioned tables

I believe that partition elimination is a crucial tool for optimizing performance.

More on indexing & partitioning here:
Teradata Indexing
The Secondary Index in Teradata – The NUSI compendium

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

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.

Email address I agree to receive the free book, Teradata updates and occasional promotions from DWHPro. I can unsubscribe at any time.

Email me the free book

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

About the free book