The syntax for the CREATE Table statement has been changed to permit user data tables to be created without a primary index. Such tables are referred to as NoPI (No Primary Index) tables.
This feature provides a performance advantage when using FastLoad or Tpump Array Insert to load data into staging tables. Because Nopi staging tables have no row-ordering constraints, the system can always append rows to the end of a NoPI table. Rows in a NoPI table can also be stored on any AMP, which is advantageous for TPump Array INSERT operations because many rows can then be packed into a single AMP step, thus dramatically reducing the performance burden on both CPU and I/O. After a NoPI staging table has been populated, the table can be processed further using SQL DML statements such as DELETE, INSERT, and SELECT.
While using a NoPI table, you can:
• Manipulate rows directly using most SQL DML statements or you can move its rows into a primary-indexed target table using INSERT… SELECT, MERGE, or UPDATE…FROM SQLs.
• Create unique secondary indexes to avoid full-table scans during row access. For example, while single-AMP retrieval of NoPI rows by means of their primary index is not possible, you can work around this through the appropriate assignment of unique secondary indexes (USIs) to NoPI tables, and by careful construction of request conditions to specify those USIs to access individual NoPI table rows.
• Create nonunique secondary indexes (NUSIs) to facilitate set processing retrieval of rows from NoPI tables.
• Avoid full-table scans when deleting a set of rows from a NoPI table by assigning secondary indexes and specifying them in your request conditions.
Benefits.
• Enhanced performance for FastLoad bulk data loads into staging tables.• Enhanced performance for TPump Array INSERT minibatch loads into staging tables.
Points to note:
• The absence of a primary index or secondary index in NoPI tables means that all row access is done using full-table scans.
• The drawback to using secondary indexes for NoPI tables is that while they can enhance query processing significantly, they can also reduce load performance.
• You cannot modify a NoPI table using SQL UPDATE or UPSERT.
• This feature introduces a new DBS Control flag, PrimaryIndexDefault, that determines the behavior of a CREATE TABLE statement that does not explicitly specify any of the following:
• PRIMARY INDEX clause• NO PRIMARY INDEX clause
• PRIMARY KEY or UNIQUE constraints
