How Rows for the Sales Table Are Grouped on an AMP - Teradata Database

Teradata Database Design
Product
Teradata Database
Release Number
15.10
Language
English (United States)
Last Update
2018-10-06
Product Category
Software

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.

 

 Partition Number

Color/Pattern Representing This Combined Partition in the Graphic

 

 

 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

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 cyan.

     

     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.

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

     

     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.

    The combined partitions and rows that are not eliminated are highlighted in orange.

     

     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.