Home Page Icon
Home Page
Table of Contents for
Title Page
Close
Title Page
by Tom Coffing, Leona Coffing
Teradata Basics for Business Users
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
Search in book...
Toggle Font Controls
Playlists
Add To
Create new playlist
Name your new playlist
Playlist description (optional)
Cancel
Create playlist
Sign In
Email address
Password
Forgot Password?
Create account
Login
or
Continue with Facebook
Continue with Google
Sign Up
Full Name
Email address
Confirm Email Address
Password
Login
Create account
or
Continue with Facebook
Continue with Google
Prev
Previous Chapter
Cover Page
Next
Next Chapter
front
T
ERADATA
B
ASICS
F
OR
B
USINESS
U
SERS
A
UTHORS
T
OM
C
OFFING
AND
L
EONA
C
OFFING
Book
2
T
ERA
-T
OM
G
ENIUS
S
ERIES
Add Highlight
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click
here
login for view all page.
Day Mode
Cloud Mode
Night Mode
Reset