For DBAs who are fairly familiar with Oracle OLTP system, it's difficult to understand why many Teradata DBAs choose to have tables with no primary indexes (NoPI tables). The reason is performance.
To improve bulk data loading performance, Teradata DBAs create tables especially staging tables with NO PRIMARY INDEX. Because there is no primary index, table rows can be dispatched to any given AMP (Access Module Processor). In this way, Teradata system can load data into tables faster.
However, because of lacking primary keys, the following restrictions applies:
(Created 11/24/2015)
To improve bulk data loading performance, Teradata DBAs create tables especially staging tables with NO PRIMARY INDEX. Because there is no primary index, table rows can be dispatched to any given AMP (Access Module Processor). In this way, Teradata system can load data into tables faster.
However, because of lacking primary keys, the following restrictions applies:
- SQL UPDATE triggers cannot update a NoPI table.
- SQL UPDATE requests cannot update a NoPI target table.
- SQL MERGE requests cannot update or insert into a NoPI target table.
- SQL MERGE requests can update or insert into a primary-indexed target table from a NoPI source table.
(Created 11/24/2015)