- Cover Page
- Title Page
- Copyright Page
- About Tom Coffing
- About Leah Nolander
- Contents
- Chapter 1 - Physical Database Design in Layman's Terms
- What is Parallel Processing?
- The Basics of a Single Computer
- Teradata Parallel Processes Data
- Parallel Architecture
- The Teradata Architecture
- All Teradata Tables are Spread across All AMPS
- Understand that Teradata can Scale to Incredible Size
- Teradata Parallel Processing
- How Teradata Creates Tables
- Every AMP has the Exact Same Tables
- All Teradata Tables are Spread across All AMPs
- The Table Header and the Data Rows are Stored Separately
- An AMP Stores the Rows of a Table inside a Data Block
- To Read a Data Block an AMP Moves the Block into Memory
- A Full Table Scan Means all AMPs must Read all Rows
- The “Achilles Heel” or Slowest Process is Block Transfer
- Each Table has a Primary Index
- A Query Using the Primary Index is a Single AMP Retrieve.
- Using EXPLAIN
- As Rows are Added a Data Block will Eventually Split
- A Full Table Scan Means all AMPs must Read all Blocks
- An EXPLAIN that shows a Full Table Scan
- A Primary Index Query Uses a Single AMP and Single Block
- Using EXPLAIN in a NUPI Query
- Each AMP can have Many Blocks for a Single Table
- A Full Table Scan Means all AMPs must Read all Blocks
- Quiz – How Many Blocks Move into FSG Cache?
- Answer – How Many Blocks Move into FSG Cache?
- Quiz – How Many Blocks Move Using the Primary Index?
- Answer – How Many Blocks Move Using the Primary Index?
- 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
- What is the Goal of a Teradata Physical Database Design?
- Chapter 2 - Three Options for Teradata Table Design
- There are Three Options to Teradata Table Design
- Teradata V14.10 Intelligent Memory
- 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?
- Factors when Choosing Table Design
- Teradata Limits for V12, V13 and V14
- Chapter 3 - Teradata - The Cold Hard Facts
- Teradata Parallel Processing
- 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
- The AMP Adds a Uniqueness Value to Create the Row-ID
- Each AMP Sorts their Tables by the Row-ID
- 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
- There are Normal Tables and then there are Partitioned Tables
- A Visual of One Year of Data with Range_N per Month
- Partitioning is Designed to Eliminate the Full Table Scan
- A Partition # and Row-ID = Row Key
- An AMP Stores its Rows Sorted in Only Two Different Ways
- AMPs Moves their Data Blocks into Memory to Read/Write
- Most Taxing Thing for an AMP is Moving Blocks into Memory
- Rows are Stored in Data Blocks which are Stored in Cylinders
- Rows for an AMP Stored inside a Data Block in a Cylinder
- An AMP's Master Index is Used to Find the Right Cylinder
- The Row Reference Array (RRA) Does the Binary Search?
- A Block Splits into Two Blocks at Maximum Block Size
- Data Blocks Maximum Block Size has Changed (V14.10)
- The New Block Split with Teradata V14.10
- The Block Split with Even More Detail in Teradata V14.10
- Teradata V14.10 Block Split Defaults
- There is One Master Index and Thousands of Cylinder Indexes
- Each Table has a 48-bit TableID
- Chapter 4 - How Joins Work Under the Covers
- Teradata Join Quiz
- Teradata Join Quiz Answer
- The Joining of Two Tables
- 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
- Using a Simple Volatile Table
- 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?
- Teradata V14.10 Join Feature PRPD
- Chapter 5 - Secondary Indexes – An Alternate Path to the Data
- Creating a Unique Secondary Index (USI)
- What is in a Unique Secondary Index (USI) Subtable?
- A Unique Secondary Index (USI) Subtable is Hashed
- How the Parsing Engine Uses the USI Subtable
- A USI is a Two-AMP Operation
- Creating a Non-Unique Secondary Index (NUSI)
- What is in a Unique Secondary Index (USI) Subtable?
- Non-Unique Secondary Index (NUSI) Subtable is AMP Local
- How the Parsing Engine Uses the NUSI Subtable
- Creating a Value-Ordered NUSI
- The Hash Map Determines which AMP will own the Row
- A Unique Primary Index Spreads the Data Evenly
- Quiz – Answer the Tough USI Questions
- Answer to Quiz – Answer the Tough USI Questions
- A Picture with a Base Table, USI, and NUSI Subtable
- Quiz – Tough Questions on the USI and NUSI Subtables
- Answer – Tough Questions on the USI and NUSI Subtables
- A Query Using an USI Only Moves Two Blocks
- A Query Using A NUSI Always Uses all AMPs
- Two Non-Unique Secondary Indexes (NUSI) on a Table
- A NUSI BITMAP Query (1 of 3)
- A NUSI BITMAP Theory (2 of 3)
- A NUSI Bitmap in Action (3 of 3)
- A Brilliant Technique for a Unique Secondary Index
- The USI for Partitioned Tables Points to the Row Key
- A Brilliant Technique for a Non-Unique Secondary Index
- The NUSI for Partitioned Tables Points to the Row Key
- The Bigger Quiz
- The Bigger Quiz Answers
- Multiple Choice DBA
- Multiple Choice DBA
- What are the Big Four Tactical Queries?
- What are the Big Four Tactical Queries?
- Chapter 6 – Physical Design
- The Four Stages of Modeling for Teradata
- The Logical Model
- The Logical Model can be Loaded inside Nexus
- First, Second and Third Normal Form
- Quiz – Choose that Normalization Technique
- Answer to Quiz – Choose that Normalization Technique
- Quiz – What Normalization is it Now?
- Answer to Quiz – What Normalization is it Now?
- The Employee_Table and Department_Table can be Joined
- The Employee_Table and Department_Table Join SQL
- The Extended Logical Model Template
- User Access is of Great Importance
- User Access in Layman's Terms
- User Access for Joins in Layman's Terms
- The Nexus shows Users the Table's Primary Index
- Data Demographics Tell us if the Column is Worthy
- Data Demographics – Distinct Rows
- Data Demographics – Distinct Rows Query
- Data Demographics – Max Rows Null
- Data Demographics – Max Rows Null Query
- Data Demographics – Max Rows Per Value
- Data Demographics – Max Rows Per Value
- Data Demographics – Typical Rows Per Value
- Typical Rows Per Value – Query 1 (Median)
- Typical Rows Per Value – Query 2 (Median)
- Row_Number with Qualify to Get the Typical Rows Per Value
- SQL to Get the Average Rows Per Value for a Column (Mean)
- Getting the Average Values Per Column
- Data Demographics – Change Rating
- Factors when Choosing Teradata Indexes
- Finding Table Sizes
- Finding Skew in the Tables in a Database
- Finding Skew in a Table
- Display the Distribution of a Column Per AMP
- Primary Index Data Demographics Candidate Guidelines
- Primary Index Access Considerations
- Answer -Three Important Primary Index Considerations
- The First Step is to Pick all Potential Primary Index Columns
- The First Step is to Pick all Potential Primary Index Columns
- The 2nd Step is to Pick all Potential Secondary Indexes
- Answer to 2nd Step to Picking Potential Secondary Indexes
- Now it is Time to Choose the Primary and Secondary Indexes
- 3rd Step is to Picking your Indexes
- Our Index Picks
- Chapter 7 - Denormalization
- Denormalization
- Derived Data
- Repeating Groups
- Pre-Joining Tables
- Storing Summary Data with a Trigger
- Summary Tables or Data Marts the Old Way
- Aggregate Join Index the New Way
- New Aggregate Join Index (Teradata V14.10)
- Horizontal Partitioning the Old Way
- Horizontal Partitioning the New Way
- Vertical Partitioning the Old Way
- A Vertical Partitioning Trick that is Old School
- Vertical Partitioning the New Way
- Temporary Tables - A Volatile Table with a Primary Index
- The Joining of Two Tables Using a Volatile Table
- Temporary Tables - Global Temporary Tables
- The Joining of Two Tables Using a Global Temporary Table
- Creating a Multi-Table Join Index
- Visual of a Join Index
- Creating a Single-Table Join Index
- Conceptual of a Single Table Join Index on an AMP
- Single Table Join Index Great for LIKE Clause
- Single Table Join Index with Value Ordered NUSI
- Sparse Join Index
- A Global Multi-Table Join Index
- Creating a Hash Index
- Chapter 8 – Things to Consider
- Teradata Mode Vs. ANSI Mode
- ANSI Mode Transactions
- Teradata Mode Transactions
- Multi-Statement Transaction in BTEQ
- Teradata Mode Default is a Set Table
- An Example of a Duplicate Row Error
- Creating a Set Table with a Unique Primary Index
- Creating a Set Table with a Unique Secondary Index
- ANSI mode Defaults to a Multiset Table
- Using a Volatile Table to Get Rid of Duplicate Rows
- Creating a Volatile Table
- 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
- Creating a Secondary Index on 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
- CREATING a Global Temporary Table
- Using a Simple Global Temporary Table
- Two Brilliant Techniques for Global Temporary Tables
- The Joining of Two Tables Using a Global Temporary Table
- Making an Exact Copy a Table
- Making a NOT-So-Exact Copy a Table
- Copying a Table
- Troubleshooting Copying and Changing the Primary Index
- Copying Only Specific Columns of a Table
- Copying a Table and Keeping the Statistics
- Copying a Table with Statistics
- Copying a Table Structure with NO Data but Statistics
- What to COLLECT STATISTICS On?
- 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
- COLLECT STATISTICS Directly from Another Table
- How to Recollect STATISTICS on a Table
- Examples of COLLECT STATISTICS for V14
- Chapter 9 - Deep Dive Overhead for each Row
- Why Go Deep inside the Overhead of a Row?
- A Row Layout in Teradata
- Row Length
- Row ID
- How the Row Hash is Created for Each Row
- Unique Primary Indexes have Even Distribution
- The AMP Adds a Uniqueness Value to its Rows
- The Row-Hash is 32-bits and so is the Uniqueness Value
- Non-Unique Primary Indexes have Skewed Data
- Flag Byte
- Presence Byte
- Presence Byte is Used to show Null Values in each Row
- A Close-up Look at the Presence Byte for Nulls
- An Extreme example to Look at the Presence Byte for Nulls
- Quiz – How Many Presence Bits Used for these Columns?
- Answer – How Many Presence Bits Used for these Columns?
- Quiz – How Many Presence Bits Used with NOT NULL?
- Answer – How Many Presence Bits Used with NOT NULL?
- Quiz – Which bit will be Set to a One?
- Answer – Which bit will be Set to a One?
- Quiz – How Many Presence Bits Needed Now?
- Answer – How Many Presence Bits Needed Now?
- What Happens when we Need More than One Presence Byte?
- An Example that must use a 2nd Presence Byte
- An Example that must use a 2nd Presence Byte
- Quiz – Answer the Presence Bit/Byte Questions?
- Answers to the – Answer the Presence Bit/Byte Questions?
- Quiz - How Many Nullable Columns are Possible?
- Answer- How Many Nullable Columns are Possible?
- Chapter 10 - Compression
- Important Information about Compression
- Presence Bytes are also Used for Compression
- Why One Byte (8 bits) can Represent up to 255 Values
- Answers to One Byte (8 bits) can Represent up to 255 Values
- Now that you Understand that 8 Bits can Represent 0 – 255
- A Compression Example that Compresses Two Values
- A Compression Example that Compresses Three Values
- Quiz – Name that Compression Value
- The Next Important Concept in Compression
- Quiz – Can you Fill in the Compression Values?
- Answer – Can you Fill in the Compression Values?
- The Last Major Concept in Compression
- Quiz – Using One Presence Byte for Multiple Columns
- Answer – Using One Presence Byte for Multiple Columns
- Quiz – How Many Presence Bytes are Needed?
- Answer – How Many Presence Bytes are Needed?
- Advanced Quiz – Fill in the Presence Bits?
- Answer to Advanced Quiz – Fill in the Presence Bits?
- The Cost Vs. the Savings
- The Cost List of Compression
- A Deeper Dive into NULL Values
- Quiz - How Much Space Did we Just Save?
- Answer - How Much Space Did we Just Save?
- Advanced Quiz - How Much Space Did we Just Save?
- Advanced Quiz - How Much Space Did we Just Save?
- Using the DBC Tables in a Compression Experiment
- A Compression Test
- A Compression Test
- We then Moved all Eight Tables to Another Database
- Compression Reports with Nexus and SmartCompress
- We then Created Two Global Temporary Tables
- We then Created and Executed our Macro
- Report Comparing Compressed and NonCompressed Tables
- Chapter 11 – Top SQL Commands Cheat Sheet
- SELECT all Columns from a Table and Sort
- Select Specific Columns and Limiting the Rows
- Changing your Default Database
- Keywords that Describe you
- Select TOP Rows in a Rank Order
- A Sample Number of Rows
- Getting a Sample Percentage of Rows
- Find Information about a Database
- Find Information about a Table
- Using Aggregates
- Performing a Join
- Performing a Join Using ANSI Syntax
- Using Date, Time and Timestamp
- Using Date Functions
- Using the System Calendar
- Using the System Calendar in a Query
- Formatting Data
- Using Rank
- Using a Derived Table
- Using a Subquery
- Correlated Subquery
- Using Substring
- Basic CASE Statement
- Advanced CASE Statement
- Using an Access Lock in your SQL
- Collect Statistics
- 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 your Space
- Finding Space Skew in Tables in a Database
- Finding the Number of Rows per AMP for a Column
- Finding Account Information
- Ordered Analytics
- Back Cover
-
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.