Chapter 2 – The Primary Index

“He who walks in another’s tracks leaves no footprints.”

- Joan L. Brannon

The Primary Index is defined when the table is CREATED

The Primary Index is defined when the table is CREATED. Above, we have a UPI which stands for a Unique Primary Index (UPI).

A Unique Primary Index (UPI)

A Unique Primary Index (UPI) spreads the rows of a table evenly across the AMPs.

Primary Index in the WHERE Clause - Single-AMP Retrieve

Use the Primary Index column in your SQL WHERE clause and only 1-AMP retrieves.

Using EXPLAIN

If you want to see the Parsing Engine's plan, all you have to do is type EXPLAIN in front of your SQL. You can also hit the Function Key F6, or click on the magnifying glass on Nexus. Above, you can see that we are doing a single-AMP RETRIEVE step by way of a Unique Primary Index (UPI).

A Non-Unique Primary Index (NUPI)

A Non-Unique Primary Index (NUPI) will have duplicates grouped together on the same AMP, so data will always be skewed (uneven). The above skew is reasonable.

Primary Index in the WHERE Clause - Single-AMP Retrieve

Use the Primary Index column in your SQL WHERE clause and only 1-AMP retrieves.

Using EXPLAIN in a NUPI Query

If you want to see the Parsing Engine's plan, all you have to do is type EXPLAIN in front of your SQL. You can also hit the Function Key F6 or click on the magnifying glass on Nexus. Above, you can see that we are doing a single-AMP RETRIEVE step by way of a Primary Index (NUPI).

Service Level Goals and Service Level Agreements

A conceptual example of a Multi-Column Primary Index

A table can have only one Primary Index, but you can combine up to 64 columns together max to form one Multi-Column Primary Index.

Primary Index in the WHERE Clause - Single-AMP Retrieve

Use the Primary Index column in your SQL WHERE clause, and only 1-AMP retrieves.

A conceptual example of a Table with NO PRIMARY INDEX

A Table that specifically states NO PRIMARY INDEX will receive no primary index. It will distribute the data evenly but randomly, and this is often used as a staging table.

A Full Table Scan is likely on a table with NO Primary Index

Since a NO Primary Index (NoPI) table has no primary index, the system retrieves by performing a Full Table Scan which means All-AMPs read All-Rows they own once.

An EXPLAIN that shows a Full Table Scan

If you want to see the Parsing Engine's plan, all you have to do is type EXPLAIN in front of your SQL. You can also hit the Function Key F6 or click on the magnifying glass on Nexus. Above, you can see that we are doing an all-AMPs RETRIEVE step from Emp_Intl by way of an all-rows scan. This means a Full Table Scan.

Table CREATE Examples with four different Primary Indexes

A table can have only one Primary Index so picking the right one is essential. Above, are four different examples for your consideration.

What happens when you forget the Primary Index?

When you forget to define the Primary Index, Teradata will default to the first column in the table, and it will be defined as Non-Unique. Clearly define what you want!

Why create a table with No Primary Index (NoPI)?

NoPI tables are designed to be staging tables. Data from a Mainframe or server can be loaded onto Teradata quickly with perfect distribution. Then, an INSERT/ SELECT can be done to move the data from the staging table (on Teradata) to the production table (also on Teradata). The data can be transformed in staging, and there are no Load Restrictions with an INSERT/ SELECT. A NoPI table usually isn’t queried, but can be!