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
Teradata Maximums
Teradata Maximums per Release
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 Rows Destination
The Row is Delivered to the Proper AMP
The AMP will add a Uniqueness Value
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
NO CASE, NO RANGE, or UNKNOWN
Partitioning and Joins
Chapter 4 — Teradata Space
How Permanent Space is Given
The Teradata Hierarchy
How Spool Space is calculated
A Spool Space Example
PERM, SPOOL and TEMP Space
Spool Space controls system time
Spool Reserve
A quiz on Perm and Spool Space
Another quiz on Perm and Spool Space
Chapter 5 — Modeling the Data
The Application Development Life Cycle
Asking the Right Questions
Logical Data Model
Primary Keys
Foreign Keys
Normalization
A Normalized Data Warehouse
Dimensional Modeling
Data Marts
Update Applications
Chapter 6 — 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 7 — Denormalization
Derived Data
Horizontal Partitioning
Vertical Partitioning
Multi-Table Join Indexes
Temporary Tables
Derived Tables
Volatile Temporary Tables
Global Temporary Tables
Views
Chapter 8 — Secondary Indexes
Unique Secondary Index (USI)
How Teradata retrieves an USI query
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
A Join in Simple Terms
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
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 - Locks
Teradata has 4 locks for 3 levels of Locking
Locks and their compatibility
How Teradata Locks Objects
Teradata Locks – First Come First Serve
Locking
The NOWAIT Option
Chapter 13 — Tools
Database Query Log (DBQL)
DBQL Collection Options
DBQL Tables and Views
How to Begin Logging for DBQM
Teradata Dynamic Query Manager (TDQM)
TDQM works with Queryman (SQL Assistant)
TDQM Workloads and Rules
Performance Monitor - PM
Access Logging
Statistics Wizard
Index Wizard
TSET
Chapter 14 — Loading the Data
Fastload
Multiload
Multiload Picture
Tpump
Fastexport
Warehouse Builder
Insert/Select
Utilities Summary
Chapter 15 — Priority Scheduler
Priority Scheduler Partition Hierarchy
Priority Scheduler Hierarchy Definitions
Multiple Resource Partitions Example
Scheduling Policies
Performance Periods