- Cover Page
- Title Page
- Copyright Page
- About Tom Coffing
- About Leona Coffing
- Contents
- Chapter 1 - The Teradata Fundamentals
- What is Parallel Processing?
- The Basics of a Single Computer
- Teradata Parallel Processes Data
- Parallel Architecture
- All Teradata Tables are Spread across All AMPS
- Teradata Systems can Add AMPs for Linear Scalability
- Teradata Parallel Processing
- Teradata Systems can Continue to Grow to Thousands of AMPs
- How Teradata Creates Tables
- 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
- Using Explain
- A Non-Unique Primary Index is also a Single AMP Retrieve
- Using Explain in a NUPI Query
- 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
- Which Query is the Fastest?
- Answer - Which Query is the Fastest?
- Which Query is the Slowest?
- 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?
- Chapter 3 - Space
- Perm and Spool Space
- 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?
- Roles
- Create a Role and then Assign that Role its Access Rights
- Create a User and Assign them a Default Role
- Granting Access Rights
- There are Three Types of Access Rights
- Description of the Three Types of Access Rights
- Profiles
- Creating a Profile and a User
- ProfileInfoVX, RoleMembers, RoleInfo and UserRoleRights
- Accounts and their Associated Priorities
- Creating a User with Multiple Account Priorities
- Account String Expansion (ASE)
- The DBC.AMPUsage View
- Teradata TASM Provides a User Traffic System
- Teradata Viewpoint
- 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
- Chapter 6 - Secondary Indexes
- Teradata Primary and Secondary Index Cheat Sheet
- Teradata Query Cheat Sheet
- Creating a Unique Secondary Index (USI)
- Creating a Non-Unique Secondary Index (NUSI)
- Creating a Value-Ordered NUSI
- Nusi Bitmap
- The Facts on Primary Indexes, Secondary Indexes, and Full Table Scans
- Multiple Choice DBA
- Multiple Choice DBA
- What are the Big Four Tactical Queries?
- What are the Big Four Tactical Queries?
- Chapter 7 - 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?
- 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
- Histogram Quiz
- Answers to Histogram Quiz
- What to Collect Statistics on?
- Why Collect Statistics?
- 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
- Chapter 9 - View Functions
- Creating a Simple View
- Basic Rules for Views
- How to Modify a View
- Exceptions to the Order by Rule Inside a View
- How to Get Help with 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
- Aggregates on View Aggregates
- Locking Row for Access
- Altering a Table
- Altering a Table after a View has been Created
- A View that Errors after an Alter
- Troubleshooting a View
- Updating Data in a Table Through a View
- Maintenance Restrictions on a Table Through a View
- Chapter 10 - Macro Functions
- The 14 Rules of Macros
- Creating and Executing a Simple Macro
- Multiple SQL Statements Inside a Macro
- Complex Joins 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
- Troubleshooting a Macro
- Chapter 11 - Explain
- Explain Keywords
- Explain Keywords Continued
- 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 Example – BMSMS
- 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
- Creating a Bi-Temporal Table
- Period Data Types
- 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
- As of SQL for Temporal Tables
- Nonsequenced for Both
- Creating Views for Temporal Tables
- Bi-Temporal Example – Socrates is Deleted!
- Bi-Temporal Results – Socrates is Deleted
- Chapter 13 - Temporary Tables
- There are Three Types of Temporary Tables
- Creating a Derived Table
- Naming the Derived Table
- 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
- Quiz - Answer the Questions
- 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
- 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
- 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
- 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.