Chapter 6 - Secondary Indexes
“Hey, every once in a while the secondary form works better than the original, but it's certainly a rarity.”
Teradata Primary and Secondary Index Cheat Sheet
A table has only one Primary Index, but it can have up to 32 Secondary Indexes.
There are two types of secondary indexes and they are Unique (USI) and Non-Unique (NUSI).
Use an USI in the SQL WHERE clause and it results in a 2 second two AMP retrieve.
Use a NUSI in the SQL WHERE clause and it is an all AMP retrieve, but not a full table scan.
| Unique Primary Index (UPI) | Non-Unique Primary Index (NUPI) |
| SELECT * FROM Employee_Table WHERE Employee_No = 12345; |
SELECT * FROM Order_Table WHERE Customer_No = 12345; |
| Single AMP retrieve (1 sec) | Single AMP retrieve (1 sec) |
| Unique Secondary Index (USI) | Non-Unique Secondary Index (NUSI) |
| SELECT * | SELECT * |
| FROM Employee_Table | FROM Employee_Table |
| WHERE | WHERE |
| Soc_Security_No = ‘274-23-1235’ ; | Last_Name = ‘Matson’ ; |
| Two AMP retrieve (2 sec) | All AMP retrieve (20 seconds) |
Teradata Query Cheat Sheet
Every table has one, and only one, Primary Index.
There are two types of Primary Indexes, and they are Unique (UPI) and Non-Unique (NUPI).
Use an UPI or a NUPI in the WHERE clause, and it results in a one second single AMP retrieve.
A table has only one Primary Index, but it can have up to 32 Secondary Indexes.
There are two types of secondary indexes, and they are Unique (USI) and Non-Unique (NUSI).
Use an USI in the SQL WHERE clause, and it results in a 2 second two AMP retrieve.
Use a NUSI in the SQL WHERE clause, and it is an all AMP retrieve, but quicker than a full table scan.
Some table are partitioned by Simple, Range_N, or Case_N partitioning.
Partitioned tables partition the rows by sorting them by the partitioning statement on each AMP.
A Partitioned table is called a Partition Primary Index (PPI) table.
Query a PPI table using the partition column in the WHERE clause, and it only queries that partition(s).
Partition by Month and query for January, and only one partition is read on each AMP.
A columnar table partitions the columns instead of the rows.
The rows of a columnar table still distribute the entire row to their AMPs randomly, but evenly.
The AMPs break up the columnar table rows they receive into individual column containers.
When users query just a few columns of a table, the columnar design is faster.
A Join Index can be setup to pre-join tables into physical combined answer sets.
When users query tables, the Parsing Engine will secretly use the Join Index answer sets instead.
There are single table join indexes, multi-table join indexes, and aggregate join indexes.
Teradata has temporal tables that are time based with ValidTime and TransactionTime.
Tables with both ValidTime and TransactionTime are called Bi-Temporal tables.
Data in a temporal table is not physically deleted, but logically deleted.
Temporal tables appear like normal table but use special SQL and you can see the old data.
Teradata (in V14.10) have in-memory tables that are kept inside memory.
In-memory tables are lightning fast to query and should be used when there is a need for extreme speed.
Creating a Unique Secondary Index (USI)
Whenever Emp_No is queried in the SQL Where clause, it results in a fast two AMP retrieve (2 sec).
Creating a Non-Unique Secondary Index (NUSI)
Whenever First_name is queried in the SQL Where clause, it will often be faster than a full table scan.
Creating a Value-Ordered NUSI
Whenever Dept_No is queried in the SQL with a BETWEEN statement, it might be faster than a full table scan.
NUSI BITMAP
SELECT * FROM Emp_Intl
WHERE First_Name = ‘Mo’
AND Dept_No = 100 ;
When multiple columns are queried together in the WHERE clause and they are Non-Unique Secondary Indexes, you might see a BMSMS in the explain plan, which means a bit map is being used to speed up a complicated query.
The Facts on Primary Indexes, Secondary Indexes, and Full Table Scans
Multiple Choice DBA
A large table called Big_Table exists. Two completely different departments query Big_Table regularly. The Sales Department always queries on a Unique column. The Marketing Department always queries on a Non-Unique column.
You are the DBA and it is up to you to choose the best indexing for Big_Table. Choose the multiple choice answer that will work best so both departments are happy.
A) UPI and NUPI
B) USI and NUSI
C) UPI and NUSI
D) NUPI and USI
You have to pick either A, B, C or D. What do you think will work as the best indexing for Big_Table? Remember that one department always queries on a unique column, and the other department always queries on a Non-Unique column. Good Luck!
Multiple Choice DBA
A large table called Big_Table exists. Two completely different departments query Big_Table regularly. The Sales Department always queries on a Unique column. The Marketing Department always queries on a Non-Unique column.
You are the DBA and it is up to you to choose the best indexing for Big_Table. Choose the multiple choice answer that will work best so both departments are happy.
A) UPI and NUPI (Can't have two primary indexes)
B) USI and NUSI ( I need at least one Primary Index)
C) UPI and NUSI (1-AMP and All-AMP)
D) NUPI and USI (1-AMP and 2 AMP)
D is the best answer because the NUPI will provide the department who always queries on a Non-Unique column with a NUPI Single-AMP retrieve. The department who always queries on a unique column can use the USI which is a 2-AMP retrieve.
What are the Big Four Tactical Queries?
There are four Tactical Queries. A Tactical Query can be depended on to run in sub second time. Can you name the four Tactical Queries from fastest to slowest?
1)
2)
3)
4)
Tactical queries will run very fast, and they can be depended upon to run in sub second time. Can you list the four fastest queries in order from fastest to slowest, but all must run in under sub second time?
What are the Big Four Tactical Queries?
There are four Tactical Queries. A Tactical Query can be depended on to run in sub second time. Can you name the four Tactical Queries from fastest to slowest?
1) Unique Primary Index (UPI) in the WHERE Clause
2) Non-Unique Primary Index (NUPI) in the WHERE Clause
3) Unique Secondary Index (USI) in the WHERE Clause
4) All Amp Retrieve by way of a Single Partition
Tactical queries will run very fast and they can be depended upon to run in sub second time. The fastest is, of course, example 1 with an UPI in the Where clause. Then, it is the NUPI in the Where clause. Then, it is the USI in the WHERE clause. Finally, it is an All AMP retrieve by way of a Single Partition.