16.10 - Detailed Multilevel Partitioning Example - Teradata Database

Teradata Database Design

Product
Teradata Database
Release Number
16.10
Release Date
June 2017
Content Type
User Guide
Publication ID
B035-1094-161K
Language
English (United States)

Multilevel Partitioned Sales Table With 3 Levels of Row Partitioning

The following CREATE TABLE request defines a table with 3 levels of row partitioning.

     CREATE TABLE sales (
       storeid      INTEGER NOT NULL,
       productid    INTEGER NOT NULL,
       salesdate    DATE FORMAT 'yyyy-mm-dd' NOT NULL,
       totalrevenue DECIMAL(13,2),
       totalsold    INTEGER,
       note         VARCHAR(256))
     UNIQUE PRIMARY INDEX (storeid, productid, salesdate)
     PARTITION BY (RANGE_N(salesdate BETWEEN DATE '2003-01-01'
                                     AND     DATE '2005-12-31'
                                     EACH INTERVAL '1' YEAR),
                   RANGE_N(storeid   BETWEEN 1
                                     AND   300
                                     EACH  100),
                   RANGE_N(productid BETWEEN 1
                                     AND   400
                                     EACH  100));

Combined Partitioning Expression for the Sales Table

The corresponding combined partitioning expression is the following.

     (RANGE_N(salesdate  BETWEEN DATE '2003-01-01'
                         AND     DATE '2005-12-31'
                         EACH INTERVAL '1' YEAR)-1)*12+
     (RANGE_N(storeid    BETWEEN 1
                         AND   300
                         EACH  100)-1)*4+
     (RANGE_N(productid  BETWEEN 1
                         AND   400
                         EACH  100)

There are 3 row partitions for both the first level and second levels, and 4 row partitions for the third level. Therefore, the total number of combined partitions is the product of 3*3*4, or 36.

The following diagram indicates the logical hierarchy of the three row partitioning levels for sales (65,535 assumes 2-byte partitioning):



How Rows for the Sales Table Are Grouped on an AMP

The table on the following page shows how rows would be grouped on an AMP and, for each row, the partition number for each level (the result of the row partitioning expression for that level) and combined partition number (the result of the combined partitioning expression).

Rows are grouped by the combined partition number, and within a group are ordered by hash value first, then by uniqueness value. The Note column is truncated in all of these examples.

Note that for this example, only one sample row is shown for each combined partition number.

You can see that all the rows for a particular year are grouped together in this table; therefore, accessing those rows can be accomplished by reading only a subset of the data on each of the AMPs.



For example:

  • All the rows with a storeid in the range of 101 through 200, inclusively, can be found in the row partitions for combined partition numbers 5 to 8, 17 to 20, and 29 to 32.

    As a result, if you were to specify a predicate of WHERE storeid BETWEEN 101 AND 200, Teradata Database could use row partition elimination to scan only combined partitions 5-8, 17-20, and 29-32 because only they contain rows that evaluate to TRUE for the condition.

    The row partitions and rows that are not eliminated are highlighted in teal.

 Row Partition Number    Sales
Combined Row Partition Number L1 L2 L3 Storeid Productid Salesdate Total Revenue Total Sold Note
         1 1 1 1          96            10 2003-04-15        4158          42 Good day
         2 1 1 2          71          184 2003-07-06        1972          68 Marginal Sa
         3 1 1 3          80          241 2003-11-09        3055          47 Slow day
         4 1 1 4          82          363 2003-12-24        1261          13 Promotion
         5 1 2 1        186              1 2003-01-11          255          17 Shelf Life
         6 1 2 2        122          163 2003-06-13        2405          65 Multiple
         7 1 2 3        110          234 2003-05-01        2618          77 2 for 1
         8 1 2 4        187          384 2003-08-03          684            9 Buy 3, 1 Fr
         9 1 3 1        280            31 2003-03-10          493          29 Rain
       10 1 3 2        202          116 2003-02-17        6732          68 Cash Sale
       11 1 3 3        292          272 2003-01-30          539          11 Quarterly
       12 1 3 4        213          385 2003-04-29        4233          83 Promotion
       13 2 1 1          76            51 2004-10-05          442          34 2 for 1
       14 2 1 2          26          149 2004-01-09          365            5 Good day
       15 2 1 3          57          295 2004-11-04        2684          61 Marginal Sa
       16 2 1 4          87          338 2004-08-02          696          58 Slow day
       17 2 2 1        108            34 2004-04-27        4218          74 2 for 1
       18 2 2 2        171          143 2004-10-30        5925          79 Shelf Life
       19 2 2 3        114          228 2004-05-24        2064          48 Rain
       20 2 2 4        135          347 2004-08-03          110          55 Promotion
       21 2 3 1        257            75 2004-09-18        3417          51 Promotion
       22 2 3 2        295          191 2004-11-11          376            8 Quarterly
       23 2 3 3        208          204 2004-03-17          864          36 Multiple
       24 2 3 4        221          330 2004-12-15        1456          52 Buy 3, 1 Fr
       25 3 1 1          39            85 2005-09-15          192          48 Cash Sale
       26 3 1 2          55          112 2005-07-12          232          29 Shelf Life
       27 3 1 3          76          243 2005-03-13        6696          93 2 for 1
       28 3 1 4            7          309 2005-08-20          116          58 Discounted
       29 3 2 1        186            44 2005-05-30        4275          75 Credit
       30 3 2 2        183          167 2005-06-14        2982          42 Promotion
       31 3 2 3        171          218 2005-05-22            80            8 Rain
       32 3 2 4        180          389 2005-06-09        4128          96 Good day
       33 3 3 1        278            61 2005-09-22        1581          51 Buy 4, 1 Fr
       34 3 3 2        256          110 2005-04-02        3280          80 Sale
       35 3 3 3        246          233 2005-11-29        5133          59 Discounted
       36 3 3 4        251          342 2005-02-11          968          44 50% off
  • All the rows with a productid in the range of 201 through 300, inclusively, can be found in the combined partition numbers 3, 7, 11, 15, 19, 23, 27, 31, and 35.

    As a result, if you were to specify a predicate of WHERE productid BETWEEN 201 AND 300, Teradata Database could use row partition elimination to scan only partitions 3, 7, 11, 15, 19, 23, 27, 31, and 35 because only they contain rows that evaluate to TRUE for the condition.

 Partition Number    Sales
Combined Partition Number L1 L2 L3 Storeid Productid Salesdate Total Revenue Total Sold Note
         1 1 1 1          96           10 2003-04-15        4158          42 Good day
         2 1 1 2          71         184 2003-07-06        1972          68 Marginal Sa
         3 1 1 3          80         241 2003-11-09        3055          47 Slow day
         4 1 1 4          82         363 2003-12-24        1261          13 Promotion
         5 1 2 1        186              1 2003-01-11          255          17 Shelf Life
         6 1 2 2        122         163 2003-06-13        2405          65 Multiple
         7 1 2 3        110         234 2003-05-01        2618          77 2 for 1
         8 1 2 4        187         384 2003-08-03          684            9 Buy 3, 1 Fr
         9 1 3 1        280           31 2003-03-10          493          29 Rain
       10 1 3 2        202         116 2003-02-17        6732          68 Cash Sale
       11 1 3 3        292         272 2003-01-30          539          11 Quarterly
       12 1 3 4        213         385 2003-04-29        4233          83 Promotion
       13 2 1 1          76           51 2004-10-05          442          34 2 for 1
       14 2 1 2          26         149 2004-01-09          365            5 Good day
       15 2 1 3          57         295 2004-11-04        2684          61 Marginal Sa
       16 2 1 4          87         338 2004-08-02          696          58 Slow day
       17 2 2 1        108           34 2004-04-27        4218          74 2 for 1
       18 2 2 2        171         143 2004-10-30        5925          79 Shelf Life
       19 2 2 3        114         228 2004-05-24        2064          48 Rain
       20 2 2 4        135         347 2004-08-03          110          55 Promotion
       21 2 3 1        257           75 2004-09-18        3417          51 Promotion
       22 2 3 2        295         191 2004-11-11          376            8 Quarterly
       23 2 3 3        208         204 2004-03-17          864          36 Multiple
       24 2 3 4        221         330 2004-12-15        1456          52 Buy 3, 1 Fr
       25 3 1 1          39           85 2005-09-15          192          48 Cash Sale
       26 3 1 2          55         112 2005-07-12          232          29 Shelf Life
       27 3 1 3          76         243 2005-03-13        6696          93 2 for 1
       28 3 1 4            7         309 2005-08-20          116          58 Discounted
       29 3 2 1        186           44 2005-05-30        4275          75 Credit
       30 3 2 2        183         167 2005-06-14        2982          42 Promotion
       31 3 2 3        171         218 2005-05-22            80            8 Rain
       32 3 2 4        180         389 2005-06-09        4128          96 Good day
       33 3 3 1        278           61 2005-09-22        1581          51 Buy 4, 1 Fr
       34 3 3 2        256         110 2005-04-02        3280          80 Sale
       35 3 3 3        246         233 2005-11-29        5133          59 Discounted
       36 3 3 4        251         342 2005-02-11          968          44 50% off
  • All the rows with a storeid in the range of 1 and 100, inclusively, and a productid in the range 301 through 400, inclusively, can be found in the combined partitions for combined partition numbers 4, 16, and 28.

    As a result, if you were to specify a predicate of WHERE storeid BETWEEN 1 AND 100 AND productid BETWEEN 301 AND 400, Teradata Database could use row partition elimination to scan only combined partitions 4, 16, and 28 because only they contain rows that evaluate to TRUE for the condition.

 Partition Number    Sales
Combined Partition Number L1 L2 L3 Storeid Productid Salesdate Total Revenue Total Sold Note
         1 1 1 1          96            10 2003-04-15        4158          42 Good day
         2 1 1 2          71          184 2003-07-06        1972          68 Marginal Sa
         3 1 1 3          80          241 2003-11-09        3055          47 Slow day
         4 1 1 4          82          363 2003-12-24        1261          13 Promotion
         5 1 2 1        186              1 2003-01-11          255          17 Shelf Life
         6 1 2 2        122          163 2003-06-13        2405          65 Multiple
         7 1 2 3        110          234 2003-05-01        2618          77 2 for 1
         8 1 2 4        187          384 2003-08-03          684            9 Buy 3, 1 Fr
         9 1 3 1        280            31 2003-03-10          493          29 Rain
       10 1 3 2        202          116 2003-02-17        6732          68 Cash Sale
       11 1 3 3        292          272 2003-01-30          539          11 Quarterly
       12 1 3 4        213          385 2003-04-29        4233          83 Promotion
       13 2 1 1          76            51 2004-10-05          442          34 2 for 1
       14 2 1 2          26          149 2004-01-09          365            5 Good day
       15 2 1 3          57          295 2004-11-04        2684          61 Marginal Sa
       16 2 1 4          87          338 2004-08-02          696          58 Slow day
       17 2 2 1        108            34 2004-04-27        4218          74 2 for 1
       18 2 2 2        171          143 2004-10-30        5925          79 Shelf Life
       19 2 2 3        114          228 2004-05-24        2064          48 Rain
       20 2 2 4        135          347 2004-08-03          110          55 Promotion
       21 2 3 1        257            75 2004-09-18        3417          51 Promotion
       22 2 3 2        295          191 2004-11-11          376            8 Quarterly
       23 2 3 3        208          204 2004-03-17          864          36 Multiple
       24 2 3 4        221          330 2004-12-15        1456          52 Buy 3, 1 Fr
       25 3 1 1          39            85 2005-09-15          192          48 Cash Sale
       26 3 1 2          55          112 2005-07-12          232          29 Shelf Life
       27 3 1 3          76          243 2005-03-13        6696          93 2 for 1
       28 3 1 4            7          309 2005-08-20          116          58 Discounted
       29 3 2 1        186            44 2005-05-30        4275          75 Credit
       30 3 2 2        183          167 2005-06-14        2982          42 Promotion
       31 3 2 3        171          218 2005-05-22            80            8 Rain
       32 3 2 4        180          389 2005-06-09        4128          96 Good day
       33 3 3 1        278            61 2005-09-22        1581          51 Buy 4, 1 Fr
       34 3 3 2        256          110 2005-04-02        3280          80 Sale
       35 3 3 3        246          233 2005-11-29        5133          59 Discounted
       36 3 3 4        251          342 2005-02-11          968          44 50% off

Similarly, Teradata Database can find rows by reading only a subset of the data for other combinations of two or more conditions on the partitioning columns. If you are looking for rows with a specific value of each of the partitioning columns, then only one combined partition for the specific combined row partition number needs to be read.

The following graphic, which is best viewed on a color monitor and best printed on a color printer, shows one possible representation of how the 36 combined partitions created for this table might be populated. The combined partitions have anywhere from 1 to 8 rows in this graphic, unlike the example table on which it is based, which provides only one example row per combined partition.



For this example, because it implies specifying values for all the columns in the primary index, only the group of rows with the same hash value determined from the primary index values need to be read, and only on a single AMP rather than the entire row partition for the specific combined row partition number on every AMP.

Performance Implications of Multilevel Row Partitioning

  • If a SELECT request specifies values for all the primary index columns, Teradata Database can determine the AMP on which the rows reside, and only a single AMP needs to be accessed.

    If conditions are not specified on the partitioning columns, then Teradata Database can probe each combined partition to find the rows based on their hash value.

    If conditions are also specified on the partitioning columns, row partition elimination might reduce the number of row partitions to be probed on that AMP.

  • If a SELECT request does not specify the values for all the primary index columns or there is no primary index, then Teradata Database must do an all-AMP full table scan for a nonpartitioned table.

    However, if row partitioning is defined on the table, and if you specify conditions on the partitioning columns, row partition elimination can reduce an all-AMP full file scan to an all-AMP scan of only the partitions of the combined partitioning expression that are not eliminated.

    The degree of row partition elimination that can be achieved depends on the partitioning expressions, the conditions in the query, and the ability of the Optimizer to recognize such opportunities.

    You need not specify values for all the partitioning columns in a query for row partition elimination to occur. Row partition elimination occurs at each level independently; the combination of the row partition elimination, if any, for each level determines which combined partitions need to be processed.