Chapter 14 - Top SQL Commands Cheat Sheet
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
Chapter 14 – Top SQL Commands Cheat Sheet
“You miss 100 percent of the shots you never take.”
-Wayne Gretzky
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
-
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.