16.20 - Effects of Dynamic Row Partition Elimination on Joins - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1184-162K
Language
English (United States)
The following guidelines apply to maximizing dynamic row partition elimination for your join queries (dynamic row partition elimination is not supported with merge join methods for multilevel partitioned tables. See Teradata Vantage™ - Database Design, B035-1094 and Teradata Vantage™ SQL Request and Transaction Processing, B035-1142) against a partitioned table.
  • Dynamic row partition elimination improves performance when a partitioned table and another table are joined by an equality condition on the partitioning column set of the partitioned table.

    Note that this does not work with an inclusion product join, which the Optimizer specifies to work with IN (subquery) join conditions.

  • Only those partitions that are required to answer a request are involved in the join.

    When performing join steps, the system determines the qualifying partitions of the partitioned table dynamically based on the values of rows from the other table.

    Instead of a product join against all the rows in the partitioned table, the Optimizer assigns a product join only for a row of the other table against a single partition instead of the entire table.

    If there are 100 partitions in the partitioned table and only 5 are needed to answer a join request, the other 95 are not joined, and the system realizes a 95% resource saving for that join operation.

  • Always collect statistics on the join columns.

The following table definitions, EXPLAIN request, and EXPLAIN report are used to demonstrate how dynamic row partition elimination can facilitate join operations where one of the tables in the join is a PPI table.

     CREATE SET TABLE DB.sales (    -- Defines total day sales
       prodid   INTEGER,            -- for each product
       saledate DATE FORMAT 'YYYY-MM-DD',
       amount   DECIMAL(10,2))
     PRIMARY INDEX (prodid, saledate)
     PARTITION BY RANGE_N(saledate BETWEEN DATE '2004-01-01'
                                   AND     DATE '2004-12-31'
                                   EACH INTERVAL '1' MONTH );

     CREATE SET TABLE DB.fiscal_month (    -- Defines the days in
       yr       SMALLINT NOT NULL,         -- in each fiscal month.
       mth      SMALLINT NOT NULL,         -- A fiscal month may
       dayofmth DATE NOT NULL)             -- partially span up
     PRIMARY INDEX (yr, mth);              -- to 2 calendar months.

For example, fiscal month 9 in 2004 is from August 30 to September 26, or 28 days.

Assume a 2 AMP system and 10 product sales per day.

An EXPLAIN of the SELECT statement below includes a RETRIEVE step from DB.fiscal_month by way of the primary index “DB.fiscal_month.yr = 2004, DB.fiscal_month.mth = 9" followed by a SORT to partition by rowkey. Next, JOIN step to all partitions of DB.sales. using a product join, with a join condition of (“DB.sales.saledate = dayofmth") enhanced by dynamic partition elimination.

      SELECT yr, mth, SUM(amount)
             FROM DB.sales, DB.fiscal_month
             WHERE saledate = dayofmth
             AND   yr = 2004
             AND   mth = 9
             GROUP BY yr, mth;