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