Contents
Chapter 1 – The Teradata Fundamentals
The Basics of a Single Computer
Teradata Parallel Processes Data
All Teradata Tables are spread across ALL AMPS
Teradata Systems can Add AMPs for Linear Scalability
Teradata Systems can continue to grow to thousands of AMPs
Every AMP has the Exact Same Tables
All Teradata Tables are spread across All AMPs
Each Table has a Primary Index that is Unique or Non-Unique
The Hash Map Determines which AMP will own the Row
A Unique Primary Index Spreads the Data Evenly
A Non-Unique Primary Index Skews the Data
Comparing the Same Table with Different Primary Indexes
Unique Primary Index Queries are a Single AMP Retrieve
A Non-Unique Primary Index is also a Single AMP Retrieve
Teradata has a No Primary Index Table called a NoPI Table
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?
Chapter 2 – The Teradata Users' Fastest Queries
Answer - Which Query is the Fastest?
Answer - Which Query is the Slowest?
Which Table is more likely to have a Unique Primary Index?
Answer - Which Table is more likely to have a Unique Primary Index?
How Many AMPs involved with an UPI?
Answer - How Many AMPs involved with an UPI?
How Many AMPs involved with a NUPI?
Answer - How Many AMPs involved with an NUPI?
Multi-Column Primary Index Quiz
Answer - Multi-Column Primary Index Quiz
Full Table Scan times vs. Single AMP retrieve
Answer - Full Table Scan times vs. Single AMP retrieve
Which Query is a Single AMP Retrieve?
Answer - Which Query is a Single AMP Retrieve?
Perm Space is for Permanent Tables
Spool Space is work space that builds a User's Answer Sets
How is Spool Space like a Hotel Lobby?
Think of Spool Space like Aisles in a Grocery Store
When a User is created they are assigned a Spool Space Limit
All Spool Space is calculated on a Per AMP Basis
What is a common reason that a User runs out of Spool?
Why is a Database Assigned Spool Space?
Spool is an individual limit and not a Pool to Share
What is the Purpose of Spool Limits?
Perm Space is for Permanent Tables
Perm and Spool is Calculated on a Per AMP Basis
Why did my query Abort and say “Out of Spool”?
How come my Join caused me to run “Out of Spool”?
Finding out how much Space you have
Space per AMP on all tables in a Database shows Skew
Chapter 4 – The User Environment
DBC is the only user when the system first arrives
DBC will Create Databases and Give them Space
DBC will create some initial Users
A Typical Teradata Environment
What are Similarities between a DATABASE and a USER?
Create a Role and then Assign that Role Its Access Rights
Create a User and Assign them a Default Role
There are Three Types of Access Rights
Description of the Three Types of Access Rights
ProfileInfoVX, RoleMembers, RoleInfo and UserRoleRights
Accounts and their Associated Priorities
Creating a User with Multiple Account Priorities
Account String Expansion (ASE)
Teradata TASM provides a User Traffic System
Chapter 5 - Three Options for Teradata Table Design
There are Three Options to Teradata Table Design
How Teradata Creates Traditional Tables
Each Table has a Primary Index
A Query Using the Primary Index is a Single AMP Retrieve
A Primary Index Query uses a Single AMP and Single Block
How Teradata Creates a PPI Table
PPI Table Sorting the Rows by Month of Order_Date
An All AMPs Retrieve By Way of a Single Partition
Creating a PPI Table with CASE_N
A Visual of Case_N Partitioning
An All AMPs Retrieve By Way of a Single Partition
What does a Columnar Table look like?
A Comparison of Data for Normal Vs. Columnar
A Columnar Table is best for Queries with Few Columns
Quiz – How Many Containers are in FSG Cache?
Answer – How Many Containers are in FSG Cache?
Intelligent Memory (Teradata V14.10)
Teradata V14.10 Intelligent Memory Gives Data a Temperature
Data deemed VeryHot stays in each AMP's Intelligent Memory
Intelligent Memory Stays in Memory
Factors When Choosing Table Design
Teradata Primary and Secondary Index Cheat Sheet
Creating a Unique Secondary Index (USI)
Creating a Non-Unique Secondary Index (NUSI)
The Facts on Primary Indexes, Secondary Indexes, and Full Table Scans
What are the Big Four Tactical Queries?
What are the Big Four Tactical Queries?
Chapter 7 - How Joins Work Under the Covers
Teradata Moves Joining Rows to the Same AMP
Imagine Joining Two NoPI Tables that have No Primary Index
Both Tables are redistributed to Join Rows on the Same AMP
How do you join if One Table is Big and One Table is small?
Duplicate the Small Table on Every AMP (like a mirror)
What Could You Do If Two Tables Joined 1000 Times a Day?
Joining Two Tables with the same PK/FK Primary Index
A Join With No Redistribution or Duplication
A Performance Tuning Technique for Large Joins
The Joining of Two Tables with an Additional WHERE Clause
An Example of the Fastest Join Possible
A Volatile Table with a Primary Index
Using a Simple Global Temporary Table
Two Brilliant Techniques for Global Temporary Tables
The Joining of Two Tables Using a Global Temporary Table
Quiz – How Much Data Moves Across the BYNET?
Answer – How Much Data Moves Across the BYNET?
Chapter 8 - Collect Statistics
The Teradata Parsing Engine (Optimizer) is Cost Based
The Purpose of Collect Statistics
When Teradata Collects Statistics it Creates a Histogram
The Interval of the Collect Statistics Histogram
What to COLLECT STATISTICS On?
How do you know if Statistics were Collected on a Table?
A Huge Hint that No Statistics Have Been Collected
The Basic Syntax for COLLECT STATISTICS
COLLECT STATISTICS Examples for a better Understanding
The New Teradata V14 Way to Collect Statistics
The Official Syntax for COLLECT STATISTICS
How to Re-COLLECT STATISTICS on a Table
How to Copy a Table with Data and the Statistics?
How to Copy a Table with NO Data and the Statistics?
When to COLLECT STATISTICS Using only a SAMPLE
Examples of COLLECT STATISTICS Using only a SAMPLE
Examples of COLLECT STATISTICS For V14
How to Collect Statistics on a PPI Table on the Partition
Exceptions to the ORDER BY Rule inside a View
Views sometimes CREATED for Formatting or Row Security
Another Way to Alias Columns in a View CREATE
Resolving Aliasing Problems in a View CREATE
Resolving Aliasing Problems in a View CREATE
Resolving Aliasing Problems in a View CREATE
CREATING Views for Complex SQL such as Joins
WHY certain columns need Aliasing in a View
Altering a Table after a View has been created
A View that errors After an ALTER
Updating Data in a Table through a View
Maintenance Restrictions on a Table through a View
CREATING and EXECUTING a Simple Macro
Multiple SQL Statements inside a Macro
Passing an INPUT Parameter to a Macro
Troubleshooting a Macro with INPUT Parameters
Troubleshooting a Macro with INPUT Parameters
An UPDATE Macro with Two Input Parameters
Executing a Macro with Named (Not Positional) Parameters
Explain Example – Full Table Scan
Explain Example – Unique Primary Index (UPI)
Explain Example – Non-Unique Primary Index (NUPI)
Explain Example – Unique Secondary Index (USI)
Explain Example – Redistributed to All-AMPs
Explain Example – Row Hash Match Scan
Explain Example – Duplicated on All-AMPs
Explain Example –Low Confidence
Explain Example – High Confidence
Explain Example – Product Join
Explain Terminology for Partitioned Primary Index Tables
Explain Example – From a Single Partition
Explain Example – From N Partitions
Explain Example – Partitions and Current_Date
Chapter 12 - Temporal Tables Create Functions
Three types of Temporal Tables
Bi-Temporal Data Type Standards
Bi-Temporal Example – Tera-Tom buys!
A Look at the Temporal Results
Bi-Temporal Example – Tera-Tom Sells!
Bi-Temporal Example – How the data looks!
Normal SQL for Bi-Temporal Tables
NONSEQUENCED SQL for Temporal Tables
Creating Views for Temporal Tables
Bi-Temporal Example – Socrates is DELETED!
Bi-Temporal Results – Socrates is DELETED
There are three types of Temporary Tables
Aliasing the Column Names in the Derived Table
Most Derived Tables Are Used To Join To Other Tables
Multiple Ways to Alias the Columns in a Derived Table
Our Join Example with a Different Column Aliasing Style
Column Aliasing Can Default for Normal Columns
CREATING A Derived Table using the WITH Command
Our Join Example With the WITH Syntax
The Same Derived Query shown Three Different Ways
Answer to Quiz - Answer the Questions
Clever Tricks on Aliasing Columns in a Derived Table
A Derived Table lives only for the lifetime of a single query
An Example of Two Derived Tables in a Single Query
You Populate a Volatile Table with an INSERT/SELECT
The Three Steps to Use a Volatile Table
Why Would You Use the ON COMMIT DELETE ROWS?
The HELP Volatile Table Command Shows your Volatiles
A Volatile Table with a Primary Index
The Joining of Two Tables Using a Volatile Table
You Can Collect Statistics on Volatile Tables
The New Teradata V14 Way to Collect Statistics
Four Examples of Creating a Volatile Table Quickly
Four Advanced Examples of Creating a Volatile Table Quickly
Creating Partitioned Primary Index (PPI) Volatile Tables
Using a Volatile Table to Get Rid of Duplicate Rows
Using a Simple Global Temporary Table
Two Brilliant Techniques for Global Temporary Tables
The Joining of Two Tables Using a Global Temporary Table
CREATING A Global Temporary Table
Chapter 14 – Top SQL Commands Cheat Sheet
SELECT All Columns from a Table and Sort
Select Specific Columns and Limiting the Rows
Changing your Default Database
Select TOP Rows in a Rank Order
Getting a Sample Percentage of rows
Find Information about a Database
Find information about a Table
Performing a Join using ANSI Syntax
Using Date, Time, and Timestamp
Using the System Calendar in a Query
Using an Access Lock in your SQL
CREATING a Volatile Table with a Primary Index
CREATING a Volatile Table that is Partitioned (PPI)
CREATING a Volatile Table that is deleted after the Query
Finding the Typical Rows per Value for specific column
Finding out how much Space you have
How much Space you have Per AMP
Finding Space Skew in Tables in a Database