Although a relational data model uses Primary Keys and Foreign Keys to establish the relationships between tables, that design is a Logical Model. Each vendor uses specialized techniques to implement a Physical Model. Teradata does not use keys in its physical model. Instead, Teradata is implemented using indices, both primary and secondary.

The Primary Index (PI) is the most important index in all of Teradata. The performance of Teradata can be linked directly to the selection of this index. The data value in the PI column(s) is submitted to the hashing function. The resulting row hash value is used to map the row to a specific AMP for data distribution and storage.

To illustrate this concept, I have on several occasions used two decks of cards. Imagine if you will, fourteen people in a room. To the largest, most powerful looking man in the room, you give one of the decks of cards. His large hands allow him to hold all fifty-two cards at one time, with some degree of success. The cards are arranged with the ace of spades continuing through the king of spades in ascending order. After the spades, are the hearts, then the clubs and last, the diamonds. Each suit is arranged starting with the ace and ascending up to the king. The cards are partitioned by suit.

The other deck of cards is divided among the other thirteen people. Using this procedure, all cards with the same value (i.e. aces) all go to the same person. Likewise, all the deuces, treys and subsequent cards each go to one of the thirteen people. Each of the four cards will be in the same order as the suits contained in the single deck that went to the lone man: spades, hearts, clubs and diamonds. Once all the cards have been distributed, each of the thirteen people will be holding four cards of the same value (4*13=52). Now, the game can begin.

The requests in this game come in the form of "give-me," one or more cards.

To make it easy for the lone player, we first request: give-me the ace of spades. The person with four aces finds their ace, as does the lone player with all 52 cards, both on the top other their cards. That was easy!

As the difficulty of the give-me requests increase, the level of difficulty dramatically increases for the lone man. For instance, when the give-me request is for all of the twos, one of the thirteen people holds up all four of their cards and they are done. The lone man must locate the 2 of spades between the ace and trey. Then, go and locate the 2 of hearts, thirteen cards later between the ace and trey. Then, find the 2 of clubs, thirteen cards after that, as well as the 2 of diamonds, thirteen cards after that to finally complete the request.

Another request might be give-me all of the diamonds. For the thirteen people, each person locates and holds up one card of their cards and the request is finished. For the lone person with the single deck, the request means finding and holding up the last thirteen cards in their deck of fifty-two. In each of these give-me requests, the lone man had to negotiate all fifty two cards while the thirteen other people only needed to determine which of the four cards applied to the request, if any. This is the same procedure used by Teradata. It divides up the data like we divided up the cards.

As illustrated, the thirteen people are faster than the lone man. However, the game is not limited to thirteen players. If there were 26 people who wished to play on the same team, the cards simply need to be divided or distributed differently.

When using the value (ace through king) there are only 13 unique values. In order for 26 people to play, we need a way to come up with 26 unique values for 26 people. To make the cards more unique, we might combine the value of the card (i.e. ace) with the color. Therefore, we have two red aces and two black aces as well as two sets for every other card. Now when we distribute the cards, each of the twenty-six people receives only two cards instead of the original four. The distribution is still based on fifty-two cards (2 times 26).

At the same time, 26 people is not the optimum number for the game. Based on what has been discussed so far, what is the optimum number of people?

With this many people, each person has one and only one card. Any time a give-me is requested of the participants, their one card either qualifies or it does not. It doesn't get any simpler or faster than this situation.

As easy as this sounds, to accomplish this distribution the value of the card alone is not sufficient to manifest 52 unique values. Neither is using the value and the color. That combination only gives us a distribution of 26 unique values when 52 unique values are desired.

To achieve this distribution we need to establish still more uniqueness. Fortunately, we can use the suit along with the value. Therefore, the ace of spades is different than the ace of hearts, which is different from the ace of clubs and the ace of diamonds. In other words, there are now 52 unique identities to use for distribution.

To relate this distribution to Teradata, one or more columns of a table are chosen to be the Primary Index.

Primary Index

The Primary Index can consist of up to sixteen different columns. These columns, when considered together, provide a comprehensive technique to derive a Unique Primary Index (UPI, pronounced as "you-pea") value as we discussed previously regarding the card analogy. That is the good news.

To store the data, the value(s) in the PI are hashed via a calculation to determine which AMP will own the data. The same data values always hash the same row hash and therefore are always associated with the same AMP.

The advantage to using up to sixteen columns is that row distribution is very smooth or evenly based on unique values. This simply means that each AMP contains the same number of rows. At the same time, there is a downside to using several columns for a PI. The PE needs every data value for each column as input to the hashing calculation to directly access a particular row. If a single column value is missing, a full table scan will result because the row hash cannot be recreated. Any row retrieval using the PI column(s) is always an efficient, one AMP operation.

Although uniqueness is good in most cases, Teradata does not require that a UPI be used. It also allows for a Non-Unique Primary Index (NUPI, pronounced as new-pea). The potential downside of a NUPI is that if several duplicate values (NUPI dups) are stored, they all go to the same AMP. This can cause an uneven distribution that places more rows on some of the AMPs than on others. This means that any time an AMP with a larger number of rows is involved, it has to work harder than the other AMPs. The other AMPs will finish before the slower AMP. The time to process a single user request is always based on the slowest AMP. Therefore, serious consideration should be used when making the decision to use a NUPI.

Every table must have a PI and it is established when the table is created. If the CREATE TABLE statement contains: UNIQUE PRIMARY INDEX( <column-list> ), the value in the column(s) will be distributed to an AMP as a UPI. However, if the statement reads: PRIMARY INDEX ( <column-list> ), the value in the column(s) will be distributed as a NUPI and allow duplicate values. Again, all the same values will go to the same AMP.

If the DDL statement does not specify a PI, but it specifies a PRIMARY KEY (PK), the named column(s) are used as the UPI. Although Teradata does not use primary keys, the DDL may be ported from another vendor's database system.

A UPI is used because a primary key must be unique and cannot be null. By default, both UPIs and NUPIs allow a null value to be stored unless the column definition indicates that null values are not allowed using a NOT NULL constraint.

Now, with that being said, when considering JOIN accesses on the tables, sometimes it is advantageous to use a NUPI. This is because the rows being joined between tables must be on the same AMP. If they are not on the same AMP, one of the rows must be moved to the same AMP as the matching row. Teradata will use one of two different strategies to temporarily move rows. It can copy all needed rows to all AMPs or it can redistribute them using the hashing mechanism on the column defined as the join domain that is a PI. However, if neither join column is a PI, it might be necessary to redistribute all participating rows from both tables by hash code to get them together on a single AMP.

Planning data distribution, using access characteristics, can reduce the amount of data movement and therefore improve join performance. This works fine as long as there is a consistent number of duplicate values or only a small number of duplicate values. The logical data model needs to be extended with usage information in order to know the best way to distribute the data rows. This is done during the physical implementation phase before creating tables.

Secondary Index

A Secondary Index (SI) is used in Teradata as a way to directly access rows in the data, sometimes called the base table, without requiring the use of PI values. Unlike the PI, an SI does not effect the distribution of the data rows. Instead, it is an alternate read path and allows for a method to locate the PI value using the SI. Once the PI is obtained, the row can be directly accessed using the PI. Like the PI, an SI can consist of up to 16 columns.

In order for an SI to retrieve the data row by way of the PI, it must store and retrieve an index row. To accomplish this Teradata creates, maintains and uses a subtable. The PI of the subtable is the value in the column(s) that are defined as the SI. The "data" stored in the subtable row is the previously hashed value of the real PI for the data row or rows in the base table. The SI is a pointer to the real data row desired by the request. An SI can also be unique (USI, pronounced as you-sea) or non-unique (NUSI, pronounced as new-sea).

The rows of the subtable contain the row hashed value of the SI, the actual data value(s) of the SI, and the row hashed value of the PI as the row ID. Once the row ID of the PI is obtained from the subtable row, using the hashed value of the SI, the last step is to get the actual data row from the AMP where it is stored. The action and hashing for an SI is exactly the same as when starting with a PI. values can generate small hash values and small data values can produce large hash values. So, to overcome the issue associated with a hashed value, there is a range feature called Value Ordered NUSIs. At this time, it may only be used with a four byte or smaller numeric data column. Based on its functionality, a Value Ordered NUSI is perfect for date processing.