asked **vishnoiprem ** August 17, 2014 04:53 AM
teradata

How to find AMP Number using Row-Hash value?

add comment

answered By vishnoiprem 0

here are three hash-related functions:

- HASHROW to get the hash

- HASHBUCKET to get the first 16/20-bits as an Integer, the so-called hash bucket number

- HASHAMP to get the AMP on which a row is stored (i.e. a number between 0 and the number of AMPs on your system - 1)

SELECT HASHROW(55) AS hr, HASHBUCKET(hr) AS hb, HASHAMP(hb);

HASHROW/HASHBUCKET return the same values on every TD system using the same hash-algorithm, but HASHAMP returns an AMP-number specific to your system.

So AMP 4 is just an example, on your system it will probably be a different number

- HASHROW to get the hash

- HASHBUCKET to get the first 16/20-bits as an Integer, the so-called hash bucket number

- HASHAMP to get the AMP on which a row is stored (i.e. a number between 0 and the number of AMPs on your system - 1)

SELECT HASHROW(55) AS hr, HASHBUCKET(hr) AS hb, HASHAMP(hb);

HASHROW/HASHBUCKET return the same values on every TD system using the same hash-algorithm, but HASHAMP returns an AMP-number specific to your system.

So AMP 4 is just an example, on your system it will probably be a different number

```
SELECT HASHAMP (HASHBUCKET (HASHROW ())) AS AMP#, COUNT (*) FROM 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:
SELECT
HASHROW ('Teradata') AS "Hash Value"
, HASHBUCKET (HASHROW ('Teradata')) AS "Bucket Num"
, HASHAMP (HASHBUCKET (HASHROW ('Teradata'))) AS "AMP Num"
, HASHBAKAMP (HASHBUCKET (HASHROW ('Teradata'))) AS "AMP Fallback Num" ;
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
```

flag

Write A Tutorials

Question tags

Similar questions

Follow this question

Follow teradata category

Question tags

teradata
× 1

Asked
**2 years and 2 months ago ago**

Number of Views -742

Number of Answers -1

Last updated

**2 years and 2 months ago ago**

Number of Views -742

Number of Answers -1

Last updated

Similar questions