{"id":28529,"date":"2022-03-10T20:30:42","date_gmt":"2022-03-10T20:30:42","guid":{"rendered":"https:\/\/www.devopsschool.com\/blog\/?p=28529"},"modified":"2022-12-23T06:47:55","modified_gmt":"2022-12-23T06:47:55","slug":"secondary-index-in-teradata","status":"publish","type":"post","link":"https:\/\/www.devopsschool.com\/blog\/secondary-index-in-teradata\/","title":{"rendered":"Secondary Index in Teradata"},"content":{"rendered":"\n
#<\/th>Contents<\/th><\/tr><\/thead>
1.<\/td>What is Secondary Index in Teradata?<\/td><\/tr>
2.<\/td>When we can Define Secondary Index?<\/td><\/tr>
3.<\/td>Types of Secondary Index<\/td><\/tr>
4.<\/td>Unique Secondary Index (USI)<\/td><\/tr>
5.<\/td>Non Unique Secondary Index (NUSI)<\/td><\/tr>
6.<\/td>Value-Ordered NUSI<\/td><\/tr>
7.<\/td>Advantage and Disadvantage of Secondary Index<\/td><\/tr>
8.<\/td>Primary\u00a0Index vs. Secondary Index<\/td><\/tr>
9.<\/td>Additional Topic<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

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

To Understand More About Secondary Index in Teradata we have to go through below points:<\/p>\n\n\n\n

There are 3 general ways to access a table:<\/p>\n\n\n\n

  • Primary Index access (one AMP access)<\/li>
  • Secondary Index access (two or all AMP access)<\/li>
  • Full Table Scan (all AMP access)<\/li><\/ul>\n\n\n\n

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

    What is Secondary Index in Teradata?<\/h2>\n\n\n\n

    A secondary index provides an alternate pathto the rows of a table.<\/p>\n\n\n\n

    A secondary index can be used to maintain uniquenesswithin a column or set of columns.<\/p>\n\n\n\n

    A table can have from 0 to 32 secondary indexes.<\/p>\n\n\n\n

    Secondary Indexes:<\/strong><\/p>\n\n\n\n

    • Do not affect table distribution.<\/li>
    • Add overhead, both in terms of disk space and maintenance.<\/li>
    • May be added or dropped dynamically as needed.<\/li>
    • Are chosen to improve table performance.<\/li><\/ul>\n\n\n\n

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

      When we can Define Secondary Index?<\/h3>\n\n\n\n

      A Secondary Index may be defined:<\/p>\n\n\n\n

      • At table creation (CREATE TABLE)<\/li>
      • Also can define after table creation Using (CREATE INDEX)<\/li>
      • may be up to 64 columns<\/li><\/ul>\n\n\n\n

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

        Types of Secondary Index<\/h2>\n\n\n\n

        There are two types of secondary index<\/strong>:<\/p>\n\n\n\n

        • Unique Secondary Index (USI)<\/li>
        • Non Unique Secondary Index (NUSI)<\/li><\/ul>\n\n\n\n

          Unique Secondary Index (USI)<\/strong><\/p>\n\n\n\n

          • If the index choice of column(s) is unique, it is called a USI. <\/li>
          • Unique Secondary Index Accessing a row via a USI is a 2AMP operation.<\/li><\/ul>\n\n\n\n

            Syntax to create USI:<\/strong><\/p>\n\n\n\n

            CREATE UNIQUE INDEX (EmpNumber) ON Employee;<\/em><\/p>\n\n\n\n

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

            Non<\/strong> Unique Secondary Index (NUSI)<\/strong><\/p>\n\n\n\n

            • If the index choice of column(s) is non-unique, it is called a NUSI.<\/li>
            • Non-Unique Secondary Index Accessing row(s) via a NUSI is an all AMP operation<\/li><\/ul>\n\n\n\n

              Syntax to create USI:<\/strong><\/p>\n\n\n\n

              CREATE INDEX (LastName) ON Employee;<\/em><\/p>\n\n\n\n

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

              Unique Secondary Index (USI) <\/h2>\n\n\n\n

              A Subtable is created on each AMP anytime a secondary index is created. A secondary index\u00a0requires a\u00a0separate physical structure<\/strong>\u00a0(the\u00a0subtable\u00a0).<\/p>\n\n\n\n

              What\u00a0is in a Unique Secondary Index (USI) Subtable?<\/strong><\/p>\n\n\n\n

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

              The USI Subtable contains two columns:<\/p>\n\n\n\n

              1. Emp_No (The USI column)<\/p>\n\n\n\n

              2. Row-ID\u00a0of the real Primary Index of the base table<\/p>\n\n\n\n

              Inside the secondary index subtable is the column Emp_No (USI Column). Then, there is the corresponding Row-ID of the real Primary Index of the table. The USI subtable\u2019s Primary Index is Emp_No, and it is hashed and has a Row-ID like a normal table.<\/p>\n\n\n\n

              A\u00a0Unique Secondary Index (USI) Subtable is hashed<\/strong>:<\/p>\n\n\n\n

              \"\"<\/figure>\n\n\n\n
              • The USI Subtable spreads the rows evenly among the AMPs. The Primary Index of the Subtable is Emp_No (The USI column).<\/li>
              • Subtable rows are hashed by their Primary Index (Emp_No) and distributed evenly. They contain the Row-ID of the base row, thus providing a pointer to the AMP and row.<\/li><\/ul>\n\n\n\n

                How\u00a0the Parsing Engine uses the USI Subtable<\/strong>:<\/p>\n\n\n\n

                \"\"<\/figure>\n\n\n\n
                • A\u00a0USI is a Two-AMP Operation.<\/li>
                • The first AMP is assigned to read the subtable and the second the base table. <\/li>
                • Two binary searches are performed in total, and one row is returned.<\/li><\/ul>\n\n\n\n

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

                  Non Unique Secondary Index (NUSI)<\/h2>\n\n\n\n
                  \"\"<\/figure>\n\n\n\n

                  A Subtable is created on each AMP. <\/p>\n\n\n\n

                  What\u00a0is in a Unique Secondary Index (USI) Subtable?<\/strong><\/p>\n\n\n\n


                  The NUSI Subtable only contains two columns:<\/p>\n\n\n\n

                  • First_Name\u00a0(The NUSI column)<\/li>
                  • Row-ID\u00a0of the real Primary Index of the base table<\/li><\/ul>\n\n\n\n

                    The NUSI rows get their own Row-ID, but they are not hashed to different AMPs and stay AMP local. That is why NUSI know as AMP locally.<\/p>\n\n\n\n

                    Non-\u00a0Unique Secondary Index (NUSI) Subtable is AMP Local<\/strong><\/p>\n\n\n\n

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

                    The NUSI Subtable is AMP local because all values in the subtable are of those in the base table (on the same AMP).<\/p>\n\n\n\n

                    How\u00a0the Parsing Engine uses the NUSI Subtable<\/strong>:<\/p>\n\n\n\n

                    \"\"<\/figure>\n\n\n\n
                    • First_Name is a Non-Unique Secondary Index!<\/li>
                    • The Parsing Engine will order each AMP to check if they have a \u2018Kyle\u2019 in their NUSI Subtable.<\/li>
                    • Each AMP will simultaneously perform a binary search on their NUSI subtable.<\/li>
                    • If an AMP has a \u2018Kyle\u2019 the PE will order them to retrieve the base row (AMP Local).<\/li>
                    • If there are 50 AMPs then all 50 AMPs perform a binary search simultaneously and if they find a \u201cKyle\u201d they perform another binary search on their local base table.<\/li><\/ul>\n\n\n\n

                      Note:<\/strong> \u00a0A NUSI query always searches all AMPs, but the intent is not to do a Full Table Scan. If there are 50 AMPs, then a minimum of 50 binary searches are done.<\/p>\n\n\n\n

                      Drop Secondary Index<\/strong>:<\/p>\n\n\n\n

                      • Dropping named secondary indexes<\/li><\/ul>\n\n\n\n

                        DROP INDEX indexname ON tutorial_db.employee; <\/p>\n\n\n\n

                        • Dropping unnamed secondary indexes<\/li><\/ul>\n\n\n\n

                          DROP INDEX(co) ON tutorial_db.employee;<\/p>\n\n\n\n

                          Value-Ordered NUSI<\/h2>\n\n\n\n

                          It is called a Value Ordered NUSI because subtable is sorted numerically based on actual column value instead being sorted by Secondary Index Value hash.Works best for the range queries.Can be defined only on ‘numeric’ & ‘date’ data types.<\/p>\n\n\n\n

                          Creating\u00a0a Value-Ordered NUSI<\/strong><\/p>\n\n\n\n

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

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

                          Advantage and Disadvantage of Secondary Index<\/h2>\n\n\n\n

                          Advantage<\/strong><\/p>\n\n\n\n

                          • A table can have up to 32 secondary indexes (including join\/hash indexes).<\/li>
                          • Maximum of 64 columns can be added in a single secondary index for a table.<\/li>
                          • Does not affect data distribution for table and can be added or dropped as needed.<\/li>
                          • Improves table access performance by providing alternative path.<\/li><\/ul>\n\n\n\n

                            Disadvantage<\/strong><\/p>\n\n\n\n

                            • Secondary index on multiple columns is less usable.Index would be used only when all columns are used in the WHERE clause.<\/li>
                            • Adds overhead, both in terms of disk space and maintenance.<\/li><\/ul>\n\n\n\n

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

                              Primary\u00a0Index vs. Secondary Index<\/h2>\n\n\n\n
                              \"\"<\/figure>\n\n\n\n

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

                              Additional Topic<\/h2>\n\n\n\n

                              Full Table Scans<\/h3>\n\n\n\n

                              A full table scan is another way to access data without using any Primary or Secondary Indexes.<\/p>\n\n\n\n

                              Every row of the table must be read.<\/p>\n\n\n\n

                              Full table scans typically occur when either:<\/p>\n\n\n\n

                              • An index is not used in the query<\/li>
                              • An index is used in a non-equality<\/li><\/ul>\n","protected":false},"excerpt":{"rendered":"

                                # Contents 1. What is Secondary Index in Teradata? 2. When we can Define Secondary Index? 3. Types of Secondary Index 4. Unique Secondary Index (USI) 5. Non Unique Secondary… <\/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-28529","post","type-post","status-publish","format-standard","hentry","category-teradata"],"_links":{"self":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/28529","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=28529"}],"version-history":[{"count":1,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/28529\/revisions"}],"predecessor-version":[{"id":28541,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/28529\/revisions\/28541"}],"wp:attachment":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/media?parent=28529"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/categories?post=28529"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/tags?post=28529"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}