Chapter 6
Secondary Indexes
“Hey, every once in awhile the secondary form works better than the original but it's certainly a rarity.”
Table of Contents Chapter 6 – Secondary Indexes
– Review of a Unique Primary Index (UPI)
– Primary Index in the WHERE Clause – Single-AMP Retrieve
– Review of a Non-Unique Primary Index (NUPI)
– Primary Index in the WHERE Clause – Single-AMP Retrieve
– Creating a Unique Secondary Index (USI)
– What is in a Unique Secondary Index (USI) Subtable?
– A Unique Secondary Index (USI) Subtable is Hashed
– How the Parsing Engine uses the USI Subtable?
– An USI is a Two-AMP Operation
– Review of a Non-Unique Primary Index (NUPI)
– Creating a Non-Unique Secondary Index (NUSI)
– What is in a Unique Secondary Index (USI) Subtable?
– Non-Unique Secondary Index (NUSI) Subtable is AMP Local
– How the Parsing Engine uses the NUSI Subtable?
– Creating a Value-Ordered NUSI
– Watch the Videos on Secondary Indexes
Review of 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.
Review of 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( Last_Name)
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 and ok.
Primary Index in the WHERE Clause – Single-AMP Retrieve
Use the Primary Index column in your SQL WHERE clause and only 1-AMP retrieves.
Creating a Unique Secondary Index (USI)
When a Secondary Index is created a subtable is created on each AMP.
What is in a Unique Secondary Index (USI) Subtable?
The USI Subtable only contains two columns:
1. Emp_No (The USI column)
2. Row-ID of the real Primary Index of the base table
Inside the secondary index subtable is Emp_No column (USI Column). Then there is the corresponding Row-ID of the real Primary Index of the table.
A Unique Secondary Index (USI) Subtable is Hashed
The USI Subtable spreads the rows evenly among the AMPs. The Primary Index of the Subtable is Emp_No (The USI column).
Subtable rows are hashed by their Primary Index (Emp_No) and distributed evenly.
How the Parsing Engine uses the USI Subtable?
Parsing Engines Plan – A Two-AMP Operation
Emp_No is a Unique Secondary Index!
Hash 1004 and see which AMP holds the row in its subtable? (AMP 3)
Contact AMP 3 and have it retrieve row 1004.
Find the real Row-ID of the base table row? (4,1)
Use the Row-ID to find the base table row with a single-AMP retrieve.
An USI is a Two-AMP Operation
The first AMP is assigned to read the subtable and the second the base table.
Review of 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( Last_Name)
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 and ok.
Creating a Non-Unique Secondary Index (NUSI)
When a Secondary Index is created a subtable is created on each AMP.
What is in a Unique Secondary Index (USI) Subtable?
The NUSI Subtable only contains two columns:
1. First_Name (The NUSI column)
2. Row-ID of the real Primary Index of the base table
Inside the secondary index subtable is Last_Name column (NUSI Column). Then there is the corresponding Row-ID of the real Primary Index of the table.
Non-Unique Secondary Index (NUSI) Subtable is AMP Local
The NUSI Subtable is AMP local because all values in the subtable are of those in the base table (on the same AMP).
Subtable rows match those of the base rows on the same AMP.
How the Parsing Engine uses the NUSI Subtable?
Parsing Engines Plan – An ALL-AMP Operation
First_Name is a Non-Unique Secondary Index!
Have each AMP check if they have a ‘Kyle’ in their NUSI Subtable.
If an AMP has a ‘Kyle’ have them retrieve the base row (AMP Local)
Creating a Value-Ordered NUSI
The Secondary Index Subtable will be sorted by the Values and not in Hash Order.
Watch the Videos on Secondary Indexes
Tera-Tom Trivia
Tom Coffing spent years as an actor and has done television commercials, radio shows and standup-comedy. Tom won two orchid awards as a lead actor in Cincinnati's historic Mariemont theatre. Tom spent years in Toastmasters and years performing as an actor to better his communication and teaching skills.
Click on the link(s) below or place it in your browser and watch the video on secondary indexes.