Cover image for Teradata Basics for Business Users

Teradata Basics for Business Users

Author Tom Coffing , Leona Coffing
Release Date: 2013/09/01
ISBN: 9781940540122
Topic:
0%
24
Chapters
0-1
Hours read
0k
Total Words
    Start Reading Now    
       Add to Wishlist       
View table of contents

Book Description

The brilliance behind the Tera-Tom Genius series is that each book gets deeper and deeper into the details of Teradata. The Business Users book begins with brilliant fundamentals before diving into how Teradata works, how data is laid out, how people are set up as users, and how they are tracked. Business Users will completely understand Teradata at the exact level they need to after reading this book.

Table of Contents

  1. Cover Page
  2. Title Page
  3. Copyright Page
  4. About Tom Coffing
  5. About Leona Coffing
  6. Contents
  7. Chapter 1 - The Teradata Fundamentals
    1. What is Parallel Processing?
    2. The Basics of a Single Computer
    3. Teradata Parallel Processes Data
    4. Parallel Architecture
    5. All Teradata Tables are Spread across All AMPS
    6. Teradata Systems can Add AMPs for Linear Scalability
    7. Teradata Parallel Processing
    8. Teradata Systems can Continue to Grow to Thousands of AMPs
    9. How Teradata Creates Tables
    10. Every AMP has the Exact Same Tables
    11. All Teradata Tables are Spread across All AMPs
    12. Each Table has a Primary Index that is Unique or Non-Unique
    13. The Hash Map Determines which AMP will Own the Row
    14. A Unique Primary Index Spreads the Data Evenly
    15. A Non-Unique Primary Index Skews the Data
    16. Comparing the Same Table with Different Primary Indexes
    17. Unique Primary Index Queries are a Single AMP Retrieve
    18. Using Explain
    19. A Non-Unique Primary Index is also a Single AMP Retrieve
    20. Using Explain in a NUPI Query
    21. Teradata has a no Primary Index Table Called a NoPI Table
    22. A Conceptual Example of a Table with no Primary Index
    23. A Full Table Scan is Likely on a Table with no Primary Index
    24. Table Create Examples with Four Different Primary Indexes
    25. What Happens when you Forget the Primary Index?
  8. Chapter 2 - The Teradata Users' Fastest Queries
    1. Which Query is the Fastest?
    2. Answer - Which Query is the Fastest?
    3. Which Query is the Slowest?
    4. Answer - Which Query is the Slowest?
    5. Which Table is more Likely to have a Unique Primary Index?
    6. Answer - Which Table is more Likely to have a Unique Primary Index?
    7. How Many AMPs Involved with an UPI?
    8. Answer - How Many AMPs Involved with an UPI?
    9. How Many AMPs Involved with a NUPI?
    10. Answer - How Many AMPs Involved with an NUPI?
    11. Multi-Column Primary Index Quiz
    12. Answer - Multi-Column Primary Index Quiz
    13. Full Table Scan Times vs. Single AMP Retrieve
    14. Answer - Full Table Scan Times vs. Single AMP Retrieve
    15. Which Query is a Single AMP Retrieve?
    16. Answer - Which Query is a Single AMP Retrieve?
  9. Chapter 3 - Space
    1. Perm and Spool Space
    2. Perm Space is for Permanent Tables
    3. Spool Space is work Space that Builds a User's Answer Sets
    4. How is Spool Space Like a Hotel Lobby?
    5. Think of Spool Space Like Aisles in a Grocery Store
    6. When a User is Created they are Assigned a Spool Space Limit
    7. All Spool Space is Calculated on a Per AMP Basis
    8. What is a Common Reason that a User Runs out of Spool?
    9. Why is a Database Assigned Spool Space?
    10. Spool is an Individual Limit and not a Pool to Share
    11. What is the Purpose of Spool Limits?
    12. Perm Space is for Permanent Tables
    13. Perm and Spool is Calculated on a Per AMP Basis
    14. Why did My Query Abort and Say “Out of Spool”?
    15. How Come My Join Caused me to Run “Out of Spool”?
    16. Finding out how much Space you have
    17. Space Per AMP on All Tables in a Database Shows Skew
  10. Chapter 4 - The User Environment
    1. DBC is the only User when the System First Arrives
    2. DBC will Create Databases and Give them Space
    3. DBC will Create Some Initial Users
    4. A Typical Teradata Environment
    5. What are Similarities between a Database and a User?
    6. Roles
    7. Create a Role and then Assign that Role its Access Rights
    8. Create a User and Assign them a Default Role
    9. Granting Access Rights
    10. There are Three Types of Access Rights
    11. Description of the Three Types of Access Rights
    12. Profiles
    13. Creating a Profile and a User
    14. ProfileInfoVX, RoleMembers, RoleInfo and UserRoleRights
    15. Accounts and their Associated Priorities
    16. Creating a User with Multiple Account Priorities
    17. Account String Expansion (ASE)
    18. The DBC.AMPUsage View
    19. Teradata TASM Provides a User Traffic System
    20. Teradata Viewpoint
  11. Chapter 5 - Three Options for Teradata Table Design
    1. There are Three Options to Teradata Table Design
    2. How Teradata Creates Traditional Tables
    3. Each Table has a Primary Index
    4. A Query Using the Primary Index is a Single AMP Retrieve
    5. A Primary Index Query Uses a Single AMP and Single Block
    6. How Teradata Creates a PPI Table
    7. PPI Table Sorting the Rows by Month of Order_Date
    8. An All AMPs Retrieve by Way of a Single Partition
    9. Creating a PPI Table with Case_N
    10. A Visual of Case_N Partitioning
    11. An All AMPs Retrieve by Way of a Single Partition
    12. What does a Columnar Table Look Like?
    13. A Comparison of Data for Normal Vs. Columnar
    14. A Columnar Table is best for Queries with Few Columns
    15. Quiz – How Many Containers are in FSG Cache?
    16. Answer – How Many Containers are in FSG Cache?
    17. Intelligent Memory (Teradata V14.10)
    18. Teradata V14.10 Intelligent Memory Gives Data a Temperature
    19. Data Deemed VeryHot Stays in Each AMP's Intelligent Memory
    20. Intelligent Memory Stays in Memory
    21. Factors when Choosing Table Design
  12. Chapter 6 - Secondary Indexes
    1. Teradata Primary and Secondary Index Cheat Sheet
    2. Teradata Query Cheat Sheet
    3. Creating a Unique Secondary Index (USI)
    4. Creating a Non-Unique Secondary Index (NUSI)
    5. Creating a Value-Ordered NUSI
    6. Nusi Bitmap
    7. The Facts on Primary Indexes, Secondary Indexes, and Full Table Scans
    8. Multiple Choice DBA
    9. Multiple Choice DBA
    10. What are the Big Four Tactical Queries?
    11. What are the Big Four Tactical Queries?
  13. Chapter 7 - How Joins Work under the Covers
    1. Teradata Join Quiz
    2. Teradata Join Quiz Answer
    3. The Joining of Two Tables
    4. Teradata Moves Joining Rows to the Same AMP
    5. Imagine Joining Two NoPI Tables that have no Primary Index
    6. Both Tables are Redistributed to Join Rows on the Same AMP
    7. How do you Join if One Table is Big and One Table is Small?
    8. Duplicate the Small Table on Every AMP (Like a Mirror)
    9. What Could you do if Two Tables Joined 1000 Times a Day?
    10. Joining Two Tables with the same PK/FK Primary Index
    11. A Join With no Redistribution or Duplication
    12. A Performance Tuning Technique for Large Joins
    13. The Joining of Two Tables with an Additional Where Clause
    14. An Example of the Fastest Join Possible
    15. Using a Simple Volatile Table
    16. A Volatile Table with a Primary Index
    17. Using a Simple Global Temporary Table
    18. Two Brilliant Techniques for Global Temporary Tables
    19. The Joining of Two Tables Using a Global Temporary Table
    20. Quiz – How Much Data Moves across the BYNET?
    21. Answer – How Much Data Moves across the BYNET?
  14. Chapter 8 - Collect Statistics
    1. The Teradata Parsing Engine (Optimizer) is Cost Based
    2. The Purpose of Collect Statistics
    3. When Teradata Collects Statistics it Creates a Histogram
    4. The Interval of the Collect Statistics Histogram
    5. Histogram Quiz
    6. Answers to Histogram Quiz
    7. What to Collect Statistics on?
    8. Why Collect Statistics?
    9. How do you Know if Statistics were Collected on a Table?
    10. A Huge Hint that no Statistics have been Collected
    11. The Basic Syntax for Collect Statistics
    12. Collect Statistics Examples for a Better Understanding
    13. The New Teradata V14 Way to Collect Statistics
    14. The Official Syntax for Collect Statistics
    15. How to Re-Collect Statistics on a Table
    16. How to Copy a Table with Data and the Statistics?
    17. How to Copy a Table with no Data and the Statistics?
    18. When to Collect Statistics Using only a Sample
    19. Examples of Collect Statistics Using only a Sample
    20. Examples of Collect Statistics for V14
    21. How to Collect Statistics on a PPI Table on the Partition
  15. Chapter 9 - View Functions
    1. Creating a Simple View
    2. Basic Rules for Views
    3. How to Modify a View
    4. Exceptions to the Order by Rule Inside a View
    5. How to Get Help with a View
    6. Views Sometimes Created for Formatting or Row Security
    7. Another Way to Alias Columns in a View Create
    8. Resolving Aliasing Problems in a View Create
    9. Resolving Aliasing Problems in a View Create
    10. Resolving Aliasing Problems in a View Create
    11. Creating Views for Complex SQL Such as Joins
    12. Why Certain Columns Need Aliasing in a View
    13. Aggregates on View Aggregates
    14. Locking Row for Access
    15. Altering a Table
    16. Altering a Table after a View has been Created
    17. A View that Errors after an Alter
    18. Troubleshooting a View
    19. Updating Data in a Table Through a View
    20. Maintenance Restrictions on a Table Through a View
  16. Chapter 10 - Macro Functions
    1. The 14 Rules of Macros
    2. Creating and Executing a Simple Macro
    3. Multiple SQL Statements Inside a Macro
    4. Complex Joins Inside a Macro
    5. Passing an Input Parameter to a Macro
    6. Troubleshooting a Macro with Input Parameters
    7. Troubleshooting a Macro with Input Parameters
    8. An Update Macro with Two Input Parameters
    9. Executing a Macro with Named (Not Positional) Parameters
    10. Troubleshooting a Macro
  17. Chapter 11 - Explain
    1. Explain Keywords
    2. Explain Keywords Continued
    3. Explain Example – Full Table Scan
    4. Explain Example – Unique Primary Index (UPI)
    5. Explain Example – Non-Unique Primary Index (NUPI)
    6. Explain Example – Unique Secondary Index (USI)
    7. Explain Example – Redistributed to All-AMPs
    8. Explain Example – Row Hash Match Scan
    9. Explain Example – Duplicated on All-AMPs
    10. Explain Example –Low Confidence
    11. Explain Example – High Confidence
    12. Explain Example – Product Join
    13. Explain Example – BMSMS
    14. Explain Terminology for Partitioned Primary Index Tables
    15. Explain Example – From a Single Partition
    16. Explain Example – From N Partitions
    17. Explain Example – Partitions and Current_Date
  18. Chapter 12 - Temporal Tables Create Functions
    1. Three Types of Temporal Tables
    2. Creating a Bi-Temporal Table
    3. Period Data Types
    4. Bi-Temporal Data Type Standards
    5. Bi-Temporal Example – Tera-Tom Buys!
    6. A Look at the Temporal Results
    7. Bi-Temporal Example – Tera-Tom Sells!
    8. Bi-Temporal Example – How the Data Looks!
    9. Normal SQL for Bi-Temporal Tables
    10. Nonsequenced SQL for Temporal Tables
    11. As of SQL for Temporal Tables
    12. Nonsequenced for Both
    13. Creating Views for Temporal Tables
    14. Bi-Temporal Example – Socrates is Deleted!
    15. Bi-Temporal Results – Socrates is Deleted
  19. Chapter 13 - Temporary Tables
    1. There are Three Types of Temporary Tables
    2. Creating a Derived Table
    3. Naming the Derived Table
    4. Aliasing the Column Names in the Derived Table
    5. Most Derived Tables are Used to Join to Other Tables
    6. Multiple Ways to Alias the Columns in a Derived Table
    7. Our Join Example with a Different Column Aliasing Style
    8. Column Aliasing can Default for Normal Columns
    9. Creating a Derived Table Using the with Command
    10. Our Join Example With the With Syntax
    11. The Same Derived Query Shown Three Different Ways
    12. Quiz - Answer the Questions
    13. Answer to Quiz - Answer the Questions
    14. Clever Tricks on Aliasing Columns in a Derived Table
    15. A Derived Table Lives Only for the Lifetime of a Single Query
    16. An Example of Two Derived Tables in a Single Query
    17. Creating a Volatile Table
    18. You Populate a Volatile Table with an Insert/Select
    19. The Three Steps to use a Volatile Table
    20. Why Would you use the on Commit Delete Rows?
    21. The Help Volatile Table Command Shows your Volatiles
    22. A Volatile Table with a Primary Index
    23. The Joining of Two Tables Using a Volatile Table
    24. You can Collect Statistics on Volatile Tables
    25. The New Teradata V14 Way to Collect Statistics
    26. Four Examples of Creating a Volatile Table Quickly
    27. Four Advanced Examples of Creating a Volatile Table Quickly
    28. Creating Partitioned Primary Index (PPI) Volatile Tables
    29. Using a Volatile Table to Get Rid of Duplicate Rows
    30. Using a Simple Global Temporary Table
    31. Two Brilliant Techniques for Global Temporary Tables
    32. The Joining of Two Tables Using a Global Temporary Table
    33. Creating a Global Temporary Table
  20. Chapter 14 - Top SQL Commands Cheat Sheet
    1. Select All Columns from a Table and Sort
    2. Select Specific Columns and Limiting the Rows
    3. Changing your Default Database
    4. Keywords that Describe you
    5. Select Top Rows in a Rank Order
    6. A Sample Number of Rows
    7. Getting a Sample Percentage of Rows
    8. Find Information about a Database
    9. Find Information about a Table
    10. Using Aggregates
    11. Performing a Join
    12. Performing a Join Using ANSI Syntax
    13. Using Date, Time, and Timestamp
    14. Using Date Functions
    15. Using the System Calendar
    16. Using the System Calendar in a Query
    17. Formatting Data
    18. Using Rank
    19. Using a Derived Table
    20. Using a Subquery
    21. Correlated Subquery
    22. Using Substring
    23. Basic Case Statement
    24. Advanced Case Statement
    25. Using an Access Lock in your SQL
    26. Collect Statistics
    27. Creating a Volatile Table with a Primary Index
    28. Creating a Volatile Table that is Partitioned (PPI)
    29. Creating a Volatile Table that is Deleted after the Query
    30. Finding the Typical Rows Per Value for Specific Column
    31. Finding out how much Space you have
    32. How much Space you have Per AMP
    33. Finding your Space
    34. Finding Space Skew in Tables in a Database
    35. Finding the Number of Rows Per AMP for a Column
    36. Finding Account Information
    37. Ordered Analytics
  21. Back Cover
18.118.152.58