Teradata Space Consumption Queries

Space Consumption

In order to find the space consumed by tables and database and also to find out the free space available, following queries will be useful. There are different queries to find each of the following.

1. Total Space Available in a Database
2. Used Space in Database
3. Free Space in Database
4. Space Consumed by each Tables
5. Space Consumed by each table by the creator name.

We will make use of some of the system tables which stores the space information.

Total Space, Used Space and Free Space available in Database :

Below query gives the total space consumed by the database and free space available. This helps to identify whether the database is nearing its full capacity so that we can add additional space or clean up some space.

SELECT
        DATABASENAME DATABASE_NAME,
        SUM(MAXPERM)/(1024*1024*1024) TOTAL_SPACE_IN_GB,
        SUM(CURRENTPERM)/(1024*1024*1024) USED_SPACE_IN_GB,
        TOTAL_SPACE_IN_GB-USED_SPACE_IN_GB AS FREE_SPACE_IN_GB
 FROM
        DBC.DISKSPACE
 WHERE
        DATABASENAME ='<DATABASENAME>'
GROUP BY 1;

Size of Each Tables:

Below query is used to find the size of each table or the space consumed by each table. This will be useful to find the bigger tables in Database and can be identified if the table is needed or not so that space can be freed up.

SELECT      
DATABASENAME,
TABLENAME,
SUM(CURRENTPERM/(1024*1024*1024)) AS "TABLE SIZE IN GB"
FROM
DBC.TABLESIZE
WHERE
DATABASENAME = <'DATABASE_NAME'> AND TABLENAME = <'TABLE_NAME'>
GROUP BY 1,2;

Note: Remove the Tablename filter, if you want to find the table sizes of all the tables in the database.

TableSize by Creator/Owner Name:

This will help us to identify the tables created by other users in the database and check with them if the table is needed or not so that it can be dropped for getting free space.

SELECT        
S.DATABASENAME,
S.TABLENAME,
T.CREATORNAME,
SUM(CURRENTPERM/(1024*1024*1024)) AS "TABLE SIZE IN GB"
FROM
DBC.TABLESIZE  S
JOIN
DBC.TABLES  T
ON
S.TABLENAME=T.TABLENAME AND S.DATABASENAME=T.DATABASENAME
WHERE
S.DATABASENAME = <'DATABASE_NAME'>
GROUP BY 1,2,3;

Teradata Locks

TERADATA LOCKS

Locking in Teradata is automatic and cannot be turned off for normal tables. There are four types of locks that are used and they are:

Types of Lock EXCLUSIVE

WRITE

READ ACCESS

EXCLUSIVE	The resource is temporarily owned. Not available to users until the lock is released. This is normally obtained at the database or table level when the structure/definition is being modified.
WRITE	A data block has been retrieved from disk with the intention of modifying one or more rows. The block  containing the modified data will be written back to disk.
READ	A data block has been retrieved from disk. No changes will be made to the data.
ACCESS	Affectionately called a Dirty Read lock. Allows a SELECT to read data that is locked for WRITE. It is a very minimal form of locking.

This chart shows the automatic locking in Teradata respective to SQL commands:    Type of Lock  Caused by Locks Blocked for Other Users EXCLUSIVE DDL EXCLUSIVE, WRITE, READ, ACCESS WRITE INSERT , UPDATE, DELETE EXCLUSIVE, WRITE, READ  READ SELECT EXCLUSIVE, WRITE ACCESS   EXCLUSIVE

The resource that is locked depends on the SQL command requested by the user.The lock may be set at the database, view, table, or row level. This chart shows the impact of these levels of locking: If Locked at          Resource(s) unavailable to other users    DATABASE     All tables, views, macros and triggers owned by the database/user.       VIEW     All tables referenced in the View.      TABLE     All rows in the table.       ROW     All rows with the same row hash. All SQL commands automatically request a lock. The Teradata RDBMS attempts to lock the resource at the lowest level possible. The lowest level is a row lock. However, Teradata places more importance on performance than resource availability. This implies that the optimizer has the last say in the locking level that is used. For instance, an UPDATE has the option of locking at the table or row level. The optimizer knows that when an entire table is locked, all other users must wait to read even a single row from the table. However, when only a row is WRITE locked, other users still have access to the table, and only have to wait if they need to read the row currently locked. Therefore, normally row level locks are preferable so that rows have a maximum availability for users. This is especially important if another user is requesting a UPI value not used in the UPDATE. This type of locking provides more opportunity for concurrency of user requests and better overall performance. However, the optimizer also knows when all rows in a table are going to be changed. It could follow the row locking to allow as much access as possible. However, eventually all rows are locked. Also, it knows that to lock a row and then read a row over and over again takes longer than locking the table once, reading all rows as fast as possible, and then releasing all locks at once. A full table scan needs all rows. Therefore, the normal row level lock will be escalated to a table level lock for speed on a full table scan. Additionally, by locking the table, it eliminates the potential for a deadlock between multiple user requests. Regardless of the approach to locking that the optimizer chooses, eventually all access to locked rows is denied for a period of time. The chart in Figure 16-2 indicates that a WRITE lock blocks other WRITE locks requested by other users. Additionally all READ lock requests are also blocked because the current data is being changed and therefore, not available until it is finished. This is where the ACCESS lock can be useful. It is also seen in Figure 16-2 that the WRITE lock does not block an ACCESS lock. Therefore, a user can request an ACCESS lock for a SELECT instead of the default READ lock. This does however mean that the data read may or may not be the latest version. Hence, the nickname “Dirty Read.” This is commonly done in views.  To request a locking change, the LOCKING FOR modifier can be used. It is written ahead of the SQL statement to modify the way it executes. These are the various syntax formats of the LOCKING Modifier: LOCKING [<table-name>] FOR <desired-locking> [NOWAIT] LOCKING ROW FOR <desired-locking> LOCKING DATABASE <database-name> FOR <desired-locking> LOCKING VIEW <view-name> FOR <desired-locking> LOCKING TABLE <table-name> FOR <desired-locking> The first syntax listed above defaults to a ROW level lock using the desired lock. So, the first two LOCKING requests do the same thing. To make the command shorter, the LOCKING can be abbreviated to LOCK. The first syntax format also shows the NOWAIT option. It indicates that if a resource is not available the statement should not wait. Instead, it will instantly ABORT. There is another option called MODE that can be used. However, it does not do anything and is there strictly for compatibility with DB/2 SQL. Lastly, also for compatibility, the FOR can be changed to an IN. The NOWAIT is available for all locking requests. The other specification in the above formats is used for specifying the database object to lock. In reality, multiple LOCKING modifiers might exist on a single SELECT. For instance, in a join operation an ACCESS lock might be requested for one table and not the other. This chart shows which SQL commands can use the modifier: Modifier SQL allowed with Modifier Level EXCLUSIVE INSERT,UPDATE,DELETE,SELECT Table, View or Row WRITE SELECT Table, View or Row  READ SELECT Table, View or Row ACCESS SELECT Table, View or Row The above chart shows that the SELECT is the only command that can use the LOCKING FOR modifier for anything other than EXCLUSIVE. This is because most locks cannot be downgraded to a lesser lock, without causing potential data integrity issues. Since the SELECT is not changing data, it can be downgraded safely. It is very common to use the ACCESS locking when creating a view. Since most views only SELECT rows, a WRITE lock is not needed. Plus, if maintenance is being performed on a table, selecting rows using a view with an ACCESS lock is not delayed due to a WRITE lock. So, users are happy and don’t call to complain that the “system is slow.” Another time to use the LOCKING modifier is for multi-step transactions. Consider this situation: The first step is a SELECT and obtains a READ lock. This lock allows other users to also SELECT from the table with a READ lock. Then, the next step of the transaction is an UPDATE. It must now upgrade the READ lock to a WRITE lock. This upgrade of the lock cannot occur while other users have a READ lock on the resource. Therefore, the transaction must wait for the READ locks to disappear. This might dramatically increase the time to complete the maintenance transaction. Therefore, by upgrading the initial default of a READ lock to a WRITE lock for the SELECT it eliminates the potential for a delay in the middle of the transaction. The next SELECT uses the ACCESS lock, common in a View: EXPLAIN LOCKING ROW FOR ACCESS WHERE customer_name LIKE ‘Billy%’ ; Explanation  1) First, we lock MIKEL.Customer_table for access.  2) Next, we do an all-AMPs RETRIEVE step from MIKEL.Customer_table by    way of an all-rows scan with a condition of (    "MIKEL.Customer_table.Customer_name LIKE 'Billy%'") into Spool 1,    which is built locally on the AMPs. The size of Spool 1 is    estimated with no confidence to be 4 rows. The estimated time for    this step is 0.15 seconds.  3) Finally, we send out an END TRANSACTION step to all AMPs involved    in processing the request.  -> The contents of Spool 1 are sent back to the user as the result of    statement 1. The total estimated time is 0.15 seconds. Since the locking modifier can name the table, each table may use different locking when multiple tables are referenced in the same SQL statement.

Teradata Secondary Index

Secondary Indexes

Secondary indexes are similar to Primary Index because they define a path that will deliver the data quickly to meet the users’ expected goals. A secondary index is an alternate path to the data. They can be defined as a Unique Secondary Index (USI) or a Non-Unique Secondary Index (NUSI). Without any secondary indexes your data warehouse could be skating on thin ice!

When it comes to working with large amounts of data that is centrally located, demands for performance to access this data is key. So what can a user do to influence the way data is accessed? The first rule of thumb, which is essential when it comes to working with centralized databases today, is to know your data. Second, understand how Teradata manages data distribution and what a user can do to enhance performance. A query that utilizes a Primary Index in the WHERE column is the fastest path to the data. A query that utilizes a Secondary Index will provide an alternate path to the data and be the second fastest access method. This chapter is dedicated to secondary indexes.

Secondary Indexes

Secondary Indexes provide another path to access data. Let’s say that you were planning a road trip to your hometown. To determine the best way to get there, you need to utilize a map. This map will give you many alternatives to plan your trip. In this case, you need to get their ASAP. So you choose the best route to get there in the shortest period of time. Secondary indexes work very similar to this above example because they provide another path to the data. Teradata allows up to 32 secondary indexes per table. Keep in mind; row distribution of records does not occur when secondary indexes are defined. The value of secondary indexes is that they reside in a subtable and are stored on all AMPs, which is very different from how the primary indexes (part of base table) are stored. Keep in mind that Secondary Indexes (when defined) do take up additional space.

Secondary Indexes are frequently used in a WHERE clause. The Secondary Index can be changed or dropped at any time. However, because of the overhead for index maintenance, it is recommended that index values should not be frequently changed.

There are two different types of Secondary Indexes, Unique Secondary Index (USI), and Non-Unique Secondary Index (NUSI). Unique Secondary Indexes are extremely efficient. A USI is considered a two-AMP operation. One AMP is utilized to access the USI subtable row (in the Secondary Index subtable) that references the actual data row, which resides on the second AMP.

A Non-Unique Secondary Index is an All-AMP operation and will usually require a spool file. Although a NUSI is an All-AMP operation, it is faster than a full table scan.

Secondary indexes can be useful for:

·       Satisfying complex conditions

·       Processing aggregates

·       Value comparisons

·       Matching character combinations

·       Joining tables

Below is a general illustration of a secondary index subtable row:

First Row : Secondary Index Subtable Columns 

Second Row: Secondary Index Column Length

Secondary Index Value   Secondary Index Row Id Primary Index Row ID

Actual Length            8 Bytes                          8 Bytes

Unique Secondary Index (USI)

A Unique Secondary Index (USI) is a two-AMP operation that will always return a maximum of one row. An USI will make your queries burn with speed. When a user creates a Unique Secondary Index, Teradata automatically creates a Secondary Index Subtable. The subtable will contain the:

·       Secondary Index Value

·       Secondary Index Row ID

·       Primary Index Row ID

When a user writes an SQL query that has an USI in the WHERE clause, the Parsing Engine will Hash the Secondary Index Value. The output is the Row Hash, which points to a bucket in the Hash Map. That bucket contains an AMP number and the Parsing Engine then knows which AMP contains the Secondary Index Subtable pertaining to the requested USI information. The PE will direct the chosen AMP to look-up the Row Hash in the Subtable. The AMP will check to see if the Row Hash exists in the Subtable and double check the subtable row with the actual secondary index value. Then, the AMP will pass the Primary Index Row ID back up the BYNET network. This request is directed to the AMP with the base table row, which is then easily retrieved.

USI Subtable Example

When a USI is designated on a table, each AMP will build a subtable. So if you create 32 USI indexes on a table, then each AMP will build 32 separate subtables. Therefore, choose your Secondary Indexes wisely, because space is used when these indexes are created. When a user inputs SQL that utilizes a USI in the WHERE clause, then Teradata will know that either one row or no rows can be returned. Reason, the column in the WHERE clause is unique.

The following example illustrates the how a USI Subtable is created and how it works to speed up queries.

The above example is designed to illustrate in theory exactly how Teradata works with Base Rows and Secondary Index Subtables. Take your time and I will lead you through the key points. Try and remember what this book has already taught you.

First, take a look at the top table. The table name is NAME_TABLE. The table was created with a Non-Unique Primary Index on the column LNAME. Notice that both names of DAVIS have hashed to the same AMP. All NUPI columns with the same value will always hash to the same AMP.

I have also given each base row an abbreviated Row ID. The abbreviated Row ID is four bits for the Row Hash and one bit for the Uniqueness Value separated by a comma. Notice that both names of Davis have the same Row Hash in 0000, but the Uniqueness Values are 1 and 2.

When we decided to CREATE a Unique Secondary Index (USI) on the column EMP, a secondary index subtable is automatically created on every AMP. For each Base Row, a secondary index subtable row is created. It is hash distributed across the AMPs and placed in the secondary index subtable. Here is how that happened.

Each EMP column is run through the Hashing Algorithm and the output is a Row Hash. This Row Hash points to a bucket in the Hash Map, which directs the row to an AMP. That particular AMP is responsible for the secondary index row. Each AMP holds a portion of the secondary index rows, and in theory, each AMP holds an equal amount of secondary index rows.

Notice that each secondary index subtable row (at the bottom of the diagram) has the EMP value, the EMP row hash and uniqueness value (EMP Row ID) and the most important column is the base table Row ID. The base table Row ID is a pointer to the row in the base table.

Now that the secondary index subtable is set up it is ready for action.

Command: Select * from Table where EMP=99

The PE realizes that EMP is a Unique Secondary Index and so the plan is to find the secondary index subtable row, which will then point us directly to the base row.

The PE hashes the value in EMP, which is 99 and the output is the Row Hash. It happens to be 1111. The PE takes the numbers of the Row Hash and it points to a bucket in the Hash Map. The bucket contains the AMP number where the secondary index row resides. The AMP number is AMP 1. The PE tells AMP 1 to look in its secondary index subtable for EMP 99, which has a Row Hash of 1111.AMP 1 scans its subtable and finds Row Hash 1111, and then double checks to see if the EMP value is 99. When it finds out both are true, it returns the base table Row ID. That base table Row ID is 0000, 2 and the AMP passes this number up the BYNET. The BYNET knows which AMP holds the base row and sends a message to this AMP to get the row. The row for Davis Ellie 99 is returned to the user.

NUSI Subtable Example

When a NUSI is designated on a table, each AMP will build a subtable. So if you create 32 NUSI indexes on a table, then each AMP will build 32 separate subtables. Therefore, choose your Secondary Indexes wisely, because space is used when these indexes are created. The following example illustrates the how a NUSI Subtable is created and how it works to speed up queries.

The above example is designed to logically illustrate how Teradata works with base table rows and NUSI subtables. Take your time and I will lead you through the key points. Try and remember what this book has already taught you.

First, take a look at the top table. The table name is NAME_TABLE. The table was created with a Non-Unique Primary Index on the column LNAME. Notice that both names of DAVIS have hashed to the same AMP. All NUPI columns with the same value will always hash to the same AMP.

I have also given each base row an abbreviated Row ID. The abbreviated Row ID is four bits for the Row Hash and one bit for the Uniqueness Value separated by a comma. Notice both names of Davis have the same Row Hash in 0000, but the Uniqueness Values are 1 and 2.

If we decide to CREATE a Secondary Index (NUSI) on the column FNAME, a secondary index subtable was automatically created on every AMP. The FNAME secondary index is a Non-Unique Secondary Index (NUSI).The key points to a NUSI subtable are that each AMP will keep their own subtable. This subtable only holds information about the base rows on that AMP. This is called an AMP local secondary index subtable. It would be like every family keeping a record of their own children. So, each non-unique secondary index subtable on an AMP points to base table rows on that same AMP.

Another interesting thing about NUSI subtables is that for each value only one record is created. Notice in the picture, I have placed arrows pointing to the row on the second AMP. Both people on this AMP have a FNAME of Ray. In the subtable, we show the value of Ray, the Row Hash of Ray (so the AMP can find it quicker), and the base table Row ID for every Ray on that AMP.

Now that the secondary index subtable is set up it is ready for action.

Command: Select * from Table where F_NAME='RAY'

The PE realizes that FNAME is a Non-Unique Secondary Index and so the plan is to have each AMP check to see if they have a Ray in their subtable. For speed, the PE hashes Ray and comes up with a Row Hash of 1111. The PE tells each AMP to look in its secondary index subtable for FNAME Ray, which has a Row Hash of 1111.Each AMP scans its subtable looking for Row Hash 1111, and then double checks to see if the FNAME value is ‘Ray’. If an AMP can’t find the subtable row it knows it has nobody named ‘Ray’ and won’t participate. If an AMP finds a subtable row for ‘Ray’ it will then retrieve all base Row IDs in the table for ‘Ray’.

The rows in the above case are returned for:

Smith Ray 77

Jones Ray 34

Value-Ordered NUSI

A Value-Ordered NUSI is a Non-Unique Secondary Index that can be of any Integer Type (Numeric and Four bytes or less). Value-Ordered NUSIs are created because you want to use the command BETWEEN and look for a range of values. Teradata treats Value-Ordered NUSIs similar to ordinary NUSI indexes, but with one minor difference.

Similarly, when a Value-Ordered NUSI is created Teradata automatically creates and maintains a secondary index subtable. Teradata also keeps the subtables AMP local so each AMP is responsible for keeping track of the base rows it owns.

The only difference is the way each AMP orders the subtable rows in its subtable. The rows will be ordered not in Row Hash sequence, but in order of the values themselves. That way January 1, 2000 comes before January 2, 2000. Dates are an excellent example where Value-Ordered NUSIs make sense. When a user asks for information WHERE the Order_Date is BETWEEN ‘2001/01/01’ and ‘2001/01/30’, each AMP can gather up their rows quickly because they are ordered by the data value. That’s what Order By Values means.

The syntax for creating a Value-Ordered NUSI is:

CREATE INDEX(Dept) ORDER BY VALUES on EMPLOYEE

The requirements for a Value-Ordered Index is the following:

·       Cannot be unique or a Primary Index

·       Must only be used for NUSI‘s

·       It can be updated with SQL or Multiload

·       It can be any integer data type

Secondary Index Summary

·       You can have up to 32 secondary indexes for a table.

·       Secondary Indexes provide an alternate path to the data.

·       The two types of secondary indexes are USI and NUSI.

·       Every secondary index defined causes each AMP to create a subtable.

·       USI subtables are hash distributed.

·       NUSI subtables are AMP local.

·       USI queries are Two-AMP operations.

·       NUSI queries are All-AMP operations, but not Full Table Scans.

·       Value-Ordered NUSIs can be any non-unique index of integer type.

·       Always Collect Statistics on all NUSI indexes.

·       The PE will decide if a NUSI is strongly selective and worth using over a Full Table Scan.

·       Use the Explain function to see if a NUSI is being utilized or if bitmapping is taking place

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.