92% less CPU Usage with a Simple Trick – Teradata PPI Tables

Joining two partitioned tables can consume many resources for large tables, especially if the primary indexes or the partitioning are not identical. In this article, I will show you an unusual trick you probably haven’t seen before. First of all, I want to note that this is not a common procedure that you will see here, but it will show you how sometimes, with creativity, you can achieve considerable performance improvements. The test scenario shown works for two tables with identical primary index and partitioning. I leave it to you to try out whether it is also usable for other constellations.

The test setup consists of the below shown two tables (both tables containing about 50K rows):

CREATE MULTISET TABLE DWHPro.Sales2 , NO FALLBACK,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
SalesId INTEGER NOT NULL,
Quantity DECIMAL(18,2),
SalesDate DATE FORMAT 'YYYY-MM-DD')
PRIMARY INDEX ( SalesId )
PARTITION BY RANGE_N(SalesDate BETWEEN DATE '1900-01-01' AND DATE '2016-12-31' EACH INTERVAL '1' DAY , NO RANGE);

CREATE MULTISET TABLE DWHPro.Sales3, NO FALLBACK,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
SalesId INTEGER NOT NULL,
Quantity DECIMAL(18,2),
SalesDate DATE FORMAT 'YYYY-MM-DD')
PRIMARY INDEX ( SalesId )
PARTITION BY RANGE_N(SalesDate BETWEEN DATE '1900-01-01' AND DATE '2016-12-31' EACH INTERVAL '1' DAY , NO RANGE);

In our first query, we select all the partitions of the table. Without the WHERE condition, we would get an almost identical execution plan:

SELECT
t01.SalesId,
t02.SalesId
FROM Sales2 t01
INNER JOIN
Sales3 t02
ON
t01.SalesId = t02.SalesId AND t01.SalesDate = t02.SalesDate
WHERE t01.SALESDATE BETWEEN DATE'1900-01-01' AND DATE'2016-12-31';

The execution plan shows that both tables are redistributed, and the hash is calculated using the join columns. Since a merge join is executed, sorting the rows by ROWHASH is also necessary:

1) First, we lock a distinct DWHPRO."pseudo table" for read on a
RowHash to prevent global deadlock for DWHPRO.t02.
2) Next, we lock a distinct DWHPRO."pseudo table" for read on a
RowHash to prevent global deadlock for DWHPRO.t01.
3) We lock DWHPRO.t02 for read, and we lock DWHPRO.t01 for read.
4) We execute the following steps in parallel.
1) We do an all-AMPs RETRIEVE step from 42734 partitions of
DWHPRO.t02 with a condition of ("(DWHPRO.t02.SalesDate >=
DATE '1900-01-01') AND (DWHPRO.t02.SalesDate <= DATE
'2016-12-31')") into Spool 2 (all_amps), which is
redistributed by the hash code of (DWHPRO.t02.SalesId,
DWHPRO.t02.SalesDate) to all AMPs. Then we do a SORT to
order Spool 2 by row hash. The size of Spool 2 is estimated
with high confidence to be 42,734 rows (897,414 bytes). The
estimated time for this step is 0.34 seconds.
2) We do an all-AMPs RETRIEVE step from 42734 partitions of
DWHPRO.t01 with a condition of ("(DWHPRO.t01.SalesDate <=
DATE '2016-12-31') AND (DWHPRO.t01.SalesDate >= DATE
'1900-01-01')") into Spool 3 (all_amps), which is
redistributed by the hash code of (DWHPRO.t01.SalesId,
DWHPRO.t01.SalesDate) to all AMPs. Then we do a SORT to
order Spool 3 by row hash. The size of Spool 3 is estimated
with high confidence to be 42,734 rows (897,414 bytes). The
estimated time for this step is 0.34 seconds.
5) We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of a
RowHash match scan, which is joined to Spool 3 (Last Use) by way
of a RowHash match scan. Spool 2 and Spool 3 are joined using a
merge join, with a join condition of ("(SalesId = SalesId) AND
(SalesDate = SalesDate)"). The result goes into Spool 1
(group_amps), which is built locally on the AMPs. The size of
Spool 1 is estimated with low confidence to be 42,734 rows (
1,239,286 bytes). The estimated time for this step is 0.31
seconds.
-> The contents of Spool 1 are sent back to the user as the result of
statement 1. The total estimated time is 0.66 seconds.

Here comes the trick. We now remove the last tag from the WHERE condition. (i.e. ‘2016-12-31’):

SELECT
t01.SalesId,
t02.SalesId
FROM Sales2 t01
INNER JOIN
Sales3 t02
ON
t01.SalesId = t02.SalesId AND t01.SalesDate = t02.SalesDate
WHERE t01.SALESDATE BETWEEN DATE'1900-01-01' AND DATE'2016-12-30';

The resulting EXPLAIN PLAN reveals that the Optimizer switches to a rowkey-based merge join (without needing to do a costly row redistribution or sorting):

1) First, we lock a distinct DWHPRO."pseudo table" for read on a
RowHash to prevent global deadlock for DWHPRO.t02.
2) Next, we lock a distinct DWHPRO."pseudo table" for read on a
RowHash to prevent global deadlock for DWHPRO.t01.
3) We lock DWHPRO.t02 for read, and we lock DWHPRO.t01 for read.
4) We do an all-AMPs JOIN step from 42733 partitions of DWHPRO.t02 by
way of a RowHash match scan with a condition of (
"(DWHPRO.t02.SalesDate >= DATE '1900-01-01') AND
(DWHPRO.t02.SalesDate <= DATE '2016-12-30')"), which is joined to
42733 partitions of DWHPRO.t01 by way of a RowHash match scan with
a condition of ("(DWHPRO.t01.SalesDate <= DATE '2016-12-30') AND
(DWHPRO.t01.SalesDate >= DATE '1900-01-01')"). DWHPRO.t02 and
DWHPRO.t01 are joined using a rowkey-based merge join, with a join
condition of ("(DWHPRO.t01.SalesId = DWHPRO.t02.SalesId) AND
(DWHPRO.t01.SalesDate = DWHPRO.t02.SalesDate)"). The result goes
into Spool 1 (group_amps), which is built locally on the AMPs.
The size of Spool 1 is estimated with low confidence to be 42,734
rows (1,239,286 bytes). The estimated time for this step is 0.28
seconds.
-> The contents of Spool 1 are sent back to the user as the result of
statement 1. The total estimated time is 0.28 seconds.

This would have to be accompanied by enormous savings in resources. But since we want to select all days, we need to extend our query. I added the missing day as a test using the UNION ALL statement. Again we compare the query that causes a merge join with our query using rowkey-based merge join and added day using UNION ALL:

SELECT -- Standard merge join
t01.SalesId,
t02.SalesId
FROM Sales2 t01
INNER JOIN
Sales3 t02
ON
t01.SalesId = t02.SalesId AND t01.SalesDate = t02.SalesDate
WHERE t01.SALESDATE BETWEEN DATE'1900-01-01' AND DATE'2016-12-31';

SELECT -- Forcing the Optimizer into the rowkey-based merge join
t01.SalesId,
t02.SalesId
FROM Sales2 t01
INNER JOIN
Sales3 t02
ON
t01.SalesId = t02.SalesId AND t01.SalesDate = t02.SalesDate
WHERE t01.SALESDATE BETWEEN DATE'1900-01-01' AND DATE'2016-12-30'
UNION ALL
SELECT
t01.SalesId,
t02.SalesId
FROM Sales2 t01
INNER JOIN
Sales3 t02
ON
t01.SalesId = t02.SalesId AND t01.SalesDate = t02.SalesDate
WHERE t01.SALESDATE = DATE'2016-12-31';

As expected, the rowkey-based merge join is much cheaper and has a much better run time:

Conclusion:

This might be a great trick whenever we want to retrieve all partitions of two PPI tables joined together. When leaving out a WHERE condition covering the partitions, we may end up with a traditional merge join. This trick allows us to force the Optimizer to do a much cheaper rowkey-based merge join.

Further investigation shows that the Optimizer avoids the rowkey-based merge join if the tables we join are tiny. This trick probably doesn’t work with big tables as the Optimizer, by default, will choose the rowkey-based merge join. I wanted to show you that it is sometimes possible to get significant performance gains by changing the SQL statement. Optimization is often a creative process.