Chapter 2 – The Teradata Users' Fastest Queries
“Let me once again explain the rules. Tera-Tom books Rule!”
Which Query is the Fastest?
A)
SELECT *
FROM Employee_Table
WHERE Employee_No = 12345678 ;
B)
SELECT *
FROM Order_Table
WHERE Customer_No = 12345678 ;
Which query above will be the fastest because it is a Single AMP Retrieve?
Answer - Which Query is the Fastest?
A)
SELECT *
FROM Employee_Table
WHERE Employee_No = 12345678 ;
B)
SELECT *
FROM Order_Table
WHERE Customer_No = 12345678 ;
Which query above will be the fastest because it is a Single AMP Retrieve? Query A is using the Primary Index in the WHERE Clause.
Which Query is the Slowest?
A)
SELECT *
FROM Employee_Table
WHERE Employee_No = 12345678 ;
B)
SELECT *
FROM Order_Table
WHERE Order_No = 12345678 ;
C)
SELECT *
FROM Customer_Table
WHERE Customer_Name = ‘Acme’ ;
Which query above will be the slowest because it has to do a Full Table Scan?
Answer - Which Query is the Slowest?
A)
SELECT *
FROM Employee_Table
WHERE Employee_No = 12345678 ;
B)
SELECT *
FROM Order_Table
WHERE Order_No = 12345678 ;
C)
SELECT *
FROM Customer_Table
WHERE Customer_Name = ‘Acme’ ;
Query C is the slowest. It will do a Full Table Scan because it is NOT using the Primary Index of Customer_No in the WHERE clause.
Which Table is more likely to have a Unique Primary Index?
Do you think the Employee_Table or the Order_Table has a Unique Primary Index?
Answer - Which Table is more likely to have a Unique Primary Index?
The data in the Order_Table is skewed, so most likely it is the Employee_Table that might have a Unique Primary Index. A Non-Unique Primary Index will not have even distribution of the rows, but a Unique Primary Index always will have even distribution.
How Many AMPs involved with an UPI?
SELECT *
FROM Employee_Table
WHERE Employee_No = 12345678 ;
How many AMPs will the PE contact to satisfy this query? Will it be one, two, or all?
Answer - How Many AMPs involved with an UPI?
SELECT *
FROM Employee_Table
WHERE Employee_No = 12345678 ;
Only one AMP will be involved. The table was distributed by the Unique Primary Index of Employee_No. The PE knows where every Employee_No was placed, so when you use the Employee_No in the WHERE clause, it is always a Single AMP retrieve.
How Many AMPs involved with a NUPI?
SELECT *
FROM Order_Table
WHERE Customer_No = 12345678 ;
How many AMPs will the PE contact to satisfy this query? Will it be one, two, or all?
Answer - How Many AMPs involved with an NUPI?
SELECT *
FROM Order_Table
WHERE Customer_No = 12345678 ;
Only one AMP will be involved. The table was distributed by the Non-Unique Primary Index of Customer_No. The PE knows where every Customer_No was placed, so when you use the Customer_No in the WHERE clause, it is always a Single AMP retrieve. Even though the data is not unique, and Customer_No 1234567 could have placed many orders, those rows would all be hashed to the same AMP. No matter if you use an UPI or a NUPI in the WHERE clause, it is always a Single AMP retrieve.
Multi-Column Primary Index Quiz
A)
SELECT *
FROM Emp_Intl
WHERE First_Name = ‘Sushma’ ;
B)
SELECT *
FROM Emp_Intl
WHERE Last_Name = ‘Davis’ ;
C)
SELECT *
FROM Emp_Intl
WHERE First_Name = 'Sushma'
AND Last_Name = 'Davis' ;
Which query above will be a Single AMP Retrieve?
Answer - Multi-Column Primary Index Quiz
A)
SELECT *
FROM Emp_Intl
WHERE First_Name = ‘Sushma’ ;
B)
SELECT *
FROM Emp_Intl
WHERE Last_Name = ‘Davis’ ;
C)
SELECT *
FROM Emp_Intl
WHERE First_Name = 'Sushma' AND Last_Name = 'Davis' ;
Query C will be a Single AMP retrieve because it uses each column of the Primary Index in the WHERE and the AND clause.
Full Table Scan times vs. Single AMP retrieve
A)
If a full table scan took 4 hours on the Employee_Table, how long would it take if you used Employee_No in the WHERE clause?
B)
If a full table scan took 2 hours on the Order_Table, how long would it take if you used Order_No in the WHERE clause?
C)
If a full table scan took 1 hour on the Customer_Table, how long would it take if you used Customer_No in the WHERE clause?
Answer questions A, B, and C. A) __________ B) _________ C) _________
Answer - Full Table Scan times vs. Single AMP retrieve
A)
If a full table scan took 4 hours on the Employee_Table, how long would it take if you used Employee_No in the WHERE clause?
B)
If a full table scan took 2 hours on the Order_Table, how long would it take if you used Order_No in the WHERE clause?
C)
If a full table scan took 1 hour on the Customer_Table, how long would it take if you used Customer_No in the WHERE clause?
Answers: A (1 second) B (1 second) C (1 second)
Which Query is a Single AMP Retrieve?
A)
SELECT *
FROM Employee_Table
WHERE
Employee_No > = 12345 ;
B)
SELECT *
FROM Employee_Table
WHERE
Employee_No LIKE ‘123%’ ;
C)
SELECT *
FROM Employee_Table
WHERE
Employee_No = 12345 ;
Which Query is a Single AMP retrieve? Two of them are Full Table Scans.
Answer - Which Query is a Single AMP Retrieve?
A)
SELECT *
FROM Employee_Table
WHERE Employee_No > 12345;
B)
SELECT *
FROM Employee_Table
WHERE Employee_No LIKE ‘123%’;
C)
SELECT *
FROM Employee_Table
WHERE Employee_No = 12345 ;
Only Query C is a Single AMP Retrieve. Queries A and B are Full Table Scans.