Row-Key based merge join vs. RowHash Match Scan + Sliding Window join

Database
Enthusiast

Row-Key based merge join vs. RowHash Match Scan + Sliding Window join

I have two tables with identical NUPIs and PPI expressions.

When I join the two tables, and I include in the join the PPI columns, I get partition elimination on both tables (desired result).  The join method used in this is a Row-Key based merge join.   

SELECT 

                exp5.SO_HdrNbr , exp5.SO_SchedNbr , exp5.SO_ItmNbr , exp5.SO_HdrCrtDt ,

                exp5.CCD , exp5.SO_DocTypeCd , exp5.DivCd , exp5.SlsOrgCd , exp5.SuperGeoID ,

                exp5.SO_ItmNetPrc , exp5.SO_ItmWhlslAmt , exp5.BaseAllocnStat_D_Qty ,

                exp5.BaseAllocnStat_F_Qty , exp5.BaseAllocnStat_R_Qty , exp5.BasePtlShpmtQty ,

                exp5.BaseRsrvdInTrnstQty , exp5.BaseRsrvdOnHandQty , exp5.BaseTempInvQty ,

                exp4.so_hdrnbr

FROM   explore_t.SO_CoreExp5 exp5

JOIN eis_t.SO_Core exp4

                ON         exp5.SO_HdrNbr = exp4.SO_HdrNbr                      -- NUPI col

                AND       exp5.SO_HdrCrtDt = exp4.SO_HdrCrtDt

                AND       exp5.SO_ItmNbr = exp4.SO_ItmNbr

                AND       exp5.SO_SchedNbr = exp4.SO_SchedNbr

                AND       exp5.OrigPhysSrcSysSK = exp4.OrigPhysSrcSysSK

                AND       exp5.divcd = exp4.divcd                                               --PPI Col

                AND       exp5.slsorgcd = exp4.slsorgcd                                    --PPI Col

                AND       exp5.supergeoid = exp4.supergeoid                       --PPI Col

                AND       exp5.ccd = exp4.ccd                                                       --PPI Col

WHERE EXP5.CCD             BETWEEN  '2011-06-01' AND '2011-06-30'

and exp5.slsorgcd = '1000'

;

4) We do an all-AMPs JOIN step from 30 partitions of eis_t.exp4 by

     way of a RowHash match scan with a condition… which is joined to 30

     partitions of explore_t.exp5 by way of a RowHash match scan…  eis_t.exp4 and

     explore_t.exp5 are joined using a rowkey-based merge join …  The result goes into  

    Spool 1 (all_amps), which is built locally on the AMPs. … The estimated time for this step is 4.40 seconds.

This query uses 718 AMP CPU seconds and has a Total IO count of 13,779,162

If I run the same query, but take one of the PPI columns out from the join (logically, it is not needed, and in this case, eliminating it doesn’t affect the number of partitions read), I get a sliding window merge join:

SELECT 

                exp5.SO_HdrNbr , exp5.SO_SchedNbr , exp5.SO_ItmNbr , exp5.SO_HdrCrtDt ,

                exp5.CCD , exp5.SO_DocTypeCd , exp5.DivCd , exp5.SlsOrgCd , exp5.SuperGeoID ,

                exp5.SO_ItmNetPrc , exp5.SO_ItmWhlslAmt , exp5.BaseAllocnStat_D_Qty ,

                exp5.BaseAllocnStat_F_Qty , exp5.BaseAllocnStat_R_Qty , exp5.BasePtlShpmtQty ,

               exp5.BaseRsrvdInTrnstQty , exp5.BaseRsrvdOnHandQty , exp5.BaseTempInvQty ,

               exp4.so_hdrnbr

FROM   explore_t.SO_CoreExp5 exp5

JOIN eis_t.SO_Core exp4

                ON         exp5.SO_HdrNbr = exp4.SO_HdrNbr                      -- NUPI col

                AND       exp5.SO_HdrCrtDt = exp4.SO_HdrCrtDt

                AND       exp5.SO_ItmNbr = exp4.SO_ItmNbr

                AND       exp5.SO_SchedNbr = exp4.SO_SchedNbr

                AND       exp5.OrigPhysSrcSysSK = exp4.OrigPhysSrcSysSK

--             AND       exp5.divcd = exp4.divcd                                               --PPI Col  commented out.

                AND       exp5.slsorgcd = exp4.slsorgcd                                    --PPI Col

                AND       exp5.supergeoid = exp4.supergeoid                       --PPI Col

                AND       exp5.ccd = exp4.ccd                                                       --PPI Col

WHERE EXP5.CCD             BETWEEN  '2011-06-01' AND '2011-06-30'

and exp5.slsorgcd = '1000'

;

4) We do an all-AMPs RETRIEVE step from 30 partitions of eis_t.exp4

     …  which is built locally on the AMPs.  Then we do a SORT to order

     Spool 2 …The estimated time for this step is 5.50

     seconds.

  5) We do an all-AMPs JOIN step from 30 partitions of explore_t.exp5

     by way of a RowHash match scan with a condition … which is joined to Spool 2

     (Last Use) by way of a RowHash match scan.  explore_t.exp5 and

     Spool 2 are joined using a sliding-window merge join, with a join

     condition of …  The result goes into Spool 1 (all_amps), which is built locally on

     the AMPs.  …  The estimated time for

     this step is 15.31 seconds.

Both queries return the same result, but the resource utilization for the second one is lower.

Amp CPU seconds are down to 518, and IO count is down to 1,163,050. 

Can anyone shed some light on why this is the case?  I’m mostly interested in the difference in the IO count.

If I remove all the PPI columns from the join criteria, performance suffers as I don’t get any partition elimination on one of the tables and the tables have lots of rows (1.3B.  I can’t tell at design time which PPI columns will be used as predicates.  I have to define the Joins within our BI tool (Cognos), so they will be consistent for most queries.

2 REPLIES

Re: Row-Key based merge join vs. RowHash Match Scan + Sliding Window join

Hello Jimmylee

I am going to volunteer an explanation as I have seen this type of join occuring in my situation and "volunteer" because I am only 1 year old with teradata experience..so Here goes.:)

In the 1st case,since PI's match the rows from both tables are co-located and hence the spool form the smaller table(hopefully) is built "locally" and then "row-key based merge joined " with the second table to get the results.

In the 2nd case since the join condition negates one of the PI columns,the 1st table is spooled and sorted by the row key of the second table.This spool(hopefully from the smaller table) is then used to do asliding window merge join.This join basically "partitions the second  table into rows that can be held in memory.the first partition is read into memeory and merge joined with the rows from spool..in effect there is less CPU time .The amount of memory allocated by a DBS parameter affects the effectiveness of this type of join.

HTH.

Kalyan

Enthusiast

Re: Row-Key based merge join vs. RowHash Match Scan + Sliding Window join

Hi,

Can we conclude that sliding window joins  performs slower than a Merge Join or Rowkey-Based Merge Join when we have  many non-eliminated partitions ?

Thanks and regards,

Raja