title
Please take a moment to fill out this form. We will get back to you as soon as possible.
All fields marked with an asterisk (*) are mandatory.
Teradata Physical Implementation
Duration
2 Days
Course
CFLQ-185
Available Formats
Classroom Training, Online Training
Course Description
Overview
This Teradata Physical Implementation course is designed to provide students with in-depth knowledge of Teradata Implementation. This course can be taught using Version 12, 13, or 14. Please inform your sales representative of which version you are currently using.Objectives
Audience
- A mix of beginning,intermediate and advanced Teradata users
Prerequisites
- None
Topics
- Teradata Spreads the Rows across AMPs
- The Teradata Architecture
- The Parsing Engine
- The PE comes up with a PLAN the AMPs follow
- The AMPs
- Born to be parallel
- The BYNET
- A Scalable Architecture
- Single-AMP Retrieve
- Primary Index
- Two Types of Primary Indexes (UPI or NUPI)
- Unique Primary Index (UPI)
- Non-Unique Primary Index (NUPI)
- Multi-Column Primary Indexes
- When do you define the Primary Index?
- Defining a Non-Unique Primary Index (NUPI)
- Defining a Multi-Column Primary Index
- Hashing the Primary Index Value
- The Hash Map
- An 8-AMP Hash Map Example
- Laying a Row onto the Proper AMP
- Retrieving a Row by way of the Primary Index
- Hashing Non-Unique Primary Indexes (NUPI)
- Placing Non-Unique Primary Indexes (NUPI) Rows
- Non-Unique Primary Indexes Continued
- Placing (NUPI) Rows Continued
- Retrieving (NUPI) Rows
- Placing Multi-Column Primary Index Rows
- Retrieving Multi-Column Primary Index Rows
- Even Distribution with an UPI
- Uneven Distribution with a NUPI
- Unacceptable Distribution with a NUPI
- Review – Parsing Engines Plan with an UPI
- Review – Parsing Engines Plan with a NUPI
- Review – Big Trouble – The Full Table Scan
- Big Trouble – A Picture of a Full Table Scan
- Test your Teradata Primary Index Knowledge
- The Uniqueness Value
- The Row ID
- Duplicates and the Uniqueness Value
- AMPs Sort Their Rows by the Row ID
- Search the Data like a Phone Book
- A Visual for Data Layout
- Test Your Teradata Access Query Knowledge
- UPI Row-ID Test
- NUPI Row-ID Test
- The Base Table
- Creating a Unique Secondary Index (USI)
- The Secondary Index Subtable
- Inside the Secondary Index Subtable
- How Teradata builds the Secondary Index Subtable
- Lab: Place the USI Rows on the Proper AMP
- Lab: Place the USI Rows on the Proper AMP
- Building the Secondary Index Subtable Results
- USI – Always a Two-AMP Operation
- USI Summary
- USI Pictorial using the Hash Maps
- USI Secondary Index Quiz
- A Full Table Scan Example
- The Base Table
- Creating a Non-Unique Secondary Index (NUSI)
- Columns inside a NUSI Secondary Index Subtable
- NUSI Subtable is AMP-Local
- A Query using the NUSI Column
- A Query using the NUSI Column
- NUSI Recap
- Value Ordered NUSI
- Secondary Index Summary
- Test Your Teradata Access Query Knowledge
- An Incredible Quiz Opportunity
- Range Queries
- Why we had to perform a Full Table Scan
- A Partitioned Table
- A Partitioned Table
- One Year of Orders Partitioned
- Fundamentals of Partitioning
- Add the Partition to the Row-ID for the Row Key
- You Partition a Table when you CREATE the Table
- RANGE_N Partitioning by Week
- RANGE_N Partitioning Older and Newer Data
- Case_N Partitioning
- Multi-Level Partitioning
- New Feature V13.10 – Character Based PPI
- NEW! Character Based PPI Multi-Level Example
- NEW! CASE Based Character PPI
- TIMESTAMP partitioning that is Deterministic
- Disadvantages of PPI Tables
- PPI Tricks for the Primary Index
- Using the Keyword Partition
- Partitions for No Case, No Range and Unknown
- SQL for RANGE_N
- SQL for CASE_N
- Adding and Deleting Partitions
- Adding and Deleting Partitions with INSERT
- Partitioning Rules
- See the data
- Test Your Teradata Access Knowledge
- Columnar Tables are NOPI tables
- A No Primary Index (NOPI) table
- How to CREATE a NOPI table
- NOPI tables spread data evenly
- NOPI tables are often used for ETL Staging Tables
- NOPI Options
- NOPI Restrictions
- NOPI Row-ID Architecture
- Columnar Tables still have AMPs process entire row
- How does a Columnar Table differ from a traditional Teradata Table
- The fundamentals of Columnar
- Columnar splits rows into column Containers
- All Column Containers contain rows in perfect order
- Columnar Relative Row Numbers among Containers
- File System Generating (FSG) Cache
- How Teradata processes using FSG Cache
- How Columnar provides performance benefits
- How to create a Columnar Table
- Comparing Normal Tables to Columnar Tables
- How normal tables are processed
- How columnar tables are processed
- Indexes can be used with Columnar
- Visualize a Columnar Table
- Questions to answer about Columnar
- Single-Column and Multi-Column Containers
- Columnar Row Hybrid Creation
- Advanced Rules of Columnar Tables
- Row Based Partitioned (PPI) Tables
- CREATE Statement for both row and column partitioning
- Column and Row partitioning combined
- How to load a Columnar Table
- Columnar Table Access through Secondary Indexes
- Columnar Compression Capabilities
- Auto Compress in Columnar
- When and When NOT to use Columnar Tables
- Restrictions of Columnar Tables
- A Join Example
- Joins and the Primary Index
- Redistributing Rows in Spool
- Redistributing Rows of Both Tables
- Duplicating the Smaller Table
- Quiz – How Many Rows are in Spool?
- Quiz Answer – How Many Rows in Spool?
- How Duplication Appears on Every AMP
- How Many Rows in Spool with Redistribution?
- Answer to How Many Rows in Spool
- An Example of an AMP with Redistribution
- New V13 – Up to 128 Tables Joined Together!
- Primary and Foreign Keys
- A Normalized Data Warehouse
- Normalization
- Dimensional Modeling also called Star Schema
- Dimensional Modeling also called Star Schema
- Fact Table Vs Dimensional Table
- The Extended Logical Data Model (ELDM)
- The End Goal of the ELDM
- Column ACCESS in the WHERE Clause
- The Purpose of Examining Access
- The Purpose of Examining Access for Joins
- Data Demographics
- Distinct Values
- Distinct Values Danger
- Maximum Rows per Value
- Typical Rows per Value
- Maximum Rows NULL
- Change Rating
- Extended Logical Data Model Template
- The Application Development Life Cycle
- An Overview of picking the right Primary Index
- Step 1 – Look at Distribution
- Step 1 – Test - Find the Potential Index Candidates through good Distribution
- Step 1 – Answers- Find the Potential Index Candidates through good Distribution
- Step 2 – Eliminate based on Change Rating
- Step 3 – NUSI Elimination via Value Access Frequency
- Step 4 – Pick the Primary Index
- Why Join Access Frequency is Top Priority?
- Why Value Access Frequency is Second Priority?
- What have we learned about picking the Primary Index?
- You pick the final Primary and Secondary Index
- What have we learned about picking the Primary Index?
- Check your answer to the Primary Index pick
- Step 5 — Pick Secondary Indexes
- USI Considerations
- USI to eliminate Duplicate Row Checking
- NUSI considerations
- Four Techniques for using a NUSI
- Tactical Queries
- Derived Data
- Storing Aggregates
- Derived Data and Temporary Tables
- Derived Tables
- Volatile Temporary Tables
- Global Temporary Tables
- Pre–Joining Tables
- Repeating Groups
- Horizontal Partitioning 1 - Splitting Rows into Separate Tables
- Horizontal Partitioning 2 – Separating Rows through a Partitioned Primary Index (PPI) Table
- Vertical Partitioning
- Covered Query
- Value Ordered NUSI
- Identity Columns
- Join Index Fundamentals
- Many types of Join Indexes
- A Multi-Table Join Index Example
- Multi-Table Join Index Picture
- Compressed Multi-Table Join Index Example
- Compressed Multi-Table Join Index Picture
- Single-Table Join Index
- Explain showing use of the Join Index
- Compressed Single-Table Join Index Example
- Aggregate Join Index
- Sparse Join Index
- Global Multi-Table Join Index
- Hash Index
- Join Index Details to Remember
- New in V13 – Compression in your Join Index from the Base Tables
- How Compression Works
- Using the Nexus Query Chameleon to Compress
- An Example of SmartCompress of Nexus
- Compression Reports with Nexus
- Compression in V13
- Parsing Engine uses Statistics for the Plan
- Columns and Indexes to Collect Statistics On
- Syntax to Collect Statistics
- Recollecting Statistics
- Random Sample instead of Collected Statistics
- V12 Statistics Enhancement – Stale Statistics
- Where Statistics are Stored in DBC
- A Collect Statistics Example
- What Statistics Really Collects?
- Loner Values and High Bias Intervals
- Teradata Limits
- Teradata Load Utilities Continued
- BTEQ
- The Four Types of BTEQ Exports
- FastLoad
- FastLoad has Two Phases
- Acquisition Phase 1
- FastLoad Application Phase 2
- A Sample FastLoad Script
- MultiLoad
- The Five Phases of MultiLoad
- A Sample MultiLoad Script
- TPump
- Limitations of TPump
- A Sample TPump Script
- FastExport
- FastExport Enhancement – No Spool
- Teradata Parallel Transport
- The TPT Data Stream Flow
- TPT Operators
- Read from Multiple Source Files Simultaneously
- TPT Operators and their Functions
- TPT Operator Types
- TPT Operators and their Equivalent Load Utility
- How to Run a TPT Script
Related Courses
-
Teradata Database Administration
CFLQ-125- Duration: 3 Days
- Delivery Format: Classroom Training, Online Training
- Price: 2,295.00 USD
Self-Paced Training Info
Learn at your own pace with anytime, anywhere training
- Same in-demand topics as instructor-led public and private classes.
- Standalone learning or supplemental reinforcement.
- e-Learning content varies by course and technology.
- View the Self-Paced version of this outline and what is included in the SPVC course.
- Learn more about e-Learning
Course Added To Shopping Cart
bla
bla
bla
bla
bla
bla
Self-Paced Training Terms & Conditions
??spvc-wbt-warning??
Exam Terms & Conditions
??exam-warning??
??group-training-form-area??
??how-can-we-help-you-area??
??personalized-form-area??
??request-quote-area??
Sorry, there are no classes that meet your criteria.
Please contact us to schedule a class.
STOP! Before You Leave
Save 0% on this course!
Take advantage of our online-only offer & save 0% on any course !
Promo Code skip0 will be applied to your registration
Nothing yet
Purchase Information
??elearning-coursenumber?? ??coursename??
View Cart
title
Please take a moment to fill out this form. We will get back to you as soon as possible.
All fields marked with an asterisk (*) are mandatory.