Teradata Data Distribution - Primary Index

Teradata Data Distribution 

There are three keys to how Teradata spreads the data among the AMPs. They are Primary Index, Primary Index, and Primary Index

The Primary Index

Teradata takes each table and spreads the table rows across the AMPs. When the table needs to be read, each AMP has to read only their portion of the table. If the AMPs start reading at the same time and there are an equal amount of rows on each AMP, then parallel processing works brilliantly. Alone an AMP can do so little, but the AMPs working together can accomplish the incredible. This brilliant feat begins with the Primary Index.

Each table in Teradata is required to have a Primary Index. The biggest key to a great Teradata Database Design begins with choosing the correct Primary Index. The Primary Index will determine on which AMP a row will reside. Because this concept is extremely important, let me state again that the Primary Index is the only thing that will determine on which AMP a row will reside.

Many people new to Teradata assume that the most important concept concerning the Primary Index is data distribution. INCORRECT! The Primary Index does determine data distribution, but even more importantly the Primary Index provides the fastest physical path to retrieving data. The Primary Index also plays an incredibly important role in how joins are performed. Remember these three important concepts of the Primary Index and you are well on your way to a great Physical Database Design.

The Primary Index plays 3 roles:

·       Data Distribution

·       Fastest Way to Retrieve Data

·       Incredibly important for Joins

The Two Types of Primary Indexes

Every table must have at least one column as the Primary Index. The Primary Index is defined when the table is created. There are only two types of Primary Indexes, which are a Unique Primary Index (UPI) or a Non-Unique Primary Index (NUPI).

Unique Primary Index (UPI)

A Unique Primary Index means that the values for the selected column must be unique. If you try and insert a row with a Primary Index value that is already in the table, the row will be rejected. A Unique Primary Index will always spread the table rows evenly amongst the AMPs. Please don’t assume this is always the best thing to do. Below is a table that has a Unique Primary Index. We have selected EMP to be our Primary Index. Because we have designated EMP to be a Unique Primary Index then there can be no duplicate employee numbers in the table.

                      Employee Table

EMP DEPT LNAME FNAME SAL

UPI

1 100 JOHN CHRIS 10000

2 200 JOHN JACK 13000

3 300 LICE ANN 12000

4 300 BROWN MARY 30000

A Unique Primary Index (UPI) will always spread the rows of the table evenly amongst the AMPs.

Don’t assume this is always the best choice!

Non-Unique Primary Index

A Non-Unique Primary Index (NUPI) means that the values for the selected column can be non-unique. You can have billions of the same values in the Primary Index. A Non-Unique Primary Index will almost never spread the table rows evenly. Please don’t assume this is always a bad thing. Below is a table that has a Non-Unique Primary Index. We have selected LNAME to be our Primary Index. Because we have designated LNAME to be a Non-Unique Primary Index we are anticipating that there will be individuals in the table with the same last name.

 Employee Table

EMP  DEPT LNAME FNAME SAL

NUPI

1 100 JOHN CHRIS 10000

2 200 JOHN JACK 13000

3 300 LICE ANN 12000

4 300 BROWN MARY 30000

A Non-Unique Primary Index (UPI) will almost NEVER spread the rows of the table evenly amongst the AMPs.

Don’t assume this is a bad thing!

How Teradata Turns the Primary Index Value into the Row Hash

The Primary Index is the only thing that determines where a row will reside. It is important that you understand this process. Here are the fundamentals in the simplest form. When a new row arrives into Teradata then:

Teradata examines the Primary Index value for the row.

Teradata takes that Primary Index value and runs it through a Hashing Formula.

The output of the Hashing Algorithm (a.k.a., Formula) is a 32-bit Row Hash.

The 32-bit Row Hash will perform two functions:

1. The 32-bit Row Hash will point to a certain spot on the Hash Map, which will indicate which AMP will hold the row.

2. The 32-bit Row Hash will always remain with the Row as part of a Row Identifier (Row ID).

Hashing is a mathematical process where an Index (UPI, NUPI) is converted into a 32-bit row hash value. The key to this hashing algorithm is the Primary Index. When this value is determined, the output of this 32-bit value is called the Row Hash.

A new row is going to be inserted into Teradata. The Primary Index is the column called EMP. The value in EMP for this row is 99. Teradata runs the value of 99 through the Hash Formula and the output is a 32-bit Row Hash. In his example our 32-bit Row Hash output: 00001111000011110000111100001111.

The Row Hash Value determines the Rows Destination

The first 16 bits of the Row Hash (a.k.a., Destination Selection Word) is used to locate an entry in the Hash Map. This entry is called a Hash Map Bucket. The only thing that resides inside a Hash Map Bucket is the AMP number where the row will reside.

The first 16 bits of the Row Hash of 00001111000011110000111100001111 is used to locate a bucket in the Hash Map. A bucket will contain an AMP number. We now know that employee 99 whose row hash is 00001111000011110000111100001111 will reside on AMP 4. Note: The AMP uses the entire 32 bits in storing and accessing the row.

If we took employee 99 and ran it through the hash formula again and again, we would always get a row hash of 00001111000011110000111100001111.

If we take the row hash of 00001111000011110000111100001111 again and again, it would always point to the same bucket in the hash map.

Every time employee 99 is run through the hash formula it returns the same Row Hash. That Row Hash will point to the same Hash Bucket every time. That is how Teradata knows which AMP will hold row 99. It does the math and it always gets what it always got!

The Row is Delivered to the Proper AMP

Now that we know that Employee 99 is to be delivered to AMP 4(For ex), Teradata packs up the row, places the Row Hash on the front of the row, and delivers it to AMP 4.

REVIEW:

·       A row is to be inserted into a Teradata table

·       The Primary Index Value for the Row is put into the Hash Algorithm

·       The output is a 32-bit Row Hash

·       The Row Hash points to a bucket in the Hash Map

·       The bucket points to a specific AMP

·       The row along with the Row Hash are delivered to that AMP

The AMP will add a Uniqueness Value

When the AMP receives a row it will place the row into the proper table, and the AMP checks if it has any other rows in the table with the same row hash. If this is the first row with this particular row hash the AMP will assign a 32-bit uniqueness value of 1. If this is the second row hash with that particular row hash, the AMP will assign a uniqueness value of 2. The 32-bit row hash and the 32-bit uniqueness value make up the 64-bit Row ID. The Row ID is how tables are sorted on an AMP.

How Teradata Retrieves Rows

In the example below a user runs a query looking for information on Employee 99. The PE sees that the Primary Index Value EMP is used in the SQL WHERE clause. Because this is a Primary Index access operation, the PE knows this is a one AMP operation. The PE hashes 99 and the Row Hash is 00001111000011110000111100001111. This points to a bucket in the Hash Map that represents AMP 4. AMP 4 is sent a message to get the Row Hash: 00001111000011110000111100001111 and make sure it’s EMP 99.

Teradata Data Placement

Teradata Data Placement 

Because Teradata was built for large data warehouses, its architects knew that data placement and management of tables could be a full time job. That is why they designed Teradata to automatically manage the data. Nobody had ever attempted this incredible feat. The Teradata designers dreamed of things that never were and made them so.

Managing table space, disks, and other system administration functions in a data warehouse is a nightmare. Teradata has made the DBA’s role a dream because Teradata lets the system handle the difficult functions. Teradata not only spreads the data evenly, but it can retrieve it quickly because it knows which AMP holds a particular row.

Teradata always attempts to spread data evenly so each AMP will manage approximately the same amount of data. As a result, the rows of every table are distributed across all of the AMPs. In other words, every AMP stores a portion of every table in the database on its virtual disk (VDISK). If a data warehouse has 200 tables, then each AMP will hold a portion the 200 tables. This method of data distribution is unique only to Teradata.

There are some significant benefits to handling data this way: First, the biggest bottleneck in any system is the disk. Because each AMP has their own virtual disk and each table is spread among the AMPs, there is no disk bottleneck.

Second, when each AMP has nearly the same quantity of table rows, then no one AMP becomes a data bottleneck. AMPs can retrieve all or a portion of the data in parallel so you do not have AMPs sitting idle while others are chugging away. Baseball superstar Casey Stengel once said, “It’s easy to get good players. Getting’ em to play together, that’s the hard part.” AMPs love to work together in parallel.

Third, each AMP is unaware of any data except its own portion. Each AMP can ONLY read or write to a particular row of data that the AMP actually owns. This makes retrieving data from a particular row very efficient as all AMPs focus on their own work. Fourth, each AMP automatically groups all of its rows by the tables from which they come. Have you ever been to a large aquarium and seen one of the displays that look like a very tall, clear cylinder? As you walk around the glass, the fish tend to swim in schools. Similarly, Teradata does this with the rows on the AMPs to boost performance. When you ask for data from any given table, an AMP will immediately go to that particular group of rows, and then select what you need. It doesn’t need to look through the rows of many tables before it finds what you need. This is how parallel processing works. The AMPs retrieve data in parallel and then pass it over the BYNET to the PE. The PE ensures the data is delivered to the user. Keep in mind the BYNET is an internal Teradata network, across which the PEs and the AMPs communicate.

The example below shows the information we have just discussed. Notice that the system has four AMPs, and three tables: “Employee,” “WebLog,” and “Order.” Notice each AMP holds a portion of the rows for every table. AMP1, for example, holds 1/4^th of the Employee table rows, 1/4^th of the WebLog table rows, and 1/4^th of the Order table rows.

Plus, the data is spread evenly across for all tables. If a query asks for all rows in the employee table, then each AMP will retrieve their employee table rows in parallel. Each AMP will then pass its data to the PE via the BYNET. Because the data in the employee table is spread evenly among all AMPs, each should finish reading at exactly the same time.

Also, notice how each AMP separates each table. Just like schools of fish, the rows of the Employee table are grouped together. In addition, the WebLog and Order tables are grouped together. This is important key in a data warehouse environment because most queries read millions of rows to satisfy a single query. Performance is enhanced when table rows are grouped together and Teradata is permitted to bring blocks of rows into memory.