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 our 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.

Teradata considers all tables partitioned, which is a notable technical detail. NPPI tables are essentially PPI tables with a single partition, specifically partition number zero, that holds all table rows. Collecting statistics on the dummy column PARTITION is recommended, even for tables without partitions. This enables the optimizer to estimate the table cardinality of NPPI tables.

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?

Teradata indexes differ from other types in that they require permanent space allocation. Essentially, each Teradata index functions as a sub-table that contains a redundant subset of the columns found in its corresponding 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.

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.

PARTITION BY RANGE_N(
(HASHBUCKET(HASHROW( ))) 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 );

Utilizing CURRENT_DATE in your partition statement simplifies the process of increasing partitions up to the current date.

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

Rows outside of the defined partitions will be directed to the NO RANGE category. If you attempt to insert records that do not match a specified partition and have not designated an UNKNOWN partition, the insertion will fail, and an error message will appear.

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

Note that certain partitions have limitations. Prior to Teradata version 13.10, the greatest quantity of partitions achievable was 65535. It is essential to acknowledge that this figure encompasses all partitions in multi-level partitioning and includes both NO RANGE and UNKNOWN partitions. One must multiply the number of partitions on each level to obtain the total number of partitions. After Teradata 14.10, however, having over 9.000 quintillion partitions became feasible.

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.

Partitioned tables offer additional benefits, such as easily removing entire partitions from the table using a single statement without impacting the transaction log.

MODIFY DROP RANGE BETWEEN DATE ‘2014-01-01’ AND DATE ‘2013-06-30’ EACH INTERVAL ‘1’ DAY;

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