Call : (+91) 968636 4243
Mail : info@EncartaLabs.com
EncartaLabs

Teradata Architecture

( Duration: 2 Days )

The Teradata Architecture training course provides skills to take advantage of Teradata's unique parallel architecture to interpret SQL requests, receive input records, and pass data.

By attending Teradata Architecture workshop, delegates will learn:

  • The Teradata Architecture
  • The Primary Index
  • Hashing of the Primary Index
  • Inside the AMPs Disk
  • Join Functions
  • Partition Primary Index (PPI) Tables
  • Columnar Tables
  • Temporary Tables
  • Secondary Indexes
  • Join Indexes
  • Collect Statistics
  • Temporal Tables Create Functions
  • Teradata Load Utilities Introduction

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 6,000 various courses 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 https://www.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
Notice
X