Cover image for Teradata Architecture for IT and Developers

Teradata Architecture for IT and Developers

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

Book Description

'This book will make every IT/Developer in your company a superstar. This book is designed to teach IT and developers everything they want to know about Teradata while leaving no stone unturned. They are going to see and understand Teradata at a level they’ve never understood it before. It is a brilliant way to explain what is happening in Teradata down to the most detailed levels. Like the rest of the Genius Series, it takes a building block approach to allow readers to continually see how the data is laid out, how to design the tables as well as understand the mechanisms that Teradata uses, and understand why Teradata is the best data warehouse in the world.

Table of Contents

  1. Cover Page
  2. Title Page
  3. Copyright Page
  4. About Tom Coffing
  5. About John Nolan
  6. Contents
  7. Chapter 1 - The Teradata Architecture
    1. What is Parallel Processing?
    2. The Basics of a Single Computer
    3. Teradata Parallel Processes Data
    4. Parallel Architecture
    5. The Teradata Architecture
    6. All Teradata Tables are Spread across ALL AMPS
    7. Teradata Systems can Add AMPs for Linear Scalability
    8. Understand that Teradata can Scale to Incredible Size
    9. AMPs and Parsing Engines (PEs) Live inside SMP Nodes
    10. Each Node is Attached Via a Network to a Disk Farm
    11. Two SMP Nodes Connected become One MPP System
    12. There are Many Nodes in a Teradata Cabinet
    13. Inside a Teradata Node
    14. The Boardless BYNET and the Physical BYNET
    15. The Parsing Engine
    16. The AMPs Responsibilities
    17. This is the Visual you Want to know in Order to Understand Teradata
  8. Chapter 2 - The Primary Index
    1. The Primary Index is Defined when the Table is CREATED
    2. A Unique Primary Index (UPI)
    3. Primary Index in the WHERE Clause - Single-AMP Retrieve
    4. Using EXPLAIN
    5. A Non-Unique Primary Index (NUPI)
    6. Primary Index in the WHERE Clause - Single-AMP Retrieve
    7. Using EXPLAIN in a NUPI Query
    8. A Conceptual Example of a Multi-Column Primary Index
    9. Primary Index in the WHERE Clause - Single-AMP Retrieve
    10. A Conceptual Example of a Table with NO PRIMARY INDEX
    11. A Full Table Scan is likely on a Table with NO Primary Index
    12. An EXPLAIN that Shows a Full Table Scan
    13. Table CREATE Examples with Four Different Primary Indexes
    14. What Happens when you Forget the Primary Index?
    15. Why Create a Table with No Primary Index (NoPI)?
  9. Chapter 3 - Hashing of the Primary Index
    1. The Hashing Formula Facts
    2. The Hash Map Determines which AMP will Own the Row
    3. The Hash Map Determines which AMP will Own the Row
    4. Placing Rows on the AMP
    5. Placing Rows on the AMP Continued
    6. A Review of the Hashing Process
    7. Non-Unique Primary Indexes have Skewed Data
    8. The Uniqueness Value
    9. The Row Hash and Uniqueness Value Make up the Row-ID
    10. A Row-ID Example for a Unique Primary Index
    11. A Row-ID Example for a Non-Unique Primary Index (NUPI)
    12. Two Reasons why Each AMP Sorts their Rows by the Row-ID
    13. AMPs Sort their Rows by Row-ID to Group Like Data
    14. AMPs Sort their Rows by Row-ID to Do a Binary Search
    15. Table CREATE Examples with Four Different Primary Indexes
    16. Null Values all Hash to the Same AMP
    17. A Unique Primary Index (UPI) Example
    18. A Non-Unique Primary Index (NUPI) Example
    19. A Multi-Column Primary Index Example
    20. A No Primary Index (NoPI) Example
  10. Chapter 4 - Teradata - The Cold Hard Facts
    1. All Teradata Tables are Spread Across all AMPs
    2. The Table Header and the Data Rows are Stored Separately
    3. An AMP Stores the Rows of a Table inside a Data Block
    4. To Read a Data Block, an AMP Moves the Block into Memory
    5. Nothing is Done on Disk and Everything is Done in Memory
    6. Most Taxing Thing for an AMP is Moving Blocks into Memory
    7. A Full Table Scan Means all AMPs must Read all Rows
    8. The “Achilles Heel and Slowest Process is Block Transfer
    9. Each Table has a Primary Index
    10. A Query using the Primary Index is a Single AMP Retrieve
    11. As Rows are Added a Data Block will Eventually Split
    12. A Full Table Scan Means all AMPs must Read all Blocks
    13. A Primary Index Query uses a Single AMP and Single Block
    14. Each AMP can have Many Blocks for a Single Table
    15. A Full Table Scan Means all AMPs must Read all Blocks
    16. Quiz - How Many Blocks Move into FSG Cache?
    17. Answer - How Many Blocks Move into FSG Cache?
    18. Quiz - How Many Blocks Move using the Primary Index?
    19. Answer - How Many Blocks Move using the Primary Index?
    20. Synchronized Scan (Sync Scan)
    21. EXPLAIN using a Synchronized Scan
    22. Intelligent Memory (Teradata V14.10)
    23. Teradata V14.10 Intelligent Memory Gives Data a Temperature
    24. Data Deemed VeryHot Stays in Each AMP's Intelligent Memory
    25. Intelligent Memory Stays in Memory
    26. What is the Goal of a Teradata Physical Database Design?
  11. Chapter 5 - Inside the AMPs Disk
    1. Rows are Stored in Data Blocks which are Stored in Cylinders
    2. An AMP's Rows are Stored inside a Data Block in a Cylinder
    3. An AMP's Master Index is used to Find the Right Cylinder
    4. The Row Reference Array (RRA) Does the Binary Search?
    5. A Block Splits into Two Blocks at Maximum Block Size
    6. Data Blocks Maximum Block Size has Changed (V14.10)
    7. The New Block Split with Teradata V14.10
    8. The Block Split with Even More Detail in Teradata V14.10
    9. Teradata V14.10 Block Split Defaults
    10. There is One Master Index and Thousands of Cylinder Indexes
    11. Blocks Continue to Split as Tables Grow Larger
    12. FYI - Some Advanced Information about Data Block Headers
    13. A Top Down View of Cylinders
    14. There are Hot, Warm, and Cold Cylinders
    15. Cylinders are used for Perm, Spool, Temp, and Journals
    16. Each AMP has their Own Master Index
    17. Each Cylinder on an AMP has a Cylinder Index
    18. Quiz - What Two Things Does and AMP Read?
    19. Answer - What Two Things Does and AMP Read?
    20. Quiz - How Many Row Reference Arrays Do you See?
    21. Answer - How Many Row Reference Arrays Do you See?
    22. Quiz - How Many Row Reference Arrays are there Now?
    23. Answer - How Many Row Reference Arrays Do you See?
    24. Quiz - How Many Row Reference Arrays in Total?
    25. Answer - How Many Row Reference Arrays in Total?
    26. Quiz - How Many Cylinder Indexes are Here?
    27. Answer - How Many Cylinder Indexes are Here?
    28. A More Detailed Illustration of the Master Index
    29. A Real-World View of the Master Index
    30. An Even More Realistic View of an AMP's Master Index
    31. The Cylinder Index
    32. An Even More Realistic View of a Cylinder Index
    33. How a Query using the Primary Index Works
    34. How the AMPs Do a Full Table Scan
    35. How an AMP Reads using a Primary Index
  12. Chapter 6 - Partition Primary Index (PPI) Tables
    1. The Concept behind Partitioning a Table
    2. Creating a PPI Table with Simple Partitioning
    3. A Visual Display of Simple Partitioning
    4. An SQL Example that Explains Simple Partitioning
    5. Creating a PPI Table with RANGE_N Partitioning Per Month
    6. A Visual of One Year of Data with Range_N Per Month
    7. An SQL Example Explaining Range_N Partitioning Per Month
    8. A Partition # and Row-ID = Row Key
    9. An AMP Stores its Rows Sorted in only Two Different Ways
    10. Creating a PPI Table with RANGE_N Partitioning Per Day
    11. A Visual of Range_N Partitioning Per Day
    12. An SQL Example that Explains Range_N Partitioning Per Day
    13. Creating a PPI Table with RANGE_N Partitioning Per Week
    14. A Visual of Range_N Partitioning Per Week
    15. SQL Example that Explains Range_N Partitioning Per Week
    16. A Clever Range_N Option
    17. Creating a PPI Table with CASE_N
    18. A Visual of Case_N Partitioning
    19. An SQL Example that Explains CASE_N Partitioning
    20. How Many Partitions Do you See?
    21. Number of PPI Partitions Allowed
    22. How Many Partitions Do you See?
    23. NO CASE and UNKNOWN Partitions Together
    24. A Visual of Case_N Partitioning
    25. Combining Older Data and Newer Data in PPI
    26. A Visual for Combining Older Data and Newer Data in PPI
    27. The SQL on Combining Older Data and Newer Data in PPI
    28. Multi-Level Partitioning Combining Range_N and Case_N
    29. A Visual of Multi-Level Partitioning
    30. The SQL on a Multi-Level Partitioned Primary Index
    31. NON-Unique Primary Indexes (NUPI) in PPI
    32. PPI Table with a Unique Primary Index (UPI)
    33. Tricks for Non-Unique Primary Indexes (NUPI)
    34. Character Based PPI for RANGE_N
    35. A Visual for Character-Based PPI for RANGE_N
    36. The SQL on Character-Based PPI for RANGE_N
    37. Character-Based PPI for CASE_N
    38. Dates and Character-Based Multi-Level PPI
    39. TIMESTAMP Partitioning
    40. Using CURRENT_DATE to Define a PPI
    41. ALTER to CURRENT_DATE the Next Year
    42. ALTER to CURRENT_DATE with Save
    43. Altering a PPI Table to Add or Drop Partitions
    44. Deleting a Partition
    45. Deleting a Partition and Saving its Contents
    46. Using the PARTITION Keyword in your SQL
    47. SQL for RANGE_N
    48. SQL for CASE_N
  13. Chapter 7 - Columnar Tables
    1. Columnar Tables have NO Primary Index
    2. This is NOT a NoPI Table
    3. NoPI Tables Spread Rows Across all-AMPs Evenly
    4. NoPI Tables used as Staging Tables for Data Loads
    5. NoPI Table Capabilities
    6. NoPI Table Restrictions
    7. What Does a Columnar Table Look Like?
    8. Comparing Normal Table Vs. Columnar Tables
    9. Columnar Table Fundamentals
    10. Example of Columnar CREATE Statement
    11. Columnar can Move Just One Container to Memory
    12. Containers on AMPs Match up Perfectly to Rebuild a Row
    13. Indexes can Be used on Columns (Containers)
    14. Indexes can Be used on Columns (Containers)
    15. Visualize a Columnar Table
    16. Single-Column Vs. Multi-Column Containers
    17. Comparing Normal Table Vs. Columnar Tables
    18. Columnar Row Hybrid CREATE Statement
    19. Columnar Row Hybrid Example
    20. Columnar Row Hybrid Query Example
    21. Review of Row-Based Partition Primary Index (PPI)
    22. Visual of Row Partitioning (PPI Tables) by Month
    23. CREATE Statement for both Row and Column Partition
    24. Visual of Row Partitioning (PPI Tables) and Columnar
    25. How to Load into a Columnar Table
    26. Columnar NO AUTO COMPRESS
    27. Auto Compress in Columnar Tables
    28. Auto Compress Techniques in Columnar Tables
    29. When and when NOT to use Columnar Tables
    30. Did you know?
  14. Chapter 8 - Space
    1. When your System Arrives, there is only User Named DBC
    2. USER DBC
    3. First Assignment is to Create Another User Just under DBC
    4. USER DBC
    5. Perm and Spool Space
    6. Perm Space is for Permanent Tables
    7. Spool Space is Work Space that Builds a User's Answer Sets
    8. Spool Space is in an AMP's Memory and on its Disk
    9. Users are Assigned Spool Space Limits
    10. What is the Purpose of Spool Limits?
    11. Why Did my Query Abort and Say “Out of Spool”?
    12. How can Skewed Data Cause me to Run “Out of Spool”?
    13. Why Did my Join Cause me to Run “Out of Spool”?
    14. Finding out how Much Space you have
    15. Space Per AMP on all Tables in a Database Shows Skew
    16. What Does my System Look Like when it First Arrives?
    17. DBC Owns all the PERM Space in the System on Day One
    18. DBC's First Assignment is Spool Space
    19. DBC's 2nd Assignment is to CREATE Users and Databases
    20. The Teradata Hierarchy Begins
    21. The Teradata Hierarchy Continues
    22. Differences between PERM and SPOOL
    23. Databases, Users, and Views
    24. What are Similarities between a DATABASE and a USER?
    25. What is the Difference between a DATABASE and a USER?
    26. Objects that take up PERM Space
    27. A Series of Quizzes on Adding and Subtracting Space
    28. Answer 1 to Quiz on Space
    29. Space Transfer Quiz
    30. Answer to Space Transfer Quiz
    31. Drop Space Quiz
    32. Answers to Drop Space Quiz
  15. Chapter 9 - 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
  16. Chapter 10 - Secondary Indexes
    1. Creating a Unique Secondary Index (USI)
    2. What is in a Unique Secondary Index (USI) Subtable?
    3. A Unique Secondary Index (USI) Subtable is Hashed
    4. How the Parsing Engine uses the USI Subtable
    5. A USI is a Two-AMP Operation
    6. Creating a Non-Unique Secondary Index (NUSI)
    7. What is in a Unique Secondary Index (USI) Subtable?
    8. Non-Unique Secondary Index (NUSI) Subtable is AMP Local
    9. How the Parsing Engine uses the NUSI Subtable
    10. Creating a Value-Ordered NUSI
    11. The Hash Map Determines which AMP will Own the Row
    12. A Unique Primary Index Spreads the Data Evenly
    13. Quiz - Answer the Tough USI Questions
    14. Answer to Quiz - Answer the Tough USI Questions
    15. A Picture with a Base Table, USI, and NUSI Subtable
    16. Quiz - Tough Questions on the USI and NUSI Subtables
    17. Answer - Tough Questions on the USI and NUSI Subtables
    18. A Query using an USI only Moves Two Blocks
    19. A Query using a NUSI Always uses all AMPs
    20. Two Non-Unique Secondary Indexes (NUSI) on a Table
    21. A NUSI BITMAP Query (1 of 3)
    22. A NUSI BITMAP Theory (2 of 3)
    23. A NUSI Bitmap in Action (3 of 3)
    24. A Brilliant Technique for a Unique Secondary Index
    25. The USI for Partitioned Tables Points to the Row Key
    26. A Brilliant Technique for a Non-Unique Secondary Index
    27. The NUSI for Partitioned Tables Points to the Row Key
    28. How the PE Decides on the NUSI or the Full Table Scan
    29. The Bigger Quiz
    30. The Bigger Quiz Answers
    31. Multiple Choice DBA
    32. Multiple Choice DBA
    33. What are the Big Four Tactical Queries?
    34. What are the Big Four Tactical Queries?
  17. Chapter 11 - 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
  18. Chapter 12 - How Joins Work Internally
    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?
    22. Teradata V14.10 Join Feature PRPD
  19. Chapter 13 - Join Indexes
    1. Creating a Multi-Table Join Index
    2. Visual of a Join Index
    3. Outer Join Multi-Table Join Index
    4. Visual of a Left Outer Join Index
    5. Compressed Multi-Table Join Index
    6. A Visual of a Compressed Multi-Table Join Index
    7. Creating a Single-Table Join Index
    8. Conceptual of a Single Table Join Index on an AMP
    9. Single Table Join Index Great for LIKE Clause
    10. Single Table Join Index with Value Ordered NUSI
    11. Aggregate Join Indexes
    12. Compressed Single-Table Join Index
    13. Aggregate Join Index
    14. New Aggregate Join Index (Teradata V14.10)
    15. Sparse Join Index
    16. A Global Multi-Table Join Index
    17. Creating a Hash Index
    18. Join Index Details
  20. Chapter 14 - 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. Where Does Teradata Keep the Collected Statistics?
    15. The Official Syntax for COLLECT STATISTICS
    16. How to Recollect STATISTICS on a Table
    17. Teradata Always Does a Random AMP Sample
    18. Random Sample is Kept in the Table Header in FSG Cache
    19. Multiple Random AMP Samplings
    20. How a Random AMP Gets a Table Row Count
    21. Random AMP Estimates for NUSI Secondary Indexes
    22. USI Random AMP Samples are not Considered
    23. There's no Random AMP Estimate for Non-Indexed Columns
    24. The PE's Plan if no Statistics Were Collected?
    25. Stale Statistics Detection and Extrapolation
    26. Extrapolation for Future Dates
    27. How to Copy a Table with Data and the Statistics?
    28. How to Copy a Table with NO Data and the Statistics?
    29. COLLECT STATISTICS Directly from Another Table
    30. When to COLLECT STATISTICS using only a SAMPLE
    31. Examples of COLLECT STATISTICS using only a SAMPLE
    32. Examples of COLLECT STATISTICS for V14
    33. How to Collect Statistics on a PPI Table on the Partition
    34. Teradata V12 and V13 Statistics Enhancements
    35. Teradata V14 Statistics Enhancements
    36. Teradata V14 Summary Statistics
    37. Teradata V14 MaxValueLength
    38. Teradata V14 MaxIntervals
    39. Teradata V14 Sample N Percent
    40. Teradata V14.10 Statistics Collection Improvements
    41. Teradata V14.10 Statistics Collection Improvements
    42. Teradata V14.10 AutoStats feature
    43. Teradata Statistics Wizard
  21. Chapter 15 - 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. WITH RECURSIVE Derived Table
    18. Defining the WITH Recursive Derived Table
    19. Looping through the Recursive Derived Table
    20. Looping through a Second Time
    21. Looping through a Third Time
    22. Looping through and Adding Nothing Ends the Loop
    23. Looping through the WITH Recursive Derived Table
    24. Creating a Volatile Table
    25. You Populate a Volatile Table with an INSERT/SELECT
    26. The Three Steps to use a Volatile Table
    27. Why Would you use the ON COMMIT DELETE ROWS?
    28. The HELP Volatile Table Command Shows your Volatiles
    29. A Volatile Table with a Primary Index
    30. The Joining of Two Tables using a Volatile Table
    31. You can Collect Statistics on Volatile Tables
    32. The New Teradata V14 Way to Collect Statistics
    33. Four Examples of Creating a Volatile Table Quickly
    34. Four Advanced Examples of Creating a Volatile Table Quickly
    35. Creating Partitioned Primary Index (PPI) Volatile Tables
    36. Using a Volatile Table to Get Rid of Duplicate Rows
    37. Using a Simple Global Temporary Table
    38. Two Brilliant Techniques for Global Temporary Tables
    39. The Joining of Two Tables using a Global Temporary Table
    40. CREATING a Global Temporary Table
  22. Chapter 16 - Teradata Load Utilities Introduction
    1. The Teradata Utilities
    2. Block Level Utilities
    3. Row Level Utilities
    4. Fast Path Inserts using Insert/Select
    5. Fast Path Deletes
    6. Freespace Percent
    7. Referential Integrity and Load Utility Solutions
    8. Teradata has a No Primary Index Table Called a NoPI Table
    9. This is NOT Necessarily a NoPI Table
    10. NoPI Tables Spread Rows across all-AMPs Evenly
    11. NoPI Tables used as Staging Tables for Data Loads
    12. NoPI Table Capabilities
    13. NoPI Table Restrictions
    14. Why would a NoPI Table have a Row-ID?
    15. BTEQ - Batch Teradata Query Tool
    16. How to Logon to BTEQ in Interactive Mode
    17. Running Queries in BTEQ in Interactive Mode
    18. BTEQ Commands Vs BTEQ SQL Statements
    19. WITH BY Command for Subtotals
    20. WITH Command for a Grand Total
    21. WITH and WITH BY Together for Subtotals and Grand Totals
    22. How to Logon to BTEQ in a SCRIPT
    23. Running Queries in BTEQ through a Batch Script
    24. Running a BTEQ Batch Script through the Command Prompt
    25. Running a BTEQ Batch Script through the Run Command
    26. Using Nexus to Build your BTEQ Scripts
    27. Using Nexus to Build your BTEQ Scripts
    28. FastLoad
    29. Block Level Utility Limits
    30. FastLoad has Two Phases
    31. FastLoad Phase 1
    32. FastLoad Phase 2
    33. A Sample FastLoad Script Created by Nexus SmartScript
    34. Executing the FastLoad Script
    35. The Nexus SmartScript Easily Builds your Utilities
    36. The Nexus SmartScript FastLoad Builder
    37. Create and Execute your FastLoad Scripts with Nexus
    38. MultiLoad
    39. Block Level Utility Limits
    40. MultiLoad has Five Phases
    41. MultiLoad has IMPORT and DELETE Tasks
    42. A Sample MultiLoad Script Created by Nexus SmartScript
    43. TPump
    44. TPump is NOT a Block Level Utility and has no Limits
    45. Limitations of TPump
    46. A Sample TPump Script Created by Nexus SmartScript
    47. FastExport
    48. New Rules for Block Utilities
    49. A Sample FastExport Script Created by Nexus SmartScript
    50. FastExport by Default Places Null Indicators in Output
    51. A Sample FastExport Script Created by Nexus SmartScript
    52. What is TPT?
    53. TPT Producers Create Streams and Consumers Write them
    54. The Four Major Operators of TPT
    55. TPT can Read from Multiple Source Files in Parallel
    56. TPT can have More Operators than Consumers
    57. TPT Operators and their Equivalent Load Utility
    58. How to Run a TPT Script
  23. Chapter 17 - 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
  24. Back Cover