Chapter 3 – The Primary Index is the Axis of all Teradata Systems
“If I have seen farther than others, it is because I was standing on the shoulders of giants.”
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)
CREATE TABLE Emp_Intl
| (Emp_No Dept_No First_Name Last_Name Salary |
INTEGER, SMALLINT, VARCHAR(12), CHAR(20), DECIMAL(10,2)) |
UNIQUE Primary Index ( Emp_No)
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.
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.
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
A Full Table Scan was performed
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.
Table CREATE Examples with four different Primary Indexes
CREATE TABLE Emp_Int1
| (Emp_No Dept_No First_Name Last_Name Salary |
INTEGER, SMALLINT, VARCHAR(12), CHAR(20), DECIMAL(10,2)) |
UNIQUE PRIMARY INDEX ( Emp_No );
UPI
CREATE TABLE Emp_Int1
| (Emp_No Dept_No First_Name Last_Name Salary |
INTEGER, SMALLINT, VARCHAR(12), CHAR(20), DECIMAL(10,2)) |
PRIMARY INDEX ( Emp_No );
NUPI
CREATE TABLE Emp_Int1
| (Emp_No Dept_No First_Name Last_Name Salary |
INTEGER, SMALLINT, VARCHAR(12), CHAR(20), DECIMAL(10,2)) |
PRIMARY INDEX ( First_Name , Last_Name );
Multi-Column NUPI
CREATE TABLE Emp_Int1
| (Emp_No Dept_No First_Name Last_Name Salary |
INTEGER, SMALLINT, VARCHAR(12), CHAR(20), DECIMAL(10,2)) |
No Primary Index
No Primary Index
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 it can be!