{"id":27721,"date":"2022-02-25T14:09:51","date_gmt":"2022-02-25T14:09:51","guid":{"rendered":"https:\/\/www.devopsschool.com\/blog\/?p=27721"},"modified":"2022-12-23T06:48:39","modified_gmt":"2022-12-23T06:48:39","slug":"hashing-of-the-primary-index","status":"publish","type":"post","link":"https:\/\/www.devopsschool.com\/blog\/hashing-of-the-primary-index\/","title":{"rendered":"Hashing of the Teradata Primary Index"},"content":{"rendered":"\n
#<\/th>Contents<\/th><\/tr><\/thead>
1.<\/td>The Hashing Formula Facts<\/td><\/tr>
2.<\/td>Important Hashing formula Terminology<\/td><\/tr>
3.<\/td>How Hashing Algorithm decides which AMP has the Row?<\/td><\/tr>
4.<\/td>Hashing Theory For Non- Unique Primary Indexes have Skewed Data<\/td><\/tr>
5.<\/td>What Uniqueness Value?<\/td><\/tr>
6.<\/td>What is ROW ID?<\/td><\/tr>
7.<\/td>Why each AMP Sorts their rows by the Row-ID?<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

The Teradata Hashing Formula Facts <\/h2>\n\n\n\n
  • There is one Hashing Formula in Teradata, and it is consistent.<\/li>
  • The concept is to take the value of a row\u2019s Primary Index and run it through the Hash Formula.<\/li>
  • It will produce a Row Hash number.<\/li>
  • That Row Hash will stay with the row forever and reside as the first part of the row.<\/li>
  • The Row Hash also determines which AMP owns the row.<\/li><\/ul>\n\n\n\n
    \"\"<\/figure>\n\n\n\n

    <\/p>\n\n\n\n

    Important Hashing formula Terminology<\/h2>\n\n\n\n

    Row Hash<\/strong> :- The primary Index row goes through Secret hash Formula which gives output number as Row Hash<\/p>\n\n\n\n

    Hash Map<\/strong> :-The Row hash number goes to a bucket in the Hash Map and is assigned to an AMP.<\/p>\n\n\n\n

    Hash Bucket<\/strong>:- If a Row Hash number is 5. Teradata counted over to bucket 5 in the Hash Map, and it has the number two (2) inside that bucket. This means that this row will go to AMP 2.<\/p>\n\n\n\n

    <\/p>\n\n\n\n

    How Hashing Algorithm decides which AMP has the Row?<\/h2>\n\n\n\n

    Hashing Algorithm Process:<\/strong><\/p>\n\n\n\n

    \"\"<\/figure>\n\n\n\n
    • A row will be placed on an AMP after the Parsing Engine (PE) hashes the row\u2019s Primary Index value.<\/li>
    • The output of the Hashing Algorithm is a row\u2019s Row Hash.<\/li>
    • The Row hash goes to a bucket in the Hash Map and is assigned to an AMP.<\/li>
    • The above example hashed Emp_No 1002 (Primary Index value) and the output was a Row Hash of 5. Teradata counted over to bucket 5 in the Hash Map, and it has the number two (2) inside that bucket. This means that this row will go to AMP 2.<\/li>
    • Than for examples if hashed Emp_No 1003 (Primary Index value) and the output was a Row Hash of 13. Teradata counted over to bucket 9 in the Hash Map, and it has the number one (3) inside that bucket. This means that this row will go to AMP 3<\/li><\/ul>\n\n\n\n
      \"\"<\/figure>\n\n\n\n

      <\/p>\n\n\n\n

      <\/p>\n\n\n\n

      Hashing Theory For Non- Unique Primary Indexes have Skewed Data<\/h2>\n\n\n\n
      • For Example, if we made Last_Name the Primary Index for a table. Here is an example of how it would distribute. Notice all duplicates have the same Row Hash.<\/li>
      • The Hash Formula is consistent so every Smith has the same Row Hash and the same goes for each Jones and each Patel. Therefore, duplicate values land on the same AMP.<\/li><\/ul>\n\n\n\n
        \"\"<\/figure>\n\n\n\n

        <\/p>\n\n\n\n

        What Uniqueness Value ?<\/h2>\n\n\n\n

        Each AMP will place a Uniqueness Value after the row hash to track duplicate values.<\/p>\n\n\n\n

        \"\"<\/figure>\n\n\n\n

        <\/p>\n\n\n\n

        What is ROW ID?<\/h2>\n\n\n\n

        The Row Hash and Uniqueness Value make up the Row-ID.<\/p>\n\n\n\n

        Row-ID equals the Row Hash of the Primary Index column and the Uniqueness Value<\/p>\n\n\n\n

        \"\"<\/figure>\n\n\n\n

        A Row-ID Example for a Unique Primary Index<\/h3>\n\n\n\n
        \"\"<\/figure>\n\n\n\n

        The two things for this Unique Primary Index (UPI) example:<\/p>\n\n\n\n

        1) The Uniqueness Value on each Row-ID is 1.<\/p>\n\n\n\n

        2) Each AMP sorts their rows by the Row-ID .<\/p>\n\n\n\n

        Row Hash and the Uniqueness Value make up the Row-ID. AMPs sort by the Row-ID.<\/p>\n\n\n\n

        <\/p>\n\n\n\n

        A Row-ID Example for a Non-Unique Primary Index (NUPI)<\/h3>\n\n\n\n
        \"\"<\/figure>\n\n\n\n

        Row Hash and the Uniqueness Value make up the Row-ID. AMPs sort by the Row-ID.<\/p>\n\n\n\n

        <\/p>\n\n\n\n

        Why each AMP Sorts their rows by the Row-ID?<\/h2>\n\n\n\n

        Two Reasons why each AMP Sorts their rows by the Row-ID<\/p>\n\n\n\n

        • AMPs sort rows by Row-ID so like data is grouped together<\/li>
        • For Binary searches<\/li><\/ul>\n\n\n\n
          \"\"<\/figure>\n\n\n\n

          AMPs sort their rows by Row-ID to Group like Data<\/strong><\/p>\n\n\n\n

          All of the Smiths are lumped together because of the sorting by Row-ID.<\/p>\n\n\n\n

          \"\"<\/figure>\n\n\n\n

          AMPs sort their rows by Row-ID to do a Binary Search<\/strong><\/p>\n\n\n\n

          A Binary Search knows the Row-IDs are in numeric order. It’s like you using a phone book. Go to the middle first and then go up or down in chunks to find things quickly.<\/p>\n\n\n\n

          \"\"<\/figure>\n\n\n\n

          Important Point:<\/strong><\/p>\n\n\n\n

          Null Values all Hash to the Same AMP<\/strong><\/em><\/p>If there are NULL values in the Primary Index, you could find this is the reason for your skew. A Table with a Unique Primary Index can have only one Null value, but a NUPI table can have many NULL values, and each NULL value hashes to the same AMP.<\/cite><\/blockquote>\n\n\n\n

          \"\"<\/figure>\n\n\n\n

          <\/p>\n\n\n\n

          <\/p>\n\n\n\n

          Checking data distribution of given columns on the AMP<\/em><\/strong><\/p>SELECT HASHAMP(HASHBUCKET(HASHROW([columnlist]))),COUNT(*) FROM [databasename].[tablename] GROUP BY 1 ORDER BY 2 DESC;<\/cite><\/blockquote>\n\n\n\n

          <\/p>\n","protected":false},"excerpt":{"rendered":"

          # Contents 1. The Hashing Formula Facts 2. Important Hashing formula Terminology 3. How Hashing Algorithm decides which AMP has the Row? 4. Hashing Theory For Non- Unique Primary Indexes have… <\/p>\n","protected":false},"author":48,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_joinchat":[],"footnotes":""},"categories":[7338],"tags":[],"class_list":["post-27721","post","type-post","status-publish","format-standard","hentry","category-teradata"],"_links":{"self":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/27721","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/users\/48"}],"replies":[{"embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/comments?post=27721"}],"version-history":[{"count":5,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/27721\/revisions"}],"predecessor-version":[{"id":27867,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/27721\/revisions\/27867"}],"wp:attachment":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/media?parent=27721"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/categories?post=27721"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/tags?post=27721"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}