Wednesday, September 15, 2010

How can you view Data Distribution in Teradata ?



Teradata uses HASH values to store data in AMPs. To view data distribution we use  Hash Functions.
Hash functions are usually used over primary index columns to find data distribution . We can identify skewness  by using this concept .
Following query can be used to find hash values of PI columns

SELECT HASHAMP(HASHBUCKET(HASHROW(<PRIMARY INDEX>))) AS
"AMP#",COUNT(*)
FROM <TABLENAME>
GROUP BY 1
ORDER BY 2 DESC;

By looking at result ,  you  query you can easily find out the Data Distribution across all AMPs in your system and further you can easily identify un-even data distribution.


definitions :
HASHROW - returns the row hash value for a given value
HASHBUCKET - the grouping of a specific hash value
HASHAMP - the AMP that is associated with the hash bucket

3 comments:

  1. i was searching this like everywhere n u gave it to me...... thank u :)

    ReplyDelete
  2. really helpfull for TD developers :)

    ReplyDelete