Dear Nagarjun,

Hope you are doing well

1) ViewPoint:
         Viewpoint provides powerful systems management and monitoring across the enterprise for both administrators and business users.

       Overall, Viewpoint makes performance metrics readily available, and you can get a lot of useful information with a lot less effort.

         The Purpose of  COLLECT STATISTICS is to gather and store demographic data for one or more columns or indices of a table or join index.In this process it collects data and stores the summary in the Data Dictionary (DD) inside USER DBC.

The optimizer uses this synopsis data to generate efficient table access and join plans.

Below are the statistics will collect

  •   The number of rows in the table
  •   Examples Advantages All Stats Approach Cash Loans Columns Count
  •   The average row size
  •   Information on all Indexes in which statistics were collected
  •   The range of values for the column(s) in which statistics were collected
  •   The number of rows per value for the column(s) in which statistics were collected
  •   The number of NULLs for the column(s) in which statistics were collected

For more details please follow this link

EXPLAIN statement is the Parsing Engines (PE’s) plan to the AMPs.A step-by-step
analysis of the queries being executed in the database.
The EXPLAIN facility provides an "English" translation of the plan the SQL Optimizer develops to service a request.

The EXPLAIN is an excellent facility for the following activities:
• Determining Access Paths
• Validating the use of indexes
• To determine locking profiles
• Showing Triggers and Join Index access
• Estimate the query runtime

Sample Syntax for Explain is.

EXPLAIN Select Emp_ID, Emp_Name from Emp_Table ;

For more details please follow this link

4) Clustering :

 A cluster is a group of AMPs that act as a single Fallback unit. Clustering has no effect on primary row distribution of the table, b ut the Fallback row will always go to another AMP in the same cluster.

Cluster sizes are set through a Teradata console utility and may range from 2 to 16 AMPs per cluster (not all clusters in a system have to be the same size). The example shows an 8-AMP system set up in two clusters
of 4-AMPs each.

Should an AMP fail, the primary and Fallback row copies stored on that AMP cannot be accessed. However, their alternate copies are available through the other AMPs in the same cluster.

The loss of an AMP in one cluster has no effect upon other clusters. It is possible to lose one AMP in each cluster and still have full access to all Fallback-protected table data. If there are two AMP failures in the same
cluster, the entire Teradata system halts.

While an AMP is down, the remaining AMPs in the cluster must do their own work plus the work of the down AMP. The larger the size of the cluster, the less noticeable the workload increase is within that cluster
when one AMP fails. Large cluster sizes are more vulnerable to a second failure before recovery from the first failure is complete. Remember that a second failure halts the entire Teradata system.

If you had an 8-AMP system with 4 clusters of 2 AMPs each, the system could lose four AMPs (one per cluster) and continue operations. If the system workload is near capacity, there will be some loss of performance

5) Hashing:

Hashing Algorithm :

A row is assigned to a particular AMP based on the primary index value. Teradata uses hashing algorithm to determine which AMP gets the row.

Following is a high level diagram on hashing algorithm.

Following are the steps to insert the data.

  •   The client submits a query.
  •   The parser receives the query and passes the PI value of the record to the hashing algorithm.
  •   The hashing algorithm hashes the primary index value and returns a 32 bit number, called Row Hash.
  •   The higher order bits of the row hash (first 16 bits) is used to identify the hash map entry. The hash map contains one AMP #. Hash map is an array of buckets which contains specific AMP #.
  •   BYNET sends the data to the identified AMP.
  •   AMP uses the 32 bit Row hash to locate the row within its disk.
  •   If there is any record with same row hash, then it increments the uniqueness ID which is a 32 bit number. For new row hash, uniqueness ID is assigned as 1 and incremented whenever a record with same row hash is inserted.
  •   The combination of Row hash and Uniqueness ID is called as Row ID.
  •   Row ID prefixes each record in the disk.
  •   Each table row in the AMP is logically sorted by their Row IDs.

Hash Function : Please refer the link regarding this
6) Check Table: Please refer the link regarding this with an simple example of check table
I hope this will resolve the issue.