Social Icons

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_noBucket_NoBAK_AMP_No
222643993188
646797764594
2411522255892
2440270798894
2531627721894
2549929077195
2940552862893
4011594403895
4011789260286
4056441001485

3 comments: