- 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;