HASH Functions

There are four types of hashing functions are available in Teradata.

1. HASHROW      
2. HASHAMP
3. HASHBUCKET   
4. HASHBAKAMP

HASHROW Function 
Basic Synatx for HASHROW is
HASHROW( [ < data-column-value > […, < data-column-value >…] ] )

Example

SELECT    HASHROW (NULL)     AS NULL_HASH
                 ,HASHROW('Anil')        AS Name_HASH
                 ,HASHROW()                 AS NO_HASH ;

• The Query will give same results if you ran again and again.
• The HASHROW function produces the 32-bit binary Row Hash that is stored as part of the data row.
• It returns maximum of 4,294,967,295 unique values.
• The values range from 0 to FFFFFFFF.

General example is
SELECT HASHROW(Emp_no) AS HASHROW_VALUE FROM Emp_table;

Result:
HASHROW_VALUE
------------
717A8724

HASHBUCKET Function

Basic Syntax for HASHBUCKET is

HASHBUCKET( [ ] )

Example
SELECT HASHBUCKET(NULL) AS NULL_BUCKET
                ,HASHBUCKET()    AS NO_BUCKET;

• The HASHBUCKET function produces 16bit binary Hash Bucket  that is used with the Hash Map to determine the AMP that should store and retrieve the data row
• The values range from 0 to 1,048,575,not counting the NULL as a potential result.

General Example
SELECT COUNT(*) AS NBR_ROWS
               ,HASHBUCKET(HASHROW (Emp_No)) AS Bucket_No
               FROM Emp_Table
                GROUP BY 2 ;

NBR_ROWS	Bucket_No
1	388963
1	1560021
1	683238
1	53025
1	1877998
1	795490
1	410221
1	1048185
1	804626
1	317398

HASHAMP Function
Basic Synatx for HASHAMP is
HASHAMP( )

Example
SELECT HASHAMP(NULL) AS NULL_BUCKET
                ,HASHAMP ()    AS NO_BUCKET;

• The HASHAMP function returns the identification number of the primary AMP for any Hash Bucket number.
• When no value is passed to the HASHAMP function, it returns a number that is one less than the number of AMPs in the current system configuration

 General Example

SELECT Emp_no   
       ,HASHBUCKET(HASHROW(Emp_no)) AS Bucket_No
       ,HASHAMP(HASHBUCKET(HASHROW(Emp_no))) AS AMP_No
       FROM Emp_Table
       ORDER BY 1;

Emp_no	Bucket_No	AMP_No
1777	973955	120
4972	93325	41
7524	540321	56
21048	957958	69
21048	957958	69
24402	707988	65
26346	9707	71
27470	471190	15
29157	613408	25
29775	452863	120

HASHBAKAMP Function
Basic Syntax for HASHBAKAMP is 

HASHBAKAMP ( )

ExampleSELECT HASHBAKAMP (NULL) AS NULL_BUCKET
               ,HASHBAKAMP () AS NO_BUCKET;

The HASHBAKAMP function returns the identification number of the Fallback AMP for any Hash Bucket number

General Example
SELECT Emp_no    
       ,HASHBUCKET(HASHROW(Emp_no)) AS Bucket_No
       ,HASHBAKAMP(HASHAMP(HASHBUCKET(HASHROW(Emp_no)))) AS BAK_AMP_No
       FROM Emp_Table
       ORDER BY 1;

Emp_no	Bucket_No	BAK_AMP_No
2226	439931	88
6467	977645	94
24115	222558	92
24402	707988	94
25316	277218	94
25499	290771	95
29405	528628	93
40115	944038	95
40117	892602	86
40564	410014	85