Below are some of the hard facts about Teradata Database and Limitations between versions.
- Each AMP holds a portion of the rows for every table in the system.
- Each Table has a Primary Index that is Unique or Non-Unique
- The Hash Map Determines which AMP will own the Row
- A Unique Primary Index Spreads the Data Evenly
- The AMP Adds a Uniqueness Value to Create the Row-ID
- Each AMP Sorts Their Tables by the Row-ID
- A Non-Unique Primary Index Skews the Data
- Unique Primary Index Queries are a Single AMP Retrieve
- A Non-Unique Primary Index is also a Single AMP Retrieve
- Teradata has a No Primary Index Table called a NoPI Table
- There are Normal Tables and then There are Partitioned Tables
- Partitioning is Designed to Eliminate the Full Table Scan
- A Partition # and Row-ID = Row Key
- An AMP Stores its Rows Sorted in only Two Different Ways
- AMPs Moves Their Data Blocks into Memory to Read/Write
- Most Taxing thing for an AMP is Moving Blocks into Memory
- Rows are Stored in Data Blocks which are stored in Cylinders
- Rows for an AMP Stored Inside a Data Block in a Cylinder
- An AMP's Master Index is Used to Find the Right Cylinder
- There is One Master Index and Thousands of Cylinder Indexes
- The Row Reference Array is always in Row-ID order
- The maximum block size (before a split) was 255 sectors (127.5 K), but now it is 2047 sectors (1 MB).
- A Block Splits into Two Blocks at Maximum Block Size.
- Once the block grows to the maximum size, then Teradata will split the single block into two separate blocks. The default for the split is 2047 sectors or 1 MB.
- The maximum for all Teradata V14 data block splits are 2047 sectors or 1 MB, but the defaults differ based on whether or not it is an enterprise class system, or an appliance
- Each Table has a 48-bit TableID
|Max Vdisk Size||1.26 TB||1.26 TB||7.2 TB|
|Max Block Size (sectors)||255||255||255|
|Max Table Header Size||128 KB||1 MB||1 MB|
|Max Databases/Users||4.2 Billion||4.2 Billion||4.2 Billion|
|Max Partitions||65,535||65,535||9.2 * 10 18|
|Max Partition Levels||15||15||62|
|Max spool tables per query||2048||2048||2048|
|Max Tables in a Join||64||128||128|
|Max columns in a table||2048||2048||2048|
|Max columns in an index||64||64||64|
|Max SQL request size||1 MB||1 MB||1 MB|
|Max AMPs and PEs||16,384||16,384||32,720|