Examples of Rewrites Using Row Partitioning | VantageCloud Lake - Examples: Rewrites Using Row Partitioning - Teradata Vantage

Row Partitioning Example: Reading Only Row Partition Two

For the following query, the database reads only row partition 2 at level 1 on all AMPs. That is about 1/3 (33.3%) of the data blocks for the markets table, because there are 3 row partitions at level 1, and Vantage must read only one to access all qualified rows.

Row partition 2 includes rows with a value of 5 or 6 for region. The database necessarily reads those rows while reading row partition 2, but those rows do not qualify for the query predicate, so are not returned in the results set.

SELECT *
FROM markets
WHERE region=4;

Row Partitioning Example: Reading Only Row Partitions Four and Five at Level Two on All AMPs

For the following query, the database reads only partitions 4 and 5 at level 2 on all AMPs. That is about 2/5 (40%) of the data blocks for the markets table, because there are 5 row partitions at level 2, and the database must read 2 of those 5 to access all of the qualified rows.

Row partition 4 includes rows whose value for business_sector is 30. The database necessarily reads those rows while reading partition 4, but those rows do not qualify for the query predicate, so are not returned in the results set.

SELECT *
FROM markets
WHERE business_sector>30;

Row Partitioning Example: Reading Only Row Partitions One, Two, and Three

For the following query, the database reads only partitions 1, 2, and 3 at level 3 on all AMPs. That is about 3/17 (17.6%) of the data blocks for the markets table, because there are 17 row partitions at level 3, and the system must read only 3 to access all qualified rows.

Partition 3 includes rows who value for revenue_code is 6. The database necessarily reads those rows as while reading partition 5, but those rows do not qualify for the query predicate, so are not returned in the results set.

SELECT *
FROM markets
WHERE revenue_code<5;

Row Partitioning Example: Reading Only Row Partitions 50 and 51

For the following query, the database reads only partitions 50 and 51 at level 4 on all AMPs. That is about 2/257 (0.78%) of the data blocks for the markets table, because there are 257 row partitions at level 4, and the system must read only 2 to access all qualified rows.

Both partition 50 and partition 51 include rows that do not meet the predicate for the query. The database necessarily reads those rows while reading the row partitions, but those rows do not qualify for the query predicate, so are not returned in the results set.

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

Example: Reading Only Row Partitions Four and Five at Level Two on All AMPs and Partition Two at Level One on All AMPs

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. That is about 2/15 (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 must read only 2 to access all qualified rows.

All these partitions contain rows that do not qualify for the query predicate, so the system does not return those rows in the results set.

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

Row Partitioning Example 1: Reading Multiple Row Partitions at Multiple Levels on All AMPs

For the following query, the 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).

Therefore, the database reads roughly 12/21,845 (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 must read only 12 (the combined total is 2 x 3 x 2 = 12) to access all qualified rows.

All of these partitions contain rows that do not qualify for the query predicate, so the database does not return those rows 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';

Row Partitioning Example 2: Reading Multiple Row Partitions at Multiple Levels on All AMPs

For the following query, the 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).

Therefore, the database reads roughly 12/65,535 (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 the database must read only 12 (the combined total is 1 x 2 x 3 x 2 = 12) to access all qualified rows.

All of these partitions contain rows that do not qualify for the query predicate, so the database does not return those rows 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';

Row Partitioning Example: Reading One Row Partition at Level Two on All AMPs

For the following query, the database reads one row partition at level 2 on all AMPs: partition 1. That is about 1/5 (20.0%) of the data blocks for the markets table, because there are 5 row partitions at level 2, and the database must read only one to access all qualified rows.

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

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

SELECT *
FROM markets
WHERE PARTITION#L2=1;

Row Partitioning Example: Reading One Combined Row Partition on All AMPs

For the following query, the 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. That is only 1/65,525 (0.15%) of the data blocks for the markets table, because there is only one combined partition with the value 32,531, and the system must read only the data block that contains combined partition number 32,531 to access all qualified rows.

The row partition the database must read contains the rows defined by the following value set:

SELECT *
FROM markets
WHERE PARTITION=32531;