Types of HASH functions used in Teradata?

Types of HASH functions used in Teradata?

SELECT HASHAMP (HASHBUCKET (HASHROW ())) AS “AMP#”, COUNT (*) FROM TABLE_NAME

GROUP BY 1 ORDER BY 2 DESC;

There are HASHROW, HASHBUCKET, HASHAMP and HASHBAKAMP.

The SQL hash functions are:

Ø  HASHROW (column(s))

Ø  HASHBUCKET (hashrow)

Ø  HASHAMP (hashbucket)

Ø  HASHBAKAMP (hashbucket)

Example:

Create Table emp

(

ID BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY

           (START WITH 1

            INCREMENT BY 1

            MINVALUE -999999999999999999

            MAXVALUE 999999999999999999

            NO CYCLE),

empname varchar(20),

empdept varchar(10)

)

PRIMARY INDEX(ID);

insert into emp(empname,empdept) values('D','X');

insert into emp(empname,empdept) values('C','X');

insert into emp(empname,empdept) values('B','X');

insert into emp(empname,empdept) values('A','X');

insert into emp(empname,empdept) values('A','X');

SELECT

            HASHROW (EMP.empname)   AS "Hash Value"

            , HASHBUCKET (HASHROW (EMP.empname)) AS "Bucket Num"

            , HASHAMP (HASHBUCKET (HASHROW (EMP.empname))) AS "AMP Num"

            , HASHBAKAMP (HASHBUCKET (HASHROW (EMP.empname)))  AS "AMP Fallback Num"

            ,EMP.empname

FROM EMP;

This is really good, by looking into the result set of above written 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