EncartaLabs

Teradata Architecture

( Duration: 2 Days )

COURSE AGENDA

1

The Teradata Architecture

  • The Parsing Engine
  • The AMPs
  • Born to be parallel
  • The BYNET
  • A Scalable Architecture
2

Data Distribution Explained

  • Logical Modeling – Primary and Foreign Keys
  • Physical Modeling – The 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
  • How Teradata Distributes and Retrieves Rows
  • Hashing the Primary Index Value
  • The Hash Map
  • Distributing a Row onto the Proper AMP
  • Retrieving a Row by way of the Primary Index
  • Hashing Non-Unique Primary Indexes (NUPI)
  • Distributing Non-Unique Primary Indexes (NUPI) Rows
  • Retrieving (NUPI) Rows
  • Distributing Multi-Column Primary Index Rows
  • Retrieving Multi-Column Primary Index Rows
  • Even Distribution with an UPI
  • Uneven Distribution with a NUPI
  • Unacceptable Skewed 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
3

Secondary Indexes

  • 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
  • USI – Always a Two-AMP Operation
  • The Parsing Engines Plan with an USI Query
  • Retrieving Base Rows using the USI
  • Picture that USI in Action
  • USI Pictorial using the Hash Maps
  • 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
4

Partition Primary Indexes

  • A Table used for our Partitioning Example
  • Range Queries
  • Why we had to perform a Full Table Scan
  • 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
  • Partitioning Rules
  • See the data
5

Users, Databases, and Space

  • The most Powerful USER
  • DBC owns all the Original Disk Space
  • DBC Example of 1000 GBs
  • DBC will first CREATE a USER or a DATABASE
  • Teradata is Hierarchical
  • Only two Objects can Receive PERM Space
  • Only difference between a User and a Database
  • A Typical approach to Security
  • Example of a DATABASE and USER Interchanged
  • PERM and SPOOL Space
  • Each AMP will have PERM and SPOOL
  • A Query using both PERM and SPOOL Space
  • Spool is Deleted when the Query is Done
  • Getting a better understanding of Spool
  • Answering the MRKT Spool Query Answer
  • Spool is like a Speed Limit
  • All Space is calculated on a Per AMP Basis
6

Collect Statistics

  • 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
  • What Statistics are Really Collected
  • Loner Values and High Bias Intervals
  • Sample Statistics
  • Extrapolated Statistics
  • Teradata Limits
7

Data Protection

  • Transaction Concept
  • Two Modes to Teradata
  • Differences between ANSI and Teradata Mode
  • ANSI Mode Commit
  • Teradata Mode Commit also called BTET
  • Trick to CREATE a Multi-Statement with BTEQ
  • Transient Journal
  • How the Transient Journal Works
  • The Transient Journal after a Commit
  • VProcs
  • Nodes and MPP
  • RAID 1 – Mirroring
  • Cliques
  • VProcs Migrate when a Node Fails
  • Hot Standby Nodes
  • Hot Standby Nodes in Action
  • FALLBACK Protection
  • How Fallback Works
  • Fallback Clusters
  • Fallback – Performance Vs Protection Quiz
  • The Six Rules of Fallback
  • Cliques and Clusters
  • Down AMP Recovery Journal (DARJ)
  • Write Ahead Logging (WAL)
  • Permanent Journal
  • Table create with Fallback and Permanent Journal
  • Permanent Journal Rules
  • Some Permanent Journal Possibilities
  • Creating a Permanent Journal
  • Create Table Examples with Permanent Journals
  • Each Permanent Journal is made up of 3 Areas
  • Permanent Journal Rules
  • Teradata Virtual Storage (New V13)
8

Teradata Locks

  • The Four Locks of Teradata
  • 3 levels of Locking
  • The Teradata Lock Manager
  • Locking Modifiers – The Access Lock
  • Locks and their compatibility
  • Moving Through the Locking Queue
  • A Single AMP Acts as the Locking Gatekeeper
  • Every AMP performs Locking Gatekeeper Dutie
  • Explains – The Pseudo Table for Locks
  • The NOWAIT Locking Option
  • Rules of Teradata Locking
9

EXPLAIN Plans

  • Explains – Psuedo Tables
  • Explain – Full Table Scan
  • Explain – Primary Index Reads
  • Explain – Secondary Index Read
  • Explain – View DDL of a Partitioned Table
  • Explain – Partition Elimination
  • Explain – Joins with Duplication on all AMPs
  • Explain – Joins with Redistribution
  • Explain – Bit Mapping with multiple NUSIs
  • Explain – How to Recognize a Product Join
10

Joins inside the Teradata Engine

  • Fundamentals of Teradata Joins
  • Joins and the Primary Index
  • Redistributing Rows in Spool
  • Redistributing Rows of Both Tables
  • Duplicating the Smaller Table
  • How Duplication Appears on Every AMP
  • How Many Rows in Spool with Redistribution?
  • Answer to How Many Rows in Spool
11

Teradata Temporary Tables

  • Derived Tables
  • A Query Pictorial Example with a Derived Table
  • Volatile Tables
  • How to populate a Volatile Table
  • Global Temporary Tables
  • A Pictorial of a Global Temporary Table
  • What happens to Global Tables at Session End?
  • Global Temp Tables and Temp Space
12

NOPI Tables with V13

  • NoPI CREATE Statements
  • The Purpose of a NoPI Table
  • NoPI Increments the Uniqueness Value in the ROWID
  • NoPI Row Hash is different on each AMP
  • NoPI Options and Facts
  • NoPI Restrictions
13

Write Ahead Logging

  • AMPs have FSG Cache for Memory Speed
  • An Example of an UPDATE Statement
  • AMP Local WALs
  • AMPs UPDATE rows in FSG Cache
  • Write to WAL and then Write to Disk
  • The WAL Depot
  • Clearing out the WAL Log and WAL Depot
14

Teradata Virtual Storage (TVS)

  • AMPs in the 1980’s
  • AMPs in the 1990’s
  • Data Blocks and Cylinders make up a Disk
  • Cylinders are dedicated to PERM, SPOOL, Etc.
  • Outside Disk Tracks are much faster
  • AMPs assigned Disk Cylinders not entire disks
  • Hot, Warm, and Cold Data
  • The old way Teradata had to add Disk Space
  • Doubling the Disk Capacity
  • Incremental Disk Growth is here
  • Mixed Disks and Solid State Drives
  • Solid State Drives are like Giant Flash Drives
  • Virtual Storage Metrics
  • The Two Modes of Teradata TVS

Encarta Labs Advantage

  • One Stop Corporate Training Solution Providers for over 4,000 Modules on a variety of subjects
  • All courses are delivered by Industry Veterans
  • Get jumpstarted from newbie to production ready in a matter of few days
  • Trained more than 50,000 Corporate executives across the Globe
  • All our trainings are conducted in workshop mode with more focus on hands-on sessions

View our other course offerings by visiting http://encartalabs.com/course-catalogue-all.php

Contact us for delivering this course as a public/open-house workshop/online training for a group of 10+ candidates.

Top