Teradata Physical Database Design

Tom Coffing, Leah Nolander

• Publisher: Coffing Publishing
• Year: 2013
• eBook ISBN: 9781940540146
• Topic: Computer Science
• Subtopic: Data Warehousing
• Index: Computer Science

The design of the data warehouse is the most important aspect, so this book is a fantastic guide on exactly how to do just that. This book brilliantly touches on the important things a designer needs to do to build a world-class data warehouse. They will clearly understand the environment and all options to enhance the performance based on the design. This will teach you great detail in a high-level manor that really focuses clearly so you won't get bogged down with too much minutia. It will allow you to understand the important issues so you can make clear decisions.

Chapter 1 - Physical Database Design in Layman's Terms

“The man on top of the mountain did not fall there.”

-Longfellow

What is Parallel Processing?

“After enlightenment, the laundry”

-Zen Proverb

Tera-Tom's Parallel Processing Wash and Dry

“After parallel processing the laundry, enlightenment!”

-Teradata Zen Proverb

Two guys were having fun on a Saturday night when one said, “I've got to go and do my laundry.” The other said, “What?!” The man explained that if he went to the laundry mat the next morning, he would be lucky to get one machine and be there all day. But, if he went on Saturday night, he could get all the machines. Then, he could do all his wash and dry in two hours. Now that's parallel processing mixed in with a little dry humor!

The Basics of a Single Computer

How are we doing on orders today?

How would I know? I'm just a disk. I need to transfer the block of data to the memory, and that is a slow process.

Data on disk does absolutely nothing. When data is requested, the computer moves the data one block at a time from disk into memory. Once the data is in memory, it is processed by the CPU at lightning speed. All computers work this way. The “Achilles Heel” of every computer is the slow process of moving data from disk to memory. That is all you need to know to be a computer expert!

Teradata Parallel Processes Data

Teradata has been the pioneer in parallel processing since 1988 when Wells Fargo bought the first Teradata system. In the picture above, you see that we have 16 orders with four orders placed on each disk. It appears to be four separate computers, but this is one system. Teradata systems work just like a basic computer as they still need to move data from disk into memory, but Teradata divides and conquers.

Parallel Architecture

The rows of a Teradata table are spread across the AMPs, so each AMP can then process an equal amount of the rows when a USER queries the table.

The Teradata Architecture

The Parsing Engine (PE) takes the User's SQL and builds a Plan for each AMP to follow to retrieve the data. Parallel Processing is all about each AMP doing an equal amount of the work. If they start at the same time and end the same time, they are performing true Parallel Processing. All communication is done over the BYNET.

All Teradata Tables are spread across ALL AMPS

Each table dreams of spreading their rows equally across the AMPs. Above, are three tables with each table holding 9 rows (3-rows per AMP).

Understand that Teradata can scale to incredible size

“If you do what you've always done, you'll get what you've always got.”

-Anonymous

The largest systems in the world have used Teradata for market dominance for the past 20 years. Its Massively Parallel Processing (MPP) technology analyzes on such a large scale that companies can run queries they have never been able to run before. Recognize that you now have something very powerful and that has the ability to analyze every aspect of your business. So do what you've never done, and get something that you've never got.

Teradata Parallel Processing

Each AMP holds a portion of the rows for every table in the system.

Teradata was born to be parallel. When a user queries a system, they logon to a Parsing Engine for the entire session. The Parsing Engine checks the User's SQL syntax and their Access Rights for security purposes, but then it comes up with a plan for the AMPs to retrieve the data. The Parsing Engine passes the plan to the AMPs over one of the two BYNET networks, and the AMP's work simultaneously in parallel to retrieve the data.

How Teradata Creates Tables

CREATE TABLE Sales_Table

( Product_ID INTEGER

,Sale_Date DATE

,Daily_Sales DECIMAL(10,2)

) PRIMARY INDEX (Product_ID) ;

When a table is created, a Table Header is created on every AMP.

When a table is created, a Table Header is automatically created on every AMP. Thus each AMP has the exact same amount of tables. From a table perspective, each AMP looks exactly the same as if they were looking at each other in a mirror.

Every AMP has the Exact Same Tables

The first thing that happens when a table is created is that a Table Header is created on every AMP. So now, each AMP has the table, and they know the t...

Table of contents

• Cover Page
• Title Page
• Copyright Page
• About Tom Coffing
• About Leah Nolander
• Contents
• Chapter 1 - Physical Database Design in Layman's Terms
• Chapter 2 - Three Options for Teradata Table Design
• Chapter 3 - Teradata - The Cold Hard Facts
• Chapter 4 - How Joins Work Under the Covers
• Chapter 5 - Secondary Indexes – An Alternate Path to the Data
• Chapter 6 – Physical Design
• Chapter 7 - Denormalization
• Chapter 8 – Things to Consider
• Chapter 9 - Deep Dive Overhead for each Row
• Chapter 10 - Compression
• Chapter 11 – Top SQL Commands Cheat Sheet
• Back Cover