A Biref Introduction to Pi and Si
A brief introduction to PI and SI
Primary index is the most important feather of Teradata, causing the Primary Index decides the distribution of rows in a table on AMPs.
There are two types of Primary Index in the Teradata Database, unique Primary Index and Non-unique Primary Index. Unique Primary Index results in even row distribution and eliminates the duplicate rows. While Non-Unique Primary Index results in potentially skewed distribution. Non-Unique Index worse, makes heavily-skewed distribution ignored the Teradata Parallel mechanism.
So how the PI decides the distribution of a row of a table on an AMP? The Primary Index value comes into the Hashing Algorithm, and via Hashing Algorithm, the so called 32bit row hash is produced. The first 16 bit is called Row Hash, used to identify the hash map’s entry. The hash map is an array which associates the DSW with a specific AMP. Secondary Index is another alternative way to access the row stored on the AMP. It is a sub-table in all the AMPs. Like Primary Index, there are two kinds of secondary index, Unique Secondary Index and Non-Unique Index. Unlike the Primary Index, Secondary Index does not have a effect on the distribution on AMPs. Let us take the accessing data using secondary index for instance. Unique Secondary Index is efficient. This kind of accessing typically allows of only two AMPs. So using Unique Secondary Index to access a row, two AMPs are involved.
Non-Unique secondary index (NUSI) may have multiple rows per value. A NUSI is efficient when you want to prevent a full table
Primary index is the most important feather of Teradata, causing the Primary Index decides the distribution of rows in a table on AMPs.
There are two types of Primary Index in the Teradata Database, unique Primary Index and Non-unique Primary Index. Unique Primary Index results in even row distribution and eliminates the duplicate rows. While Non-Unique Primary Index results in potentially skewed distribution. Non-Unique Index worse, makes heavily-skewed distribution ignored the Teradata Parallel mechanism.
So how the PI decides the distribution of a row of a table on an AMP? The Primary Index value comes into the Hashing Algorithm, and via Hashing Algorithm, the so called 32bit row hash is produced. The first 16 bit is called Row Hash, used to identify the hash map’s entry. The hash map is an array which associates the DSW with a specific AMP. Secondary Index is another alternative way to access the row stored on the AMP. It is a sub-table in all the AMPs. Like Primary Index, there are two kinds of secondary index, Unique Secondary Index and Non-Unique Index. Unlike the Primary Index, Secondary Index does not have a effect on the distribution on AMPs. Let us take the accessing data using secondary index for instance. Unique Secondary Index is efficient. This kind of accessing typically allows of only two AMPs. So using Unique Secondary Index to access a row, two AMPs are involved.
Non-Unique secondary index (NUSI) may have multiple rows per value. A NUSI is efficient when you want to prevent a full table