1 Answers

Teradata has lived and understood the data warehouse environment for decades over theircompetitors. One of the key fundamentals of the teradata is in the ability to allowthe AMPs to access data quicker with Partition Primary Indexes. Partition PrimaryIndexes are always and I mean always defined in the Physical Model.In the past Teradata has hashed the Primary Index, which produced a Row Hash. Fromthe Row Hash, Teradata was able to send the row to a specific AMP. The AMP wouldplace a uniqueness value and the Row Hash plus the Uniqueness value made up the RowID.The data on each AMP was grouped by table and sorted by ROW ID.

Through years of experience working with data warehouse user queries Teradata hasdecided to take the hashing to an additional level.In the past you could choose a Unique Primary Index (UPI) or a Non-Unique PrimaryIndex (NUPI). Now Teradata will let you choose either a Partition Primary Index (PPI)or a Non-Partition Primary Index (NPPI).

This allows for fantastic flexibility because user queries will often involve ranges or arespecific to a particular department, location, region, or code of some sort. Now theAMPs can find the data quicker because the data is grouped in alphabetical order. Youcan avoid Full Table Scans more often.An example is definitely called for here. I will show you a table that is hashed andanother that has a Partition Primary Index.

Partitioning doesnt have to be part of the Primary Index

understanding partitioning is easy once you understand the basic steps. You do not haveto partition by a column that is the primary index. Here is an example:

You can NOT have a UNIQUE PRIMARY INDEX on a table that is partitioned bysomething not included in the Primary Index.Here is an interesting brain teaser? If a Primary Index is Non-Unique on a PartitionPrimary Index table can Teradata utilize the Primary Index column to access the row orrows? Absolutely! Teradata scans all partitions that have not been eliminated and theHashed Primary Index value is scanned for the Row Hash.

The Bad NEWS about Partitioning on a columnthat is not part of the Primary IndexBefore you get too excited about partitioning by a column that is not part of the primaryindex you should remember The Alamo. This is because when you run queries thatdont mention the PARTITION COLUMN in your SQL you have to check everypartition and this can be some serious battle. Partitions can range from 1-65,535partitions. The example below will have to check every partition so be careful.

GREAT Things about Partition Primary Indexes:PPI avoids full table scans without the overhead of a secondary index and allows forinstantaneous dropping of old data and rapid addition of newer data.Remember these rules: A Primary KEY cant be changed. A Primary Index alwaysdistributes the data. A Partition Primary Index (PPI) partitions data to avoid full tablescans.

Two ways to handle Partitioning on a column thatis not part of the Primary IndexYou have two ways to handle queries when you partition by a column that is not part ofthe Primary Index.a. You can assign a Unique Secondary Index (when appropriate).b. You can include the partition column in your SQL.