DWHPro Insights

Understanding Teradata NOPI Tables: Essential Facts and Performance Benefits

By Roland Wenzlofsky ·

Understanding Teradata NOPI Tables: Essential Facts and Performance Benefits

The Essential Facts About NOPI Tables in Teradata

What are Teradata NOPI tables?

NoPI row placement is determined by the loading method and table map rather than a table primary-index hash. Neither NoPI nor PI distribution is guaranteed perfectly even. They are not intended to replace primary index tables but are used in certain situations.

How are the rows of NOPI tables distributed across the AMPs?

Distribution depends on loading and the table map and is not guaranteed perfectly even. Without an applicable index, rows can still be found by scanning.

How are the NOPI Tables created?

This is the syntax:
CREATE TABLE Customer
(
PK BIGINT NOT NULL
) NO PRIMARY INDEX;

Teradata creates a unique secondary index to ensure uniqueness if a primary key or a UNIQUE constraint is defined when creating the NOPI table.

How can Teradata mark the rows of a NOPI table if no hashing is used?

NoPI rows have internal row identifiers without a table primary-index hash. Scanning is one access path; supported indexes, partition elimination and maps can narrow work. Physical ROWID representation depends on the table format and release and is not an instruction to construct row identifiers manually.

How does Teradata determine which AMP gets which NOPI row?

A random generator is used. This is available on the AMPs as well as on the parsing engines.

The AMP random generator is used for fast load, and the random generator is on the parsing engine for SQL requests.

During fast load, the random generator of the AMPs distributes the rows in the round-robin procedure.

For SQL requests, the DBQL QueryId of the request is used to generate a hash value which is used to distribute the rows.

What happens if I skip defining the Primary Index in the DDL statement of a CREATE TABLE?

Suppose you create a table and forget the primary index:

CREATE TABLE Customer
(
PK BIGINT NOT NULL
) ;

Which type of table is created depends on a system setting. A Primary Index Table is made if the settings set the DBC Control Field to “P” or “D”.

Default index selection also depends on table attributes, constraints and release. Specify the intended PRIMARY INDEX or NO PRIMARY INDEX explicitly rather than relying on an implicit first-column rule.

If the DBC Control Field “PrimaryIndexDefault” is set to “N”, a NOPI table is created.

How does the use of a NOPI table affect the bulk load performance?

NoPI loading can avoid some PI redistribution and sorting costs; actual performance is workload and load-method dependent.
Fastload for PI tables is done in several stages.
Incoming rows (sent in blocks from the client) are first randomly distributed over the AMPs (round-robin). Afterward, the rowhash is calculated over the primary index columns, and the rows are sent to the right AMPs. On the target AMPs, the rows are collected, sorted, and written in data blocks to the disk.

For NOPI tables, the rows are received by random AMPs, but there is no need to redistribute and sort them. Furthermore, the blocks transferred from the client to Teradata are larger than those for PI tables.

Block loading can distribute work efficiently, but actual skew depends on load volume, placement and map; measure it rather than assuming balance.

A further performance advantage results from the fact that new rows are appended at the end of the table/data blocks (the rows do not have to be sorted in ascending order by rowhash within the data blocks)

For an “INSERT…SELECT” statement of a considerable number of rows: Compare measured I/O for the actual source and target definitions; there is no universal reduction.

How do NOPI tables affect query performance?

NoPI removes direct primary-index hash lookup. It does not force every query to scan every base-table row: supported secondary/join indexes, row or column partition elimination and table-map scope can narrow access. Inspect the actual table definition and EXPLAIN plan. Related: teradata NOPI

Can NOPI tables never be skewed?

NOPI tables can be skewed in specific cases.

Small block-loaded tables may be skewed because placement happens in blocks; block sizes and behavior depend on utility and release. Teradata may send all rows to the same AMP and one data block.

AMP-local copying of the rows will be applied when executing an “INSERT…SELECT” statement from a primary index table into a NOPI table. All rows stay on their current AMP, and if the primary index table is skewed, the NOPI table will be skewed.

HASH BY RANDOM can improve distribution, but randomness does not guarantee perfect balance. Both shown forms executed in the audit; assess actual AMP distribution and the table map.

INSERT INTO Customer SELECT * FROM Customer_2020
HASH BY RANDOM;

If you want to distribute on row level to achieve even better randomness, you can use this statement:

INSERT INTO Customer SELECT * FROM Customer_2020
HASH BY RANDOM (1,100000000);

Furthermore, skewing of a NOPI table can happen by executing an archive job on one system and restoring the backup on a different system.

teradata NOPIteradata NOPI

What are the use cases for NOPI tables?

NOPI tables are used as stage tables. This makes sense, e.g., if the source files are loaded 1:1 with fast load and a surrogate key is added to each row. Then a full table scan is required in any case, and a PI table would have no performance advantage.

A NOPI can also be helpful if we can find no non-skewed suitable primary index or for quick data testing without worrying about the primary index’s correct choice.

How can I find all the NOPI tables?

The Teradata tablekind for NOPI tables is ‘O’. With the following SQL statement, we can find all NOPI tables in our system:

SELECT * FROM DBC.TABLESV WHERE TABLEKIND = ‘O’;

Why can I read a NOPI table during loading?

When a NoPI table is loaded with FastLoad, users can read the table using an access lock. Dirty read access is allowed as rows are always appended to the end of a NoPI table. For tables with Primary Index, the redistribution and sorting of the rows prevent access during loading.

If I do not specify a primary index in the DDL statement, does Teradata create a NOPI table?

Depending on the system settings, if you don’t define a Primary Index, Teradata might make the first column a NUPI. If you want a NoPI table, you should explicitly state NO PRIMARY INDEX.

Historical NoPI restrictions: check the installed release

  • No SET tables allowed
  • No Identity columns allowed
  • Row-level partitioning is supported for appropriate NoPI table definitions; check the release-specific restrictions.
  • UPDATE is supported: it changed both fixture rows on Teradata Database 20.00.28.81. Check MERGE and other operation restrictions separately for the installed release.
  • No Permanent Journaling is possible
  • It can’t be defined as Queue Tables
  • No UPDATE triggers allowed
  • No Hash Index allowed
  • No Multiload, as its load algorithm depends on the PI

NOPI in Teradata – What is allowed?

  • Fallback protection
  • Secondary Indexes (USI and NUSI)
  • Join Indexes
  • CHECK and UNIQUE constraints
  • Triggers
  • COLLECT STATISTICS
  • Global Temporary Tables
  • Volatile Tables
  • CLOB and BLOB Data Types
  • Fastload but duplicates are not filtered

Teradata NOPI – Loading Considerations

  • INSERT INTO Customer VALUES (100);

    The parsing engine random generator selects a random AMP and appends the row to the end of the Teradata NOPI table.
  • INSERT INTO Customer SELECT * FROM Customer_2020;

    If Customer_2020 is a PI table, the rows will not be redistributed but stay on the same AMP. Skewing is possible!
  • FastLoad distribution depends on blocks, load configuration and the table map; it is not guaranteed perfectly even.

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.

8 thoughts on “Understanding Teradata NOPI Tables: Essential Facts and Performance Benefits”

  1. Very nice article! This background knowledge is exactly what I have been looking for in order to understand how to optimize our TD loads. The Teradata documentation itself is certainly comprehensive but also a bit overwhelming, so blog posts like these are very valuable.

    Reply
  2. Please explain the below statement in detail

    skewing can happen, by executing an archive job on a source system, and a restore of the backup on a different target system.

    Reply
  3. Hi Murali. Sure I can give you one example from real life. I am using NOPI tables in the following scenario: We are staging data into Teradata and adding a surrogate key. In the next step, the tables are exported again and the transformation takes place in an ETL-Tool. The staging is done with fastloads. As the adding of a surrogate key column is touching anyway each table row, there is no point in using PI tables (it requires always a full table scan). Therefore the staging tables are NOPI, which reduces the resource usage of the fastloads (no sort step required).

    Reply
    • This may come as naive. But Roland, can you please explain how surrogate key results in full table scan.
      I think a surrogate key is introduced to apply uniqueness which should not result in FTS.

      I could not understand this.
      “As the adding of a surrogate key column is touching anyway each table row, there is no point in using PI tables (it requires always a full table scan)”

      Reply
      • Hi Niraj,

        I think this is a misunderstanding. What I wanted to say is that the creation of the surrogate keys touches each table row when inserting the surrogate keys based on the natural key of the table.

        Reply
  4. Thanks for covering no PI tables. Can you provide one example for using no PI table compared to the PI table?

    Reply

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