Chapter 1 — The Rules of Data Warehousing
Teradata Certification
A Logical View of the Teradata Architecture
The Parsing Engine (PE)
The Access Module Processors (AMPs)
The BYNET
A Visual for Data Layout
Teradata Cabinets, Nodes, Vprocs, and Disks
Chapter 2 — Data Distribution Explained
Rows and Columns
The Primary Index
The Two Types of Primary Indexes
Unique Primary Index (UPI)
Non-Unique Primary Index
Turning the Primary Index Value into the Row Hash
The Row Hash Value determines the Row’s Destination
The Row is Delivered to the Proper AMP
The AMP will add a Uniqueness Value
An Example of an UPI Table
An Example of a NUPI Table
How Teradata Retrieves Rows
Row Distribution
A Visual for Data Layout
Teradata accesses data in three ways
Data Layout Summary
Chapter 3 — V2R6 Partition Primary Indexes
V2R6 Partitioning
Partitioning doesn’t have to be part of the Primary Index
Partition Elimination can avoid Full Table Scans
The Bad NEWS about Partitioning on a column that is not part of the Primary Index
Two ways to handle Partitioning on a column that is not part of the Primary Index
Partitioning with CASE_N
Partitioning with RANGE_N
PPI Date Range in Months
PPI Date Range in Weeks
PPI Date Range in Days
Finding the Rows in each Partition
Altering a Table to Add or Delete Partitions
Altering a Table’s Partitions but saving the Data
Altering a Table’s Partitions on Empty Tables
NO CASE, NO RANGE, or UNKNOWN
Partitioning and Joins
PPI Advantages and Disadvantages
Chapter 4 — Teradata Engine – Under the Hood
Full Cylinder Read
Table Header
Each Table is given a Table ID
How Data Blocks are Dynamically Built
Data Blocks
How Teradata Finds a Row of Data
The Master Index
The Cylinder Index
Cylinder Index Changes
How Teradata Writes to an AMP
Writing to Data Blocks of Equal Length
When a Data Block is not Big Enough for a Write
How Teradata Allocates Blocks
Block and Row Definitions
Large Row versus Oversized Row
Defragmentation
When a Cylinder becomes Full
A Node and its Memory Allocations
Chapter 5 — The Extended Logical Data Model
The Application Development Life Cycle
Asking the Right Questions
Logical Data Model
Primary Keys
Foreign Keys
Normalization
Extended Logical Data Model
The End Goal of the ELDM is to build Table Templates
Column ACCESS in the WHERE Clause
Data Demographics
Distinct Values
Maximum Rows Per Value
Typical Rows Per Value
Maximum Rows NULL
Change Rating
Extended Logical Data Model Template
Chapter 6 — The Physical Data Model
Step 1 – Look at Distribution
Step 2 – Eliminate based on Change Rating
Step 3 – NUSI Elimination via Value Access Frequency
Step 4 – Pick the Primary Index
Primary Index Factors
Why Join Access Frequency is Top Priority?
A Real World Join Example
Why Value Access Frequency is Second Priority?
What have we learned about picking the Primary Index?
Results of Step 4 – Picking a Primary Index
Step 5 — Pick Secondary Indexes
USI to eliminate Duplicate Row Checking
NUSI considerations
Multi-Column NUSI Columns used as a Covered Query
Value-Ordered NUSIs
A formula for calculating a strongly selective NUSI
Typical Row Size
Typical Block Size
Final Result for Primary and Secondary Indexes
Chapter 7 — Denormalization
Derived Data
Storing Aggregates
Pre–Joining Tables
Repeating Groups
Horizontal Partitioning
Vertical Partitioning
Covered Query
Single-Table Join Indexes
Multi-Table Join Indexes
Temporary Tables
Derived Tables
Volatile Temporary Tables
Global Temporary Tables
Chapter 8 — Secondary Indexes
Secondary Indexes
Unique Secondary Index (USI)
USI Subtable Example
How Teradata retrieves an USI query
NUSI Subtable Example
How Teradata retrieves a NUSI query
Value-Ordered NUSI
How Teradata retrieves a Value-Ordered NUSI query
NUSI Bitmapping
Prototyping indexes with EXPLAIN
Chart for Primary and Secondary Access
Secondary Index Summary
Chapter 9 — Join Strategies
Join Types vs. Join Strategies
A Join in Simple Terms
The key things to know about Teradata and Joins
Merge Join Strategies
Joins need the joined rows to be on the same AMP
Another Great Join Picture
Joining Tables with matching rows on different AMPs
Joining Tables with matching rows on different AMPs
Redistributing a Table for Join Purposes
Big Table Small Table Join Strategy
Big Table Small Table Duplication
Nested Join
Hash Join
Exclusion Join
Product Joins
Cartesian Product Join
Chapter 10 — Join Indexes
Three basic types of Join Indexes
Join Index Fundamentals
Join Indexes versus other objects
Multi-Table Join Index
Single-Table Join Indexes
Aggregate Join Index
Sparse Index
Sparse Index Picture
Global Join Index
Global Join Index Picture
Global Join Index – Multi-Table Join Back
Hash Indexes
Hash Indexes vs. Single-Table Join Indexes
Chapter 11 — Explains
The Teradata Optimizer “knows” how to Explain in Detail
Row Estimate Confidence Levels
Explain Terminology
Full Table Scan
UNIQUE PRIMARY INDEX (UPI)
NON-UNIQUE PRIMARY INDEX (NUPI)
UNIQUE SECONDARY INDEX (USI)
NON-UNIQUE SECONDARY INDEX
What is a Psuedo Lock?
Confidence Levels
Execute the following steps in Parallel
Redistributed by Hash Code
Duplicated on All AMPs
JOIN INDEX
BMSMS Bit Mapping
PPI Tables and Partitions
Group_AMPs, SORT, Eliminating Duplicate Rows, and No Residual Conditions
Last Use, End Transaction, and Computed Globally
Visual Explain
Chapter 12 — The Parsing Engine in Detail
The Parsing Engine (PE) goes through Six Steps
Each PE has a Plan Library called RTS Cache
The Parsing Engine has Data Dictionary Cache
Why the PE loves the Macro
The Parsing Engine in Detail
The Parsing Engine Knows All
Chapter 13 — Understanding Views and Macros
How to Change a VIEW Using REPLACE
How to Drop a VIEW
View Aggregation and Nested Views
All About Macros
Creating a MACRO
Macros that Use Parameters
Changing a MACRO Using REPLACE
How to Execute a MACRO
How to Drop a MACRO
Chapter 14 - Locks
Teradata has Four locks at Three levels
Locks and their compatibility
How Teradata Locks Objects
Teradata Locks – First Come First Serve
Locking Modifier
The NOWAIT Option
Chapter 15 - Collect Statistics
Dynamic AMP Sampling
How Collect Statistics Works
Sample Statistics
Sample Statistics
What You Should COLLECT STATISTICS On
COLLECT STATISTICS DETAILED SYNTAX
COLLECT STATISTICS Examples
Chapter 16 - MISC
Identity Columns
Identity Columns Example
LIKE Clause
SUBSTRING and SUBSTR Functions
Referential Integrity
Soft Referential Integrity
Materialized Views
Compression
Implementing Compression
How Compression Works
Creating a Table With DATABLOCKSIZE
The ALTER TABLE Command