Chapter 2
The Primary Index
“Civilized society is perpetually menaced with disintegration through this primary hostility of men towards one another.”
Table of Contents Chapter 2 – Primary Index
– The Primary Index is defined when the table is CREATED
– A Unique Primary Index (UPI)
– Primary Index in the WHERE Clause – Single-AMP Retrieve
– A Non-Unique Primary Index (NUPI)
– Primary Index in the WHERE Clause – Single-AMP Retrieve
– A conceptual example of a Multi-Column Primary Index
– Primary Index in the WHERE Clause – Single-AMP Retrieve
– A conceptual example of a Table with NO PRIMARY INDEX
– A Full Table Scan is likely on a table with NO Primary Index
– Table CREATE Examples with four different Primary Indexes
– What happens when you forget the Primary Index?
– Why create a table with No Primary Index (NoPI)?
– Watch the Video on the Primary Index
The Primary Index is defined when the table is CREATED
The Primary Index is defined when the table is CREATED. Above we have an UPI which stands for a Unique Primary Index (UPI).
A Unique Primary Index (UPI)
CREATE TABLE Emp_Intl
(Emp_No INTEGER,
Dept_No SMALLINT,
First_Name VARCHAR(12),
Last_Name CHAR(20),
Salary 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)
CREATE TABLE Emp_Intl
(Emp_No INTEGER,
Dept_No SMALLINT,
First_Name VARCHAR(12),
Last_Name CHAR(20),
Salary DECIMAL(10,2))
Primary Index( Dept_No)
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.
Table CREATE Examples with four different Primary Indexes
CREATE TABLE Emp_Intl
(Emp_No INTEGER,
Dept_No SMALLINT,
First_Name VARCHAR(12),
Last_Name CHAR(20),
Salary DECIMAL(10,2))
UNIQUE PRIMARY INDEX ( Emp_No );
UPI
CREATE TABLE Emp_Intl
(Emp_No INTEGER,
Dept_No SMALLINT,
First_Name VARCHAR(12),
Last_Name CHAR(20),
Salary DECIMAL(10,2))
PRIMARY INDEX ( Dept_No );
NUPI
CREATE TABLE Emp_Intl
(Emp_No INTEGER,
Dept_No SMALLINT,
First_Name VARCHAR(12),
Last_Name CHAR(20),
Salary DECIMAL(10,2))
PRIMARY INDEX ( First_Name , Last_Name );
Multi-Column NUPI
CREATE TABLE Emp_Intl
(Emp_No INTEGER,
Dept_No SMALLINT,
First_Name VARCHAR(12),
Last_Name CHAR(20),
Salary 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 can be!
Watch the Video on the Primary Index
Tera-Tom Trivia
Tom Coffing was a two-time All-American wrestler for the University of Arizona. In 1979 Tom was “Sophomore Athlete of the Year” for the University of Arizona. That year Tom placed 3 rd in the NCAA (Division 1) and is pictured above at the awards ceremony in Ames Iowa.
Click on the link below or place it in your browser and watch the video on the Primary Index.
http://www.coffingdw.com/TbasicsV12/PrimaryIndexUPI_NUPI.wmv