15.10 - Examples of Rewrites Using Row Partitioning - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
User Guide
featnum
B035-1142-151K

The queries in the following example set all use the multilevel partitioned table defined by the following CREATE TABLE request.

     CREATE TABLE markets (
       productid       INTEGER NOT NULL,
       region          BYTEINT NOT NULL,
       activity_date   DATE FORMAT 'yyyy-mm-dd' NOT NULL,
       revenue_code    BYTEINT NOT NULL,
       business_sector BYTEINT NOT NULL,
       note            VARCHAR(256))
     PRIMARY INDEX (productid, region)
     PARTITION BY (
     RANGE_N(region          BETWEEN 1 
                             AND     9 
                             EACH    3),
     RANGE_N(business_sector BETWEEN 0 
                             AND    49 
                             EACH   10),
     RANGE_N(revenue_code    BETWEEN 1 
                             AND    34 
                             EACH    2),
     RANGE_N(activity_date   BETWEEN DATE '1986-01-01' 
                             AND     DATE '2007-05-31' 
                             EACH INTERVAL '1' MONTH));

Note the following details about the partitioning of this table.

  • The following four levels of row partitioning are defined with the indicated number of row partitions per level.
  •  

     Number of Row Partitioning Level

              Partitioning Column for                     This Level

           Number of Row Partitions             Defined at This Level

                  1

    region

                               3

                  2

    business_sector

                               5

                  3

    revenue_code

                             17

                  4

    activity_date

                            257

  • The total number of combined partitions defined for the table is the maximum for a 2‑byte internal partition number: 65,535, calculated as follows.
  • where:

     

    Equation element …

    Specifies …

    PLn

    the number of partitions at level n.

    For the sake of simplicity, the comments associated with the example queries make the following assumptions.

  • Rows are evenly distributed among the row partitions.
  • Many data blocks per combined partition can be combined.
  • If the example queries were not able to capitalize on static row partition elimination, they would all require all‑AMP, full‑table scans to be processed.

    For the following query, Teradata Database reads only row partition 2 at level 1 on all AMPs. As a result, it reads roughly (33.3%) of the data blocks for the markets table because there are 3 row partitions at level 1, and Teradata Database needs to read only one of them to access all of the qualified rows.

    Row partition 2 includes rows with a value of 5 or 6 for region. Teradata Database necessarily reads those rows as it reads row partition 2, but they do not qualify for the query predicate, so they are not returned in the results set.

         SELECT * 
         FROM markets 
         WHERE region=4;

    For the following query, Teradata Database reads only partitions 4 and 5 at level 2 on all AMPs. As a result, it reads roughly (40%) of the data blocks for the markets table because there are 5 row partitions at level 2, and Teradata Database needs to read 2 of those 5 to access all of the qualified rows.

    Row partition 4 includes rows whose value for business_sector is 30. Teradata Database necessarily reads those rows as it reads partition 4, but they do not qualify for the query predicate, so they are not returned in the results set.

         SELECT * 
         FROM markets 
         WHERE business_sector>30;

    For the following query, Teradata Database reads only partitions 1, 2, and 3 at level 3 on all AMPs. As a result, it reads roughly (17.6%) of the data blocks for the markets table because there are 17 row partitions at level 3, and the system needs to read only 3 of them to access all of the qualified rows.

    Partition 3 includes rows who value for revenue_code is 6. Teradata Database necessarily reads those rows as it reads partition 5, but they do not qualify for the query predicate, so they are not returned in the results set.

         SELECT * 
         FROM markets 
         WHERE revenue_code<5;

    For the following query, Teradata Database reads only partitions 50 and 51 at level 4 on all AMPs. As a result, it reads roughly (0.78%) of the data blocks for the markets table because there are 257 row partitions at level 4, and the system needs to read only 2 of them to access all of the qualified rows.

    Both partition 50 and partition 51 include rows that do not meet the predicate for the query. Teradata Database necessarily reads those rows as it reads the row partitions, but they do not qualify for the query predicate, so they are not returned in the results set.

         SELECT * 
         FROM markets 
         WHERE activity_date>=DATE '1990-02-12'
         AND   activity_date<=DATE '1990-03-28';

    For the following query, the system reads partitions 4 and 5 at level 2 on all AMPs and partition 2 at level 1 on all AMPs. As a result, it reads roughly (13.3%) of the data blocks for the markets table because there are 15 partitions at combined levels 1 and 2 (the combined total is 3 x 5 = 15), 3 at level 1 and 5 at level 2, and the system needs to read only 2 of them to access all of the qualified rows.

    Note that all of these partitions contain some rows that do not qualify for the query predicate, so the system does not return them in the results set.

         SELECT * 
         FROM markets 
         WHERE region=4 
         AND   business_sector>30;

    For the following query, Teradata Database reads the following set of row partitions at the indicated levels on all AMPs: 2 row partitions of level 4 (partitions 50 and 51), in 3 row partitions at level 3 (partitions 1, 2, and 3), and in 2 row partitions at level 2 (partitions 4 and 5).

     

    Level Number

    Number of Row Partitions

    Read at This Level

    Partition Numbers Read Within the Level

    2

    2

  •   4
  •   5
  • 3

    3

  •   1
  •   2
  •   3
  • 4

    2

  • 50
  • 51
  • As a result, Teradata Database reads roughly (0.05%) of the data blocks for the markets table because there are 21,845 partitions (the combined total is 5 x 17 x 257 = 21,845) at combined levels 2, 3, and 4, and the system needs to read only 12 of them (the combined total is 2 x 3 x 2 = 12) to access all of the qualified rows.

    All of these partitions contain some rows that do not qualify for the query predicate, so Teradata Database does not return them in the results set.

         SELECT * 
         FROM markets 
         WHERE business_sector>30
         AND   revenue_code<5
         AND   activity_date>=DATE '1990-02-12'
         AND   activity_date<=DATE '1990-03-28';

    For the following query, Teradata Database reads the following set of row partitions at the indicated levels on all AMPs: 2 row partitions of level 4 (partitions 50 and 51), in 3 row partitions at level 3 (partitions 1, 2, and 3), in 2 row partitions at level 2 (partitions 4 and 5), in 1 row partition of level 1 (partition 2).

     

    Level Number

    Number of Partitions Read at This Level

    Partition Numbers Read Within the Level

                1

                                      1

           2

                2

                                      2

  •   4
  •   5
  •             3

                                      3

  •   1
  •   2
  •   3
  •             4

                                      2

  • 50
  • 51
  • As a result, Teradata Database reads roughly (0.18%) of the data blocks for the markets table because there are 65,535 partitions (the combined total is 3 x 5 x 17 x 257 = 65,535) at combined levels 2, 3, and 4, and Teradata Database needs to read only 12 of them (the combined total is 1 x 2 x 3 x 2 = 12) to access all of the qualified rows.

    All of these partitions contain some rows that do not qualify for the query predicate, so Teradata Database does not return them in the results set.

         SELECT * 
         FROM markets 
         WHERE region=4
         AND   business_sector>30
         AND   revenue_code<5
         AND   activity_date>=DATE '1990-02-12'
         AND   activity_date<=DATE '1990-03-28';

    For the following query, Teradata Database reads one row partition at level 2 on all AMPs: partition 1. As a result, it reads roughly (20.0%) of the data blocks for the markets table because there are 5 row partitions at level 2, and Teradata Database needs to read only one of them to access all of the qualified rows.

    The row partition Teradata Database must read contains the rows having a value for business_sector between 0 and 9, inclusive.

    This row partition contains some rows that do not qualify for the query predicate, so the system does not return them in the results set.

         SELECT * 
         FROM markets 
         WHERE PARTITION#L2=1;

    For the following query, Teradata Database reads one combined row partition on all AMPs because the specified predicate is an equality condition on the combined partition that is equal to 32,531. As a result, it reads only (0.15%) of the data blocks for the markets table because there is only one combined partition that has the value 32,531, and the system needs to read only the data block that contains combined partition number 32,531 to access all of the qualified rows.

    The row partition Teradata Database must read contains the rows defined by the following value set.

     

    PARTITION#Ln

    PARTITION Value Where Qualified Rows Are Stored

    Conditions Mapped To This PARTITION Value Range

               1

                            2

    region BETWEEN 4 AND 6

               2

                            3

    business_sector BETWEEN 20 AND 29

               3

                            8

    revenue_code BETWEEN 15 AND 16

               4

                        149

    activity_date BETWEEN 1998-05-01 AND 1998-05-31

         SELECT * 
         FROM markets 
         WHERE PARTITION=32531;