Tuesday, February 15, 2011

List types of HASH functions used in Teradata?


List types of HASH functions used in Teradata?
Answer:

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

3 comments:

  1. Detailed Explanation......
    They are 4 Type of Hash Function.

    1.HashRow:-
    Returns the rowhash value of a given sequence of expressions in BYTE(4) data type.
    ex:-
    SEL HASHROW(department_number)FROM DEPT
    ---------------------------------------
    79EAC67E
    02C65E9D
    08E241A3
    9252398E
    BBDC9616
    93604E46
    4FCC0A29
    552A06BC
    C1F8791C

    2.HashBucket
    Returns the bucket number that corresponds to a HashRow in INTEGER data type.
    EX:-
    SEL HASHBUCKET(HASHROW(department_number)) FROM DEPT
    ---------------------------------------
    20428
    49656
    31210
    710
    37458
    2274
    21802
    48092
    3.HashAMP
    Returns the identification number of the virtual AMP for the primary data row’s HashBucket in INTEGER data type.
    EX:-
    SELECT Department_Number,
    HashAMP(HashBucket(HashRow(Department_Number)))
    AS PrimaryAMP
    FROM Department
    ORDER BY 2;

    Department_Number PrimaryAMP
    ----------------- -----------
    501 1
    403 3
    301 3
    402 6
    201 9
    302 10
    600 11
    401 13
    100 14
    3.HashBakAMP
    Returns the identification number of the virtual AMP for the fallback data row’s HashBucket in INTEGER data type.
    EX:-

    SELECT Department_Number,
    HashBakAMP(HashBucket(HashRow(Department_Number)))
    AS FbAMP
    FROM Department
    ORDER BY 2;

    Department_Number FbAMP
    ----------------- -----------
    501 0
    403 2
    301 2
    402 7
    201 8
    600 10
    302 11
    401 12
    100 15

    ReplyDelete
  2. I really appreciate information shared above. It’s of great help. If someone want to learn Online (Virtual) instructor lead live training in Julia
    , kindly contact us http://www.maxmunus.com/contact
    MaxMunus Offer World Class Virtual Instructor led training on Julia . We have industry expert trainer. We provide Training Material and Software Support. MaxMunus has successfully conducted 100000+ trainings in India, USA, UK, Australlia, Switzerland, Qatar, Saudi Arabia, Bangladesh, Bahrain and UAE etc.
    For Demo Contact us:
    Name : Arunkumar U
    Email : arun@maxmunus.com
    Skype id: training_maxmunus
    Contact No.-+91-9738507310
    Company Website –http://www.maxmunus.com



    ReplyDelete