O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Tera-Tom on Teradata Physical Implementation V12-V13

Book Description

Go inside the Teradata Engine and understand Teradata at a very indepth level.

Table of Contents

  1. Cover
  2. Copyright
  3. Chapter 1 – The Teradata Architecture
    1. Teradata Spreads the Rows across AMPs
    2. The Teradata Architecture
    3. The Parsing Engine
    4. The PE comes up with a PLAN the AMPs follow
    5. The AMPs
    6. Born to be Parallel
    7. The BYNET
    8. A Scalable Architecture
    9. Single-AMP Retrieve
    10. Primary Index
  4. Chapter 2 – The Primary Index
    1. Two Types of Primary Indexes (UPI or NUPI)
    2. Unique Primary Index (UPI)
    3. Non-Unique Primary Index (NUPI)
    4. Multi-Column Primary Indexes
    5. When do you define the Primary Index?
    6. Defining a Non-Unique Primary Index (NUPI)
    7. Defining a Multi-Column Primary Index
  5. Chapter 3 – Laying out and Retrieving Data
    1. Hashing the Primary Index Value
    2. The Hash Map
    3. An 8-AMP Hash Map Example
    4. Laying a Row onto the Proper AMP
    5. Retrieving a Row by way of the Primary Index
    6. Hashing Non-Unique Primary Indexes (NUPI)
    7. Placing Non-Unique Primary Indexes (NUPI) Rows
    8. Non-Unique Primary Indexes Continued
    9. Placing (NUPI) Rows Continued
    10. Retrieving (NUPI) Rows
    11. Placing Multi-Column Primary Index Rows
    12. Retrieving Multi-Column Primary Index Rows
    13. Even Distribution with an UPI
    14. Uneven Distribution with a NUPI
    15. Unacceptable Distribution with a NUPI
    16. Review – Parsing Engines Plan with an UPI
    17. Review – Parsing Engines Plan with a NUPI
    18. Review – Big Trouble – The Full Table Scan
    19. Big Trouble – A Picture of a Full Table Scan
    20. Test your Teradata Primary Index Knowledge
  6. Chapter 4 – The Row-ID
    1. The Uniqueness Value
    2. The Row ID
    3. Duplicates and the Uniqueness Value
    4. AMPs Sort Their Rows by the Row ID
    5. Search the Data like a Phone Book
    6. A Visual for Data Layout
    7. Test Your Teradata Access Query Knowledge
    8. UPI Row-ID Test
    9. NUPI Row-ID Test
  7. Chapter 5 – Secondary Indexes
    1. The Base Table
    2. Creating a Unique Secondary Index (USI)
    3. The Secondary Index Subtable
    4. Inside the Secondary Index Subtable
    5. How Teradata builds the Secondary Index Subtable
    6. Lab: Place the USI Rows on the Proper AMP
    7. Lab: Place the USI Rows on the Proper AMP
    8. Building the Secondary Index Subtable Results
    9. USI – Always a Two-AMP Operation
    10. USI Summary
    11. USI Pictorial using the Hash Maps
    12. USI Secondary Index Quiz
    13. A Full Table Scan Example
    14. The Base Table
    15. Creating a Non-Unique Secondary Index (NUSI)
    16. Columns inside a NUSI Secondary Index Subtable
    17. NUSI Subtable is AMP-Local
    18. A Query using the NUSI Column
    19. A Query using the NUSI Column
    20. NUSI Recap
    21. Value Ordered NUSI
    22. Secondary Index Summary
    23. Test Your Teradata Access Query Knowledge
    24. An Incredible Quiz Opportunity
  8. Chapter 6 – Partitioned Tables
    1. Range Queries
    2. Why we had to perform a Full Table Scan
    3. A Partitioned Table
    4. A Partitioned Table
    5. One Year of Orders Partitioned
    6. Fundamentals of Partitioning
    7. Add the Partition to the Row-ID for the Row Key
    8. You Partition a Table when you CREATE the Table
    9. RANGE_N Partitioning by Week
    10. RANGE_N Partitioning Older and Newer Data
    11. Case_N Partitioning
    12. Multi-Level Partitioning
    13. New Featue V13.10 – Character Based PPI
    14. NEW! Character Based PPI Multi-Level Example
    15. NEW! CASE Based Character PPI
    16. TIMESTAMP Partitioning that is Deterministic
    17. Disadvantages of PPI Tables
    18. PPI Tricks for the Primary Index
    19. Using the Keyword Partition
    20. Partitions for No Case, No Range and Unknown
    21. SQL for RANGE_N
    22. SQL for CASE_N
    23. Adding and Deleting Partitions
    24. Adding and Deleting Partitions with INSERT
    25. Partitioning Rules
    26. See the data
    27. Test Your Teradata Access Knowledge
  9. Chapter 7 – Joins
    1. A Join Example
    2. Joins and the Primary Index
    3. Redistributing Rows in Spool
    4. Redistributing Rows of Both Tables
    5. Duplicating the Smaller Table
    6. Quiz – How Many Rows are in Spool?
    7. Quiz Answer – How Many Rows in Spool?
    8. How Duplication Appears on Every AMP
    9. How Many Rows in Spool with Redistribution?
    10. Answer to How Many Rows in Spool
    11. An Example of an AMP with Redistribution
    12. New V13 – Up to 128 Tables Joined Together!
  10. Chapter 8 – Logical and Extended Logical Model (ELDM)
    1. Primary and Foreign Keys
    2. A Normalized Data Warehouse
    3. Normalization
    4. Dimensional Modeling also called Star Schema
    5. Dimensional Modeling also called Star Schema
    6. Fact Table Vs Dimensional Table
    7. The Extended Logical Data Model (ELDM)
    8. The End Goal of the ELDM
    9. Column ACCESS in the WHERE Clause
    10. The Purpose of Examining Access
    11. The Purpose of Examining Access for Joins
    12. Data Demographics
    13. Distinct Values
    14. Distinct Values Danger
    15. Maximum Rows per Value
    16. Typical Rows per Value
    17. Maximum Rows NULL
    18. Change Rating
    19. Extended Logical Data Model Template
    20. The Application Development Life Cycle
  11. Chapter 9 – The Physical Data Model
    1. An Overview of picking the right Primary Index
    2. Step 1 – Look at Distribution
    3. Step 1 – Test - Find the Potential Index Candidates through good Distribution
    4. Step 1 – Answers- Find the Potential Index Candidates through good Distribution
    5. Step 2 – Eliminate based on Change Rating
    6. Step 3 – NUSI Elimination via Value Access Frequency
    7. Step 4 – Pick the Primary Index
    8. Why Join Access Frequency is Top Priority?
    9. Why Value Access Frequency is Second Priority?
    10. What have we learned about picking the Primary Index?
    11. You pick the final Primary and Secondary Index
    12. What have we learned about picking the Primary Index?
    13. Check your answer to the Primary Index pick
    14. Step 5 — Pick Secondary Indexes
    15. USI Considerations
    16. USI to eliminate Duplicate Row Checking
    17. NUSI considerations
    18. Four Techniques for using a NUSI
    19. Tactical Queries
  12. Chapter 10 – Denormalization
    1. Derived Data
    2. Storing Aggregates
    3. Derived Data and Temporary Tables
    4. Derived Tables
    5. Volatile Temporary Tables
    6. Global Temporary Tables
    7. Pre–Joining Tables
    8. Repeating Groups
    9. Horizontal Partitioning 1 - Splitting Rows into Separate Tables
    10. Horizontal Partitioning 2 – Separating Rows through a Partitioned Primary Index (PPI) Table
    11. Vertical Partitioning
    12. Covered Query
    13. Value Ordered NUSI
    14. Identity Columns
  13. Chapter 11 – Join Indexes
    1. Join Index Fundamentals
    2. Many types of Join Indexes
    3. A Multi-Table Join Index Example
    4. Multi-Table Join Index Picture
    5. Compressed Multi-Table Join Index Example
    6. Compressed Multi-Table Join Index Picture
    7. Single-Table Join Index
    8. Explain showing use of the Join Index
    9. Compressed Single-Table Join Index Example
    10. Aggregate Join Index
    11. Sparse Join Index
    12. Global Multi-Table Join Index
    13. Hash Index
    14. Join Index Details to Remember
    15. New in V13 – Compression in your Join Index from the Base Tables
  14. Chapter 12 – Compression
    1. How Compression Works
    2. Using the Nexus Query Chameleon to Compress
    3. An Example of SmartCompress of Nexus
    4. Compression Reports with Nexus
    5. Compression in V13
  15. Chapter 13 – COLLECT STATISTICS
    1. Parsing Engine uses Statistics for the Plan
    2. Columns and Indexes to Collect Statistics On
    3. Syntax to Collect Statistics
    4. Recollecting Statistics
    5. Random Sample instead of Collected Statistics
    6. V12 Statistics Enhancement – Stale Statistics
    7. Where Statistics are Stored in DBC
    8. A Collect Statistics Example
    9. What Statistics are Really Collected
    10. Loner Values and High Bias Intervals
    11. Teradata Limits
  16. Chapter 14 – The Load Utilities
    1. Teradata Load Utilities Continued
    2. BTEQ
    3. The Four Types of BTEQ Exports
    4. FastLoad
    5. FastLoad has Two Phases
    6. Acquisition Phase 1
    7. FastLoad Application Phase 2
    8. A Sample FastLoad Script
    9. MultiLoad
    10. The Five Phases of MultiLoad
    11. A Sample MultiLoad Script
    12. TPump
    13. Limitations of TPump
    14. A Sample TPump Script
    15. FastExport
    16. FastExport Enhancement – No Spool
    17. Teradata Parallel Transport
    18. The TPT Data Stream Flow
    19. TPT Operators
    20. Read from Multiple Source Files Simultaneously
    21. TPT Operators and their Functions
    22. TPT Operator Types
    23. TPT Operators and their Equivalent Load Utility
    24. How to Run a TPT Script