Sliding Window Merge Join Running for long time

Database
Teradata Employee

Sliding Window Merge Join Running for long time

Hello Team,

Require help in tuning below query. with existing filter & join conditons i tried to tune the query but no major breakthrough in reducing Runtime and Total CPU. The query is stuck in Step 4 where it is using Sliding Window Merge JOIN, it was  in same step for 25 min and eventually aborted by me. 

Appreciate your help in advance.

Defined all stats on base tables.

SAC_VT_1 is Volatile Table ( defintion is below Explain)

explain SELECT BTRN.POLH_POLH_SK , BTRN.POLH_POLY_SK , 

BTRN.BTRN_SK , BTRN.BTRN_POLY_SK , 

BTRN.BTRN_BROKER_FEE , BTRN.BTRN_NWFL_TAX_01 ,

 BTRN.BTRN_FIN_CHRG_01 , BTRN.BTRN_TRAN_NO , 

 BTRN.BTRN_TRANS_NO_01 , BTRN.BTRN_DATE_INC_01 , 

 BTRN.BTRN_DATE_EFF_01 , BTRN.BTRN_CANC_FLAG_01 , 

 BTRN.STEP , BTRN.POLH_DATE_EFFECT , BTRN.POLH_TRAN_NO , 

 BTRN.POLH_DATE_INCEPT , 

 BTRN.POLY_POLY_SK , BTRN.POLH_VALID_FLAG , BTRN.POLH_POL_STATUS , 

 BTRN.POLH_POLY_KEY_01 , BTRN.POLH_ACCT_KEY_01 , BTRN.POLH_REPL_POLICY , BTRN.POLH_DATE_ORIG_IN , 

 BTRN.POLH_POL_PAY_PLAN , BTRN.POLH_POL_BRANCH , BTRN.POLH_PROVINCE , BTRN.POLH_SPECIAL_COMM , BTRN.POLH_METHOD , 

 BTRN.POLH_TRANS_CODE , BTRN.POLH_DATE_EXPIRES , BTRN.POLH_BILL_TYPE , BTRN.POLH_HAB_ICPB_ORDER_IND ,

  BTRN.POLH_HAB_ICPB_MATCH_IND , BTRN.POLH_PORTFOLIO_TXF , BTRN.POLH_LOYALTY_DATE , BTRN.POLH_PROFILE_NO ,

   BTRN.POLH_CREDIT_SCORE_VALUE , BTRN.POLH_SPEC_RISK_OVR , BTRN.POLH_SPEC_RISK_SYS , BTRN.POLH_NP_CAN_COUNT , 

   BTRN.POLH_CR_TYPE , BTRN.POLH_CR_DATE , BTRN.POLH_MAU_COMPANY , BTRN.POLH_RULE_COMPANY , BTRN.POLH_RI_COMPANY , BTRN.POLH_EXT_RI , BTRN.POLH_EXT_RI_TYPE , 

   BTRN.POLH_INT_RI , BTRN.POLH_INT_RI_TYPE , BTRN.POLH_CREDIT_USED_IND , BTRN.POLH_POLICY_XFER_IND , BTRN.POLH_QUOTE_FACTOR , BTRN.POLH_EXT_MAU_COMP , 

   BTRN.POLH_EXT_QUO_FACTOR , BTRN.POLH_CHANNEL_IND , BTRN.POLH_PORTFOLIO_XFER_IBC_CO , BTRN.POLH_MI_MOVE_IND , BTRN.POLH_PDM_ID , 

   BTRN.POLH_ETL_UPDATE_DTTM , BTRN.POLY_ETL_UPDATE_DTTM , BTRN.POLH_VALID_FROM_DTTM , BTRN.POLH_VALID_TO_DTTM , 

   BTRN.POLY_VALID_FROM_DTTM , BTRN.POLY_VALID_TO_DTTM , BTRN.POLY_DATE_EXPIRES , BTRN.POLY_DATE_INCEPT ,

    BTRN.POLY_POL_SUSPEND , BTRN.POLY_PDM_ID , BTRN.POLY_REASON_TERM , BTRN.BTRN_TRANS_CODE , BTRN.BTRN_RISK_FTP_01 , 

    BTRN.PEDE_SK , BTRN.BTRN_PRM_TYPE_01 , BTRN.BTRN_DATE_EXPIRES , BTRN.RISK_SK , BTRN.BTRN_RSN_CODE_1 , BTRN.BTRN_RSN_CODE_2 , 

    BTRN.BTRN_RSN_CODE_3 , BTRN.RISK_CODE , BTRN.BTRN_VALID_TO_DTTM , 

    BTRN.BTRN_ETL_UPDATE_DTTM , 

    CAST( BTRN.BTRN_VALID_TO_DTTM AS DATE ) - CAST( RSKW.VALID_TO_DTTM AS DATE ) AS DIFF_BTRN_RSKW_VALID_TO_DTTM , 

    NULL AS DRIVEN , BTRN.BTRN_TWO_CAR_01 , BTRN.POLY_TRAN_NO , RSKW.RSKWV1_SK ,

     RSKW.POLY_SK , RSKW.RISK_SK , RSKW.RSKW_TRAN_NO , RSKW.RSKW_VALID_FLAG , RSKW.RSKW_RISK_CLASS ,

      RSKW.RSKW_DATE_TERM , RSKW.RSKW_DATE_EFFECT , RSKW.RSKW_RISK_KEY , RSKW.RSKW_POLY_KEY ,

       RSKW.RSKW_RI_METHOD , RSKW.RSKW_PROVINCE_V1 , RSKW.RSKW_TERRITORY_V1 , RSKW.RSKW_STAT_LOC_V1 , RSKW.RSKW_MODEL_YR_V1 ,

        RSKW.RSKW_SER_NO_V1 , RSKW.RSKW_RATE_GRP_A_V1 , RSKW.RSKW_RATE_GRP_B_V1 , RSKW.RSKW_RATE_GRP_C_V1 , RSKW.RSKW_CYL_V1 

        , RSKW.RSKW_BODY_CODE_V1 , RSKW.RSKW_PRICE_V1 , RSKW.RSKW_FUEL_TYP_V1 , RSKW.RSKW_KM_WORK_V1 , RSKW.RSKW_KM_YEAR_V1 , 

        RSKW.RSKW_RG_TYPE_A_V1 , RSKW.RSKW_RG_TYPE_B_V1 , RSKW.RSKW_RG_TYPE_C_V1 , RSKW.RSKW_RG_TYPE_D_V1 , RSKW.RSKW_DSC_CODE_V1_1 ,

         RSKW.RSKW_FACILITY_V1 , RSKW.RSKW_VEHICLE_MAKE_V1 , RSKW.RSKW_CAR_DSC_V1 , RSKW.RSKW_BODY_V1 , RSKW.RSKW_KM_WEEK_V1 , 

         RSKW.RSKW_UNREP_DAM_V1 , RSKW.RSKW_CUST_MOD_V1 , RSKW.RSKW_REG_OWN_V1 , RSKW.RSKW_SPEC_USE_V1 , RSKW.RSKW_NEW_USED_V1 , 

         RSKW.RSKW_ACQ_DT_V1 , RSKW.RSKW_POSTAL_CODE_V1 , RSKW.RSKW_VEHICLE_TYPE_V1 , RSKW.RSKW_BUS_USE_PCT_V1 , RSKW.RSKW_RATE_METH_V1 , RSKW.RSKW_USE_V1 , 

         RSKW.RSKW_CAR_CODE_V1 , RSKW.ETL_UPDATE_DTTM , RSKW.VALID_FROM_DTTM , RSKW.VALID_TO_DTTM , RSKW.RSKW_GRID_CAP_DIFF_V1 , RSKW.RSKW_GRID_LEVEL_V1 , 

         RSKW.RSKW_MVD_IND_V1 , RSKW.RSKW_CAP_IND_V1 , RSKW.RSKW_CAP_EXCEPT_V1 , RSKW.RSKW_MAX_PREM_V1 , RSKW.RSKW_RATE_GRP_D_V1 , RSKW.RSKW_CODE 

FROM sandbox.RSKWV1_PPI RSKW INNER JOIN 

 SAC_VT_1  BTRN

ON BTRN.RISK_SK = RSKW.RISK_SK 

WHERE RSKW.PDM_ID IN ( 'GW' , 'EL' )

 AND RSKW.ETL_UPDATE_DTTM  BETWEEN '2015/03/01 00:00:00' AND '2015/04/30 23:59:59'

 and  BTRN .BTRN_ETL_UPDATE_DTTM BETWEEN  '2015/03/01 00:00:00' AND '2015/04/30 23:59:59'

  MINUS ( SELECT BTRN.POLH_POLH_SK , BTRN.POLH_POLY_SK , BTRN.BTRN_SK , BTRN.BTRN_POLY_SK , BTRN.BTRN_BROKER_FEE , BTRN.BTRN_NWFL_TAX_01 , BTRN.BTRN_FIN_CHRG_01 , BTRN.BTRN_TRAN_NO , BTRN.BTRN_TRANS_NO_01 , BTRN.BTRN_DATE_INC_01 , BTRN.BTRN_DATE_EFF_01 , BTRN.BTRN_CANC_FLAG_01 , BTRN.STEP , BTRN.POLH_DATE_EFFECT , BTRN.POLH_TRAN_NO , BTRN.POLH_DATE_INCEPT , BTRN.POLY_POLY_SK , BTRN.POLH_VALID_FLAG , BTRN.POLH_POL_STATUS , BTRN.POLH_POLY_KEY_01 , BTRN.POLH_ACCT_KEY_01 , BTRN.POLH_REPL_POLICY , BTRN.POLH_DATE_ORIG_IN , BTRN.POLH_POL_PAY_PLAN , BTRN.POLH_POL_BRANCH , BTRN.POLH_PROVINCE , BTRN.POLH_SPECIAL_COMM , BTRN.POLH_METHOD , BTRN.POLH_TRANS_CODE , BTRN.POLH_DATE_EXPIRES , BTRN.POLH_BILL_TYPE , BTRN.POLH_HAB_ICPB_ORDER_IND , BTRN.POLH_HAB_ICPB_MATCH_IND , BTRN.POLH_PORTFOLIO_TXF , BTRN.POLH_LOYALTY_DATE , BTRN.POLH_PROFILE_NO , BTRN.POLH_CREDIT_SCORE_VALUE , BTRN.POLH_SPEC_RISK_OVR , BTRN.POLH_SPEC_RISK_SYS , BTRN.POLH_NP_CAN_COUNT , BTRN.POLH_CR_TYPE , BTRN.POLH_CR_DATE , BTRN.POLH_MAU_COMPANY , BTRN.POLH_RULE_COMPANY , BTRN.POLH_RI_COMPANY , BTRN.POLH_EXT_RI , BTRN.POLH_EXT_RI_TYPE , BTRN.POLH_INT_RI , BTRN.POLH_INT_RI_TYPE , BTRN.POLH_CREDIT_USED_IND , BTRN.POLH_POLICY_XFER_IND , BTRN.POLH_QUOTE_FACTOR , BTRN.POLH_EXT_MAU_COMP , BTRN.POLH_EXT_QUO_FACTOR , BTRN.POLH_CHANNEL_IND , BTRN.POLH_PORTFOLIO_XFER_IBC_CO , BTRN.POLH_MI_MOVE_IND , BTRN.POLH_PDM_ID , BTRN.POLH_ETL_UPDATE_DTTM , BTRN.POLY_ETL_UPDATE_DTTM , BTRN.POLH_VALID_FROM_DTTM , BTRN.POLH_VALID_TO_DTTM , BTRN.POLY_VALID_FROM_DTTM , BTRN.POLY_VALID_TO_DTTM , BTRN.POLY_DATE_EXPIRES , BTRN.POLY_DATE_INCEPT , BTRN.POLY_POL_SUSPEND , BTRN.POLY_PDM_ID , BTRN.POLY_REASON_TERM , BTRN.BTRN_TRANS_CODE , BTRN.BTRN_RISK_FTP_01 , BTRN.PEDE_SK , BTRN.BTRN_PRM_TYPE_01 , BTRN.BTRN_DATE_EXPIRES , BTRN.RISK_SK , BTRN.BTRN_RSN_CODE_1 , BTRN.BTRN_RSN_CODE_2 , BTRN.BTRN_RSN_CODE_3 , BTRN.RISK_CODE , BTRN.BTRN_VALID_TO_DTTM , BTRN.BTRN_ETL_UPDATE_DTTM , CAST( BTRN.BTRN_VALID_TO_DTTM AS DATE ) - CAST( RSKW.VALID_TO_DTTM AS DATE ) AS DIFF_BTRN_RSKW_VALID_TO_DTTM , NULL AS DRIVEN , BTRN.BTRN_TWO_CAR_01 , BTRN.POLY_TRAN_NO , RSKW.RSKWV1_SK , RSKW.POLY_SK , RSKW.RISK_SK , RSKW.RSKW_TRAN_NO , RSKW.RSKW_VALID_FLAG , RSKW.RSKW_RISK_CLASS , RSKW.RSKW_DATE_TERM , RSKW.RSKW_DATE_EFFECT , RSKW.RSKW_RISK_KEY , RSKW.RSKW_POLY_KEY , RSKW.RSKW_RI_METHOD , RSKW.RSKW_PROVINCE_V1 , RSKW.RSKW_TERRITORY_V1 , RSKW.RSKW_STAT_LOC_V1 , RSKW.RSKW_MODEL_YR_V1 , RSKW.RSKW_SER_NO_V1 , RSKW.RSKW_RATE_GRP_A_V1 , RSKW.RSKW_RATE_GRP_B_V1 , RSKW.RSKW_RATE_GRP_C_V1 , RSKW.RSKW_CYL_V1 , RSKW.RSKW_BODY_CODE_V1 , RSKW.RSKW_PRICE_V1 , RSKW.RSKW_FUEL_TYP_V1 , RSKW.RSKW_KM_WORK_V1 , RSKW.RSKW_KM_YEAR_V1 , RSKW.RSKW_RG_TYPE_A_V1 , RSKW.RSKW_RG_TYPE_B_V1 , RSKW.RSKW_RG_TYPE_C_V1 , RSKW.RSKW_RG_TYPE_D_V1 , RSKW.RSKW_DSC_CODE_V1_1 , RSKW.RSKW_FACILITY_V1 , RSKW.RSKW_VEHICLE_MAKE_V1 , RSKW.RSKW_CAR_DSC_V1 , RSKW.RSKW_BODY_V1 , RSKW.RSKW_KM_WEEK_V1 , RSKW.RSKW_UNREP_DAM_V1 , RSKW.RSKW_CUST_MOD_V1 , RSKW.RSKW_REG_OWN_V1 , RSKW.RSKW_SPEC_USE_V1 , RSKW.RSKW_NEW_USED_V1 , RSKW.RSKW_ACQ_DT_V1 , RSKW.RSKW_POSTAL_CODE_V1 , RSKW.RSKW_VEHICLE_TYPE_V1 , RSKW.RSKW_BUS_USE_PCT_V1 , RSKW.RSKW_RATE_METH_V1 , RSKW.RSKW_USE_V1 , RSKW.RSKW_CAR_CODE_V1 , RSKW.ETL_UPDATE_DTTM , RSKW.VALID_FROM_DTTM , RSKW.VALID_TO_DTTM , RSKW.RSKW_GRID_CAP_DIFF_V1 , RSKW.RSKW_GRID_LEVEL_V1 , RSKW.RSKW_MVD_IND_V1 , RSKW.RSKW_CAP_IND_V1 , RSKW.RSKW_CAP_EXCEPT_V1 , RSKW.RSKW_MAX_PREM_V1 , RSKW.RSKW_RATE_GRP_D_V1 , RSKW.RSKW_CODE 

  FROM sandbox.RSKWV1_PPI RSKW INNER JOIN  

 SAC_VT_1  BTRN

  ON BTRN.RISK_SK = RSKW.RISK_SK WHERE RSKW.PDM_ID IN ( 'GW' , 'EL' ) 

  AND ( BTRN.BTRN_RSN_CODE_1 IS NULL OR BTRN.BTRN_RSN_CODE_1 <> 'DV' )

   AND ( BTRN.BTRN_RSN_CODE_2 IS NULL OR BTRN.BTRN_RSN_CODE_2 <> 'DV' ) 

   AND ( BTRN.BTRN_RSN_CODE_3 IS NULL OR BTRN.BTRN_RSN_CODE_3 <> 'DV' ) 

   AND RSKW.RSKW_DATE_EFFECT <= BTRN.BTRN_DATE_EFF_01 

   AND RSKW.RSKW_DATE_EFFECT < BTRN.POLH_DATE_EXPIRES 

   AND RSKW.RSKW_DATE_EFFECT >= BTRN.POLH_DATE_INCEPT

    AND RSKW.RSKW_DATE_TERM  >  BTRN.BTRN_DATE_EFF_01 

    AND  RSKW.ETL_UPDATE_DTTM  BETWEEN '2015/03/01 00:00:00' AND '2015/04/30 23:59:59' 

    and  BTRN .BTRN_ETL_UPDATE_DTTM BETWEEN  '2015/03/01 00:00:00' AND '2015/04/30 23:59:59'

    UNION ALL SELECT BTRN.POLH_POLH_SK , BTRN.POLH_POLY_SK , BTRN.BTRN_SK , BTRN.BTRN_POLY_SK , BTRN.BTRN_BROKER_FEE , BTRN.BTRN_NWFL_TAX_01 , BTRN.BTRN_FIN_CHRG_01 , BTRN.BTRN_TRAN_NO , BTRN.BTRN_TRANS_NO_01 , BTRN.BTRN_DATE_INC_01 , BTRN.BTRN_DATE_EFF_01 , BTRN.BTRN_CANC_FLAG_01 , BTRN.STEP , BTRN.POLH_DATE_EFFECT , BTRN.POLH_TRAN_NO , BTRN.POLH_DATE_INCEPT , BTRN.POLY_POLY_SK , BTRN.POLH_VALID_FLAG , BTRN.POLH_POL_STATUS , BTRN.POLH_POLY_KEY_01 , BTRN.POLH_ACCT_KEY_01 , BTRN.POLH_REPL_POLICY , BTRN.POLH_DATE_ORIG_IN , BTRN.POLH_POL_PAY_PLAN , BTRN.POLH_POL_BRANCH , BTRN.POLH_PROVINCE , BTRN.POLH_SPECIAL_COMM , BTRN.POLH_METHOD , BTRN.POLH_TRANS_CODE , BTRN.POLH_DATE_EXPIRES , BTRN.POLH_BILL_TYPE , BTRN.POLH_HAB_ICPB_ORDER_IND , BTRN.POLH_HAB_ICPB_MATCH_IND , BTRN.POLH_PORTFOLIO_TXF , BTRN.POLH_LOYALTY_DATE , BTRN.POLH_PROFILE_NO , BTRN.POLH_CREDIT_SCORE_VALUE , BTRN.POLH_SPEC_RISK_OVR , BTRN.POLH_SPEC_RISK_SYS , BTRN.POLH_NP_CAN_COUNT , BTRN.POLH_CR_TYPE , BTRN.POLH_CR_DATE , BTRN.POLH_MAU_COMPANY , BTRN.POLH_RULE_COMPANY , BTRN.POLH_RI_COMPANY , BTRN.POLH_EXT_RI , BTRN.POLH_EXT_RI_TYPE , BTRN.POLH_INT_RI , BTRN.POLH_INT_RI_TYPE , BTRN.POLH_CREDIT_USED_IND , BTRN.POLH_POLICY_XFER_IND , BTRN.POLH_QUOTE_FACTOR , BTRN.POLH_EXT_MAU_COMP , BTRN.POLH_EXT_QUO_FACTOR , BTRN.POLH_CHANNEL_IND , BTRN.POLH_PORTFOLIO_XFER_IBC_CO , BTRN.POLH_MI_MOVE_IND , BTRN.POLH_PDM_ID , BTRN.POLH_ETL_UPDATE_DTTM , BTRN.POLY_ETL_UPDATE_DTTM , BTRN.POLH_VALID_FROM_DTTM , BTRN.POLH_VALID_TO_DTTM , BTRN.POLY_VALID_FROM_DTTM , BTRN.POLY_VALID_TO_DTTM , BTRN.POLY_DATE_EXPIRES , BTRN.POLY_DATE_INCEPT , BTRN.POLY_POL_SUSPEND , BTRN.POLY_PDM_ID , BTRN.POLY_REASON_TERM , BTRN.BTRN_TRANS_CODE , BTRN.BTRN_RISK_FTP_01 , BTRN.PEDE_SK , BTRN.BTRN_PRM_TYPE_01 , BTRN.BTRN_DATE_EXPIRES , BTRN.RISK_SK , BTRN.BTRN_RSN_CODE_1 , BTRN.BTRN_RSN_CODE_2 , BTRN.BTRN_RSN_CODE_3 , BTRN.RISK_CODE , BTRN.BTRN_VALID_TO_DTTM , BTRN.BTRN_ETL_UPDATE_DTTM , CAST( BTRN.BTRN_VALID_TO_DTTM AS DATE ) - CAST( RSKW.VALID_TO_DTTM AS DATE ) AS DIFF_BTRN_RSKW_VALID_TO_DTTM , NULL AS DRIVEN , BTRN.BTRN_TWO_CAR_01 , BTRN.POLY_TRAN_NO , RSKW.RSKWV1_SK , RSKW.POLY_SK , RSKW.RISK_SK , RSKW.RSKW_TRAN_NO , RSKW.RSKW_VALID_FLAG , RSKW.RSKW_RISK_CLASS , RSKW.RSKW_DATE_TERM , RSKW.RSKW_DATE_EFFECT , RSKW.RSKW_RISK_KEY , RSKW.RSKW_POLY_KEY , RSKW.RSKW_RI_METHOD , RSKW.RSKW_PROVINCE_V1 , RSKW.RSKW_TERRITORY_V1 , RSKW.RSKW_STAT_LOC_V1 , RSKW.RSKW_MODEL_YR_V1 , RSKW.RSKW_SER_NO_V1 , RSKW.RSKW_RATE_GRP_A_V1 , RSKW.RSKW_RATE_GRP_B_V1 , RSKW.RSKW_RATE_GRP_C_V1 , RSKW.RSKW_CYL_V1 , RSKW.RSKW_BODY_CODE_V1 , RSKW.RSKW_PRICE_V1 , RSKW.RSKW_FUEL_TYP_V1 , RSKW.RSKW_KM_WORK_V1 , RSKW.RSKW_KM_YEAR_V1 , RSKW.RSKW_RG_TYPE_A_V1 , RSKW.RSKW_RG_TYPE_B_V1 , RSKW.RSKW_RG_TYPE_C_V1 , RSKW.RSKW_RG_TYPE_D_V1 , RSKW.RSKW_DSC_CODE_V1_1 , RSKW.RSKW_FACILITY_V1 , RSKW.RSKW_VEHICLE_MAKE_V1 , RSKW.RSKW_CAR_DSC_V1 , RSKW.RSKW_BODY_V1 , RSKW.RSKW_KM_WEEK_V1 , RSKW.RSKW_UNREP_DAM_V1 , RSKW.RSKW_CUST_MOD_V1 , RSKW.RSKW_REG_OWN_V1 , RSKW.RSKW_SPEC_USE_V1 , RSKW.RSKW_NEW_USED_V1 , RSKW.RSKW_ACQ_DT_V1 , RSKW.RSKW_POSTAL_CODE_V1 , RSKW.RSKW_VEHICLE_TYPE_V1 , RSKW.RSKW_BUS_USE_PCT_V1 , RSKW.RSKW_RATE_METH_V1 , RSKW.RSKW_USE_V1 , RSKW.RSKW_CAR_CODE_V1 , RSKW.ETL_UPDATE_DTTM , RSKW.VALID_FROM_DTTM , RSKW.VALID_TO_DTTM , RSKW.RSKW_GRID_CAP_DIFF_V1 , RSKW.RSKW_GRID_LEVEL_V1 , RSKW.RSKW_MVD_IND_V1 , RSKW.RSKW_CAP_IND_V1 , RSKW.RSKW_CAP_EXCEPT_V1 , RSKW.RSKW_MAX_PREM_V1 , RSKW.RSKW_RATE_GRP_D_V1 , RSKW.RSKW_CODE

     FROM sandbox.RSKWV1_PPI RSKW 

     INNER JOIN 

     SAC_VT_1  BTRN

     ON BTRN.RISK_SK = RSKW.RISK_SK 

     WHERE RSKW.PDM_ID IN ( 'GW' , 'EL' ) 

     AND ( BTRN.BTRN_RSN_CODE_1 = 'DV' OR BTRN.BTRN_RSN_CODE_2 = 'DV' OR BTRN.BTRN_RSN_CODE_3 = 'DV' ) 

     AND RSKW.RSKW_DATE_TERM  =  BTRN.BTRN_DATE_EFF_01 

     AND RSKW.ETL_UPDATE_DTTM  BETWEEN '2015/03/01 00:00:00' AND '2015/04/30 23:59:59' 

     and  BTRN .BTRN_ETL_UPDATE_DTTM BETWEEN  '2015/03/01 00:00:00' AND '2015/04/30 23:59:59'

)

  1) First, we lock a distinct sandbox."pseudo table" for read on a

     RowHash to prevent global deadlock for sandbox.RSKW. 

  2) Next, we lock sandbox.RSKW for read. 

  3) We do an all-AMPs RETRIEVE step from 61 partitions of sandbox.RSKW

     with a condition of ("(sandbox.RSKW.ETL_UPDATE_DTTM <= TIMESTAMP

     '2015-04-30 23:59:59') AND (((sandbox.RSKW.PDM_ID = 'GW') OR

     (sandbox.RSKW.PDM_ID = 'EL')) AND (sandbox.RSKW.ETL_UPDATE_DTTM >=

     TIMESTAMP '2015-03-01 00:00:00'))") into Spool 2 (all_amps), which

     is redistributed by the hash code of (sandbox.RSKW.RISK_SK) to all

     AMPs.  Then we do a SORT to order Spool 2 by row hash.  The size

     of Spool 2 is estimated with low confidence to be 5,943,431 rows (

     1,313,498,251 bytes).  The estimated time for this step is 38.77

     seconds. 

  4) We do an all-AMPs JOIN step from 61 partitions of DBADMIN.BTRN by

     way of a RowHash match scan with a condition of (

     "(DBADMIN.BTRN.BTRN_ETL_UPDATE_DTTM >= TIMESTAMP '2015-03-01

     00:00:00') AND (DBADMIN.BTRN.BTRN_ETL_UPDATE_DTTM <= TIMESTAMP

     '2015-04-30 23:59:59')"), which is joined to Spool 2 by way of a

     RowHash match scan.  DBADMIN.BTRN and Spool 2 are joined using a

     sliding-window merge join, with a join condition of (

     "DBADMIN.BTRN.RISK_SK = RISK_SK").  The result goes into Spool 1

     (all_amps), which is redistributed by the hash code of (

     sandbox.RSKW.RSKW_CODE, sandbox.RSKW.RSKW_RATE_GRP_D_V1,

     sandbox.RSKW.RSKW_MAX_PREM_V1, sandbox.RSKW.RSKW_CAP_EXCEPT_V1,

     sandbox.RSKW.RSKW_CAP_IND_V1, sandbox.RSKW.RSKW_MVD_IND_V1,

     sandbox.RSKW.RSKW_GRID_LEVEL_V1,

     sandbox.RSKW.RSKW_GRID_CAP_DIFF_V1, sandbox.RSKW.VALID_TO_DTTM,

     sandbox.RSKW.VALID_FROM_DTTM, sandbox.RSKW.ETL_UPDATE_DTTM,

     sandbox.RSKW.RSKW_CAR_CODE_V1, sandbox.RSKW.RSKW_USE_V1,

     sandbox.RSKW.RSKW_RATE_METH_V1, sandbox.RSKW.RSKW_BUS_USE_PCT_V1,

     sandbox.RSKW.RSKW_VEHICLE_TYPE_V1,

     sandbox.RSKW.RSKW_POSTAL_CODE_V1, sandbox.RSKW.RSKW_ACQ_DT_V1,

     sandbox.RSKW.RSKW_NEW_USED_V1, sandbox.RSKW.RSKW_SPEC_USE_V1,

     sandbox.RSKW.RSKW_REG_OWN_V1, sandbox.RSKW.RSKW_CUST_MOD_V1,

     sandbox.RSKW.RSKW_UNREP_DAM_V1, sandbox.RSKW.RSKW_KM_WEEK_V1,

     sandbox.RSKW.RSKW_BODY_V1, sandbox.RSKW.RSKW_CAR_DSC_V1,

     sandbox.RSKW.RSKW_VEHICLE_MAKE_V1, sandbox.RSKW.RSKW_FACILITY_V1,

     sandbox.RSKW.RSKW_DSC_CODE_V1_1, sandbox.RSKW.RSKW_RG_TYPE_D_V1,

     sandbox.RSKW.RSKW_RG_TYPE_C_V1, sandbox.RSKW.RSKW_RG_TYPE_B_V1,

     sandbox.RSKW.RSKW_RG_TYPE_A_V1, sandbox.RSKW.RSKW_KM_YEAR_V1,

     sandbox.RSKW.RSKW_KM_WORK_V1, sandbox.RSKW.RSKW_FUEL_TYP_V1,

     sandbox.RSKW.RSKW_PRICE_V1, sandbox.RSKW.RSKW_BODY_CODE_V1,

     sandbox.RSKW.RSKW_CYL_V1, sandbox.RSKW.RSKW_RATE_GRP_C_V1,

     sandbox.RSKW.RSKW_RATE_GRP_B_V1, sandbox.RSKW.RSKW_RATE_GRP_A_V1,

     sandbox.RSKW.RSKW_SER_NO_V1, sandbox.RSKW.RSKW_MODEL_YR_V1,

     sandbox.RSKW.RSKW_STAT_LOC_V1, sandbox.RSKW.RSKW_TERRITORY_V1,

     sandbox.RSKW.RSKW_PROVINCE_V1, sandbox.RSKW.RSKW_RI_METHOD,

     sandbox.RSKW.RSKW_POLY_KEY, sandbox.RSKW.RSKW_RISK_KEY,

     sandbox.RSKW.RSKW_DATE_EFFECT, sandbox.RSKW.RSKW_DATE_TERM,

     sandbox.RSKW.RSKW_RISK_CLASS, sandbox.RSKW.RSKW_VALID_FLAG,

     sandbox.RSKW.RSKW_TRAN_NO, sandbox.RSKW.RISK_SK,

     sandbox.RSKW.POLY_SK, sandbox.RSKW.RSKWV1_SK,

     DBADMIN.BTRN.POLY_TRAN_NO, DBADMIN.BTRN.BTRN_TWO_CAR_01, NULL,

     (CAST((DBADMIN.BTRN.BTRN_VALID_TO_DTTM) AS DATE))-

     (CAST((sandbox.RSKW.VALID_TO_DTTM) AS DATE)),

     DBADMIN.BTRN.BTRN_ETL_UPDATE_DTTM, DBADMIN.BTRN.BTRN_VALID_TO_DTTM,

     DBADMIN.BTRN.RISK_CODE, DBADMIN.BTRN.BTRN_RSN_CODE_3,

     DBADMIN.BTRN.BTRN_RSN_CODE_2, DBADMIN.BTRN.BTRN_RSN_CODE_1,

     DBADMIN.BTRN.RISK_SK, DBADMIN.BTRN.BTRN_DATE_EXPIRES,

     DBADMIN.BTRN.BTRN_PRM_TYPE_01, DBADMIN.BTRN.PEDE_SK,

     DBADMIN.BTRN.BTRN_RISK_FTP_01, DBADMIN.BTRN.BTRN_TRANS_CODE,

     DBADMIN.BTRN.POLY_REASON_TERM, DBADMIN.BTRN.POLY_PDM_ID,

     DBADMIN.BTRN.POLY_POL_SUSPEND, DBADMIN.BTRN.POLY_DATE_INCEPT,

     DBADMIN.BTRN.POLY_DATE_EXPIRES, DBADMIN.BTRN.POLY_VALID_TO_DTTM,

     DBADMIN.BTRN.POLY_VALID_FROM_DTTM, DBADMIN.BTRN.POLH_VALID_TO_DTTM,

     DBADMIN.BTRN.POLH_VALID_FROM_DTTM,

     DBADMIN.BTRN.POLY_ETL_UPDATE_DTTM,

     DBADMIN.BTRN.POLH_ETL_UPDATE_DTTM, DBADMIN.BTRN.POLH_PDM_ID,

     DBADMIN.BTRN.POLH_MI_MOVE_IND,

     DBADMIN.BTRN.POLH_PORTFOLIO_XFER_IBC_CO,

     DBADMIN.BTRN.POLH_CHANNEL_IND, DBADMIN.BTRN.POLH_EXT_QUO_FACTOR,

     DBADMIN.BTRN.POLH_EXT_MAU_COMP, DBADMIN.BTRN.POLH_QUOTE_FACTOR,

     DBADMIN.BTRN.POLH_POLICY_XFER_IND,

     DBADMIN.BTRN.POLH_CREDIT_USED_IND, DBADMIN.BTRN.POLH_INT_RI_TYPE,

     DBADMIN.BTRN.POLH_INT_RI, DBADMIN.BTRN.POLH_EXT_RI_TYPE,

     DBADMIN.BTRN.POLH_EXT_RI, DBADMIN.BTRN.POLH_RI_COMPANY,

     DBADMIN.BTRN.POLH_RULE_COMPANY, DBADMIN.BTRN.POLH_MAU_COMPANY,

     DBADMIN.BTRN.POLH_CR_DATE, DBADMIN.BTRN.POLH_CR_TYPE,

     DBADMIN.BTRN.POLH_NP_CAN_COUNT, DBADMIN.BTRN.POLH_SPEC_RISK_SYS,

     DBADMIN.BTRN.POLH_SPEC_RISK_OVR,

     DBADMIN.BTRN.POLH_CREDIT_SCORE_VALUE, DBADMIN.BTRN.POLH_PROFILE_NO,

     DBADMIN.BTRN.POLH_LOYALTY_DATE, DBADMIN.BTRN.POLH_PORTFOLIO_TXF,

     DBADMIN.BTRN.POLH_HAB_ICPB_MATCH_IND,

     DBADMIN.BTRN.POLH_HAB_ICPB_ORDER_IND, DBADMIN.BTRN.POLH_BILL_TYPE,

     DBADMIN.BTRN.POLH_DATE_EXPIRES, DBADMIN.BTRN.POLH_TRANS_CODE,

     DBADMIN.BTRN.POLH_METHOD, DBADMIN.BTRN.POLH_SPECIAL_COMM,

     DBADMIN.BTRN.POLH_PROVINCE, DBADMIN.BTRN.POLH_POL_BRANCH,

     DBADMIN.BTRN.POLH_POL_PAY_PLAN, DBADMIN.BTRN.POLH_DATE_ORIG_IN,

     DBADMIN.BTRN.POLH_REPL_POLICY, DBADMIN.BTRN.POLH_ACCT_KEY_01,

     DBADMIN.BTRN.POLH_POLY_KEY_01, DBADMIN.BTRN.POLH_POL_STATUS,

     DBADMIN.BTRN.POLH_VALID_FLAG, DBADMIN.BTRN.POLY_POLY_SK,

     DBADMIN.BTRN.POLH_DATE_INCEPT, DBADMIN.BTRN.POLH_TRAN_NO,

     DBADMIN.BTRN.POLH_DATE_EFFECT, DBADMIN.BTRN.STEP,

     DBADMIN.BTRN.BTRN_CANC_FLAG_01, DBADMIN.BTRN.BTRN_DATE_EFF_01,

     DBADMIN.BTRN.BTRN_DATE_INC_01, DBADMIN.BTRN.BTRN_TRANS_NO_01,

     DBADMIN.BTRN.BTRN_TRAN_NO, DBADMIN.BTRN.BTRN_FIN_CHRG_01,

     DBADMIN.BTRN.BTRN_NWFL_TAX_01, DBADMIN.BTRN.BTRN_BROKER_FEE,

     DBADMIN.BTRN.BTRN_POLY_SK, DBADMIN.BTRN.BTRN_SK,

     DBADMIN.BTRN.POLH_POLY_SK, DBADMIN.BTRN.POLH_POLH_SK) to all AMPs. 

     Then we do a SORT to order Spool 1 by row hash and the sort key in

     spool field1 eliminating duplicate rows.  The size of Spool 1 is

     estimated with low confidence to be 14,865,975 rows (

     24,618,054,600 bytes).  The estimated time for this step is 2

     minutes and 21 seconds. 

  5) We execute the following steps in parallel. 

       1) We do an all-AMPs JOIN step from 61 partitions of

          DBADMIN.BTRN by way of a RowHash match scan with a condition

          of ("((DBADMIN.BTRN.BTRN_RSN_CODE_3 < 'DV') OR

          ((DBADMIN.BTRN.BTRN_RSN_CODE_3 IS NULL) OR

          (DBADMIN.BTRN.BTRN_RSN_CODE_3 > 'DV'))) AND

          ((DBADMIN.BTRN.BTRN_ETL_UPDATE_DTTM >= TIMESTAMP '2015-03-01

          00:00:00') AND ((DBADMIN.BTRN.BTRN_ETL_UPDATE_DTTM

          <=TIMESTAMP '2015-04-30 23:59:59') AND

          (((DBADMIN.BTRN.BTRN_RSN_CODE_2 < 'DV') OR

          ((DBADMIN.BTRN.BTRN_RSN_CODE_2 IS NULL) OR

          (DBADMIN.BTRN.BTRN_RSN_CODE_2 > 'DV'))) AND

          ((DBADMIN.BTRN.BTRN_RSN_CODE_1 < 'DV') OR

          ((DBADMIN.BTRN.BTRN_RSN_CODE_1 IS NULL) OR

          (DBADMIN.BTRN.BTRN_RSN_CODE_1 > 'DV'))))))"), which is joined

          to Spool 2 (Last Use) by way of a RowHash match scan. 

          DBADMIN.BTRN and Spool 2 are joined using a sliding-window

          merge join, with a join condition of ("(DBADMIN.BTRN.RISK_SK

          = RISK_SK) AND ((RSKW_DATE_EFFECT <=

          DBADMIN.BTRN.BTRN_DATE_EFF_01) AND ((RSKW_DATE_EFFECT <

          DBADMIN.BTRN.POLH_DATE_EXPIRES) AND ((RSKW_DATE_EFFECT >=

          DBADMIN.BTRN.POLH_DATE_INCEPT) AND (RSKW_DATE_TERM >

          DBADMIN.BTRN.BTRN_DATE_EFF_01 ))))").  The result goes into

          Spool 3 (all_amps), which is redistributed by the hash code

          of (sandbox.RSKW.RSKW_CODE, sandbox.RSKW.RSKW_RATE_GRP_D_V1,

          sandbox.RSKW.RSKW_MAX_PREM_V1,

          sandbox.RSKW.RSKW_CAP_EXCEPT_V1, sandbox.RSKW.RSKW_CAP_IND_V1,

          sandbox.RSKW.RSKW_MVD_IND_V1, sandbox.RSKW.RSKW_GRID_LEVEL_V1,

          sandbox.RSKW.RSKW_GRID_CAP_DIFF_V1,

          sandbox.RSKW.VALID_TO_DTTM, sandbox.RSKW.VALID_FROM_DTTM,

          sandbox.RSKW.ETL_UPDATE_DTTM, sandbox.RSKW.RSKW_CAR_CODE_V1,

          sandbox.RSKW.RSKW_USE_V1, sandbox.RSKW.RSKW_RATE_METH_V1,

          sandbox.RSKW.RSKW_BUS_USE_PCT_V1,

          sandbox.RSKW.RSKW_VEHICLE_TYPE_V1,

          sandbox.RSKW.RSKW_POSTAL_CODE_V1, sandbox.RSKW.RSKW_ACQ_DT_V1,

          sandbox.RSKW.RSKW_NEW_USED_V1, sandbox.RSKW.RSKW_SPEC_USE_V1,

          sandbox.RSKW.RSKW_REG_OWN_V1, sandbox.RSKW.RSKW_CUST_MOD_V1,

          sandbox.RSKW.RSKW_UNREP_DAM_V1, sandbox.RSKW.RSKW_KM_WEEK_V1,

          sandbox.RSKW.RSKW_BODY_V1, sandbox.RSKW.RSKW_CAR_DSC_V1,

          sandbox.RSKW.RSKW_VEHICLE_MAKE_V1,

          sandbox.RSKW.RSKW_FACILITY_V1,

          sandbox.RSKW.RSKW_DSC_CODE_V1_1,

          sandbox.RSKW.RSKW_RG_TYPE_D_V1,

          sandbox.RSKW.RSKW_RG_TYPE_C_V1,

          sandbox.RSKW.RSKW_RG_TYPE_B_V1,

          sandbox.RSKW.RSKW_RG_TYPE_A_V1, sandbox.RSKW.RSKW_KM_YEAR_V1,

          sandbox.RSKW.RSKW_KM_WORK_V1, sandbox.RSKW.RSKW_FUEL_TYP_V1,

          sandbox.RSKW.RSKW_PRICE_V1, sandbox.RSKW.RSKW_BODY_CODE_V1,

          sandbox.RSKW.RSKW_CYL_V1, sandbox.RSKW.RSKW_RATE_GRP_C_V1,

          sandbox.RSKW.RSKW_RATE_GRP_B_V1,

          sandbox.RSKW.RSKW_RATE_GRP_A_V1, sandbox.RSKW.RSKW_SER_NO_V1,

          sandbox.RSKW.RSKW_MODEL_YR_V1, sandbox.RSKW.RSKW_STAT_LOC_V1,

          sandbox.RSKW.RSKW_TERRITORY_V1, sandbox.RSKW.RSKW_PROVINCE_V1,

          sandbox.RSKW.RSKW_RI_METHOD, sandbox.RSKW.RSKW_POLY_KEY,

          sandbox.RSKW.RSKW_RISK_KEY, sandbox.RSKW.RSKW_DATE_EFFECT,

          sandbox.RSKW.RSKW_DATE_TERM, sandbox.RSKW.RSKW_RISK_CLASS,

          sandbox.RSKW.RSKW_VALID_FLAG, sandbox.RSKW.RSKW_TRAN_NO,

          sandbox.RSKW.RISK_SK, sandbox.RSKW.POLY_SK,

          sandbox.RSKW.RSKWV1_SK, DBADMIN.BTRN.POLY_TRAN_NO,

          DBADMIN.BTRN.BTRN_TWO_CAR_01, NULL,

          (CAST((DBADMIN.BTRN.BTRN_VALID_TO_DTTM) AS DATE))-

          (CAST((sandbox.RSKW.VALID_TO_DTTM) AS DATE)),

          DBADMIN.BTRN.BTRN_ETL_UPDATE_DTTM,

          DBADMIN.BTRN.BTRN_VALID_TO_DTTM, DBADMIN.BTRN.RISK_CODE,

          DBADMIN.BTRN.BTRN_RSN_CODE_3, DBADMIN.BTRN.BTRN_RSN_CODE_2,

          DBADMIN.BTRN.BTRN_RSN_CODE_1, DBADMIN.BTRN.RISK_SK,

          DBADMIN.BTRN.BTRN_DATE_EXPIRES, DBADMIN.BTRN.BTRN_PRM_TYPE_01,

          DBADMIN.BTRN.PEDE_SK, DBADMIN.BTRN.BTRN_RISK_FTP_01,

          DBADMIN.BTRN.BTRN_TRANS_CODE, DBADMIN.BTRN.POLY_REASON_TERM,

          DBADMIN.BTRN.POLY_PDM_ID, DBADMIN.BTRN.POLY_POL_SUSPEND,

          DBADMIN.BTRN.POLY_DATE_INCEPT, DBADMIN.BTRN.POLY_DATE_EXPIRES,

          DBADMIN.BTRN.POLY_VALID_TO_DTTM,

          DBADMIN.BTRN.POLY_VALID_FROM_DTTM,

          DBADMIN.BTRN.POLH_VALID_TO_DTTM,

          DBADMIN.BTRN.POLH_VALID_FROM_DTTM,

          DBADMIN.BTRN.POLY_ETL_UPDATE_DTTM,

          DBADMIN.BTRN.POLH_ETL_UPDATE_DTTM, DBADMIN.BTRN.POLH_PDM_ID,

          DBADMIN.BTRN.POLH_MI_MOVE_IND,

          DBADMIN.BTRN.POLH_PORTFOLIO_XFER_IBC_CO,

          DBADMIN.BTRN.POLH_CHANNEL_IND,

          DBADMIN.BTRN.POLH_EXT_QUO_FACTOR,

          DBADMIN.BTRN.POLH_EXT_MAU_COMP,

          DBADMIN.BTRN.POLH_QUOTE_FACTOR,

          DBADMIN.BTRN.POLH_POLICY_XFER_IND,

          DBADMIN.BTRN.POLH_CREDIT_USED_IND,

          DBADMIN.BTRN.POLH_INT_RI_TYPE, DBADMIN.BTRN.POLH_INT_RI,

          DBADMIN.BTRN.POLH_EXT_RI_TYPE, DBADMIN.BTRN.POLH_EXT_RI,

          DBADMIN.BTRN.POLH_RI_COMPANY, DBADMIN.BTRN.POLH_RULE_COMPANY,

          DBADMIN.BTRN.POLH_MAU_COMPANY, DBADMIN.BTRN.POLH_CR_DATE,

          DBADMIN.BTRN.POLH_CR_TYPE, DBADMIN.BTRN.POLH_NP_CAN_COUNT,

          DBADMIN.BTRN.POLH_SPEC_RISK_SYS,

          DBADMIN.BTRN.POLH_SPEC_RISK_OVR,

          DBADMIN.BTRN.POLH_CREDIT_SCORE_VALUE,

          DBADMIN.BTRN.POLH_PROFILE_NO, DBADMIN.BTRN.POLH_LOYALTY_DATE,

          DBADMIN.BTRN.POLH_PORTFOLIO_TXF,

          DBADMIN.BTRN.POLH_HAB_ICPB_MATCH_IND,

          DBADMIN.BTRN.POLH_HAB_ICPB_ORDER_IND,

          DBADMIN.BTRN.POLH_BILL_TYPE, DBADMIN.BTRN.POLH_DATE_EXPIRES,

          DBADMIN.BTRN.POLH_TRANS_CODE, DBADMIN.BTRN.POLH_METHOD,

          DBADMIN.BTRN.POLH_SPECIAL_COMM, DBADMIN.BTRN.POLH_PROVINCE,

          DBADMIN.BTRN.POLH_POL_BRANCH, DBADMIN.BTRN.POLH_POL_PAY_PLAN,

          DBADMIN.BTRN.POLH_DATE_ORIG_IN, DBADMIN.BTRN.POLH_REPL_POLICY,

          DBADMIN.BTRN.POLH_ACCT_KEY_01, DBADMIN.BTRN.POLH_POLY_KEY_01,

          DBADMIN.BTRN.POLH_POL_STATUS, DBADMIN.BTRN.POLH_VALID_FLAG,

          DBADMIN.BTRN.POLY_POLY_SK, DBADMIN.BTRN.POLH_DATE_INCEPT,

          DBADMIN.BTRN.POLH_TRAN_NO, DBADMIN.BTRN.POLH_DATE_EFFECT,

          DBADMIN.BTRN.STEP, DBADMIN.BTRN.BTRN_CANC_FLAG_01,

          DBADMIN.BTRN.BTRN_DATE_EFF_01, DBADMIN.BTRN.BTRN_DATE_INC_01,

          DBADMIN.BTRN.BTRN_TRANS_NO_01, DBADMIN.BTRN.BTRN_TRAN_NO,

          DBADMIN.BTRN.BTRN_FIN_CHRG_01, DBADMIN.BTRN.BTRN_NWFL_TAX_01,

          DBADMIN.BTRN.BTRN_BROKER_FEE, DBADMIN.BTRN.BTRN_POLY_SK,

          DBADMIN.BTRN.BTRN_SK, DBADMIN.BTRN.POLH_POLY_SK,

          DBADMIN.BTRN.POLH_POLH_SK) to all AMPs.  The size of Spool 3

          is estimated with low confidence to be 19,821,300 rows (

          32,824,072,800 bytes).  The estimated time for this step is 1

          minute and 20 seconds. 

       2) We do an all-AMPs RETRIEVE step from 61 partitions of

          DBADMIN.BTRN with a condition of ("(NOT (DBADMIN.BTRN.RISK_SK

          IS NULL )) AND ((DBADMIN.BTRN.BTRN_ETL_UPDATE_DTTM <=

          TIMESTAMP '2015-04-30 23:59:59') AND

          (((DBADMIN.BTRN.BTRN_RSN_CODE_1 = 'DV') OR

          ((DBADMIN.BTRN.BTRN_RSN_CODE_2 = 'DV') OR

          (DBADMIN.BTRN.BTRN_RSN_CODE_3 = 'DV'))) AND

          (DBADMIN.BTRN.BTRN_ETL_UPDATE_DTTM >= TIMESTAMP '2015-03-01

          00:00:00')))") into Spool 5 (all_amps) fanned out into 5 hash

          join partitions, which is built locally on the AMPs.  The

          size of Spool 5 is estimated with low confidence to be

          414,571 rows (154,220,412 bytes).  The estimated time for

          this step is 11.03 seconds. 

       3) We do an all-AMPs RETRIEVE step from 61 partitions of

          sandbox.RSKW with a condition of ("(NOT

          (sandbox.RSKW.RSKW_DATE_TERM IS NULL )) AND

          ((sandbox.RSKW.ETL_UPDATE_DTTM <= TIMESTAMP '2015-04-30

          23:59:59') AND (((sandbox.RSKW.PDM_ID = 'GW') OR

          (sandbox.RSKW.PDM_ID = 'EL')) AND

          (sandbox.RSKW.ETL_UPDATE_DTTM >= TIMESTAMP '2015-03-01

          00:00:00')))") into Spool 6 (all_amps) fanned out into 5 hash

          join partitions, which is redistributed by the hash code of (

          sandbox.RSKW.RISK_SK) to all AMPs.  The size of Spool 6 is

          estimated with low confidence to be 4,951,516 rows (

          1,094,285,036 bytes).  The estimated time for this step is

          33.41 seconds. 

  6) We do an all-AMPs JOIN step from Spool 5 (Last Use) by way of an

     all-rows scan, which is joined to Spool 6 (Last Use) by way of an

     all-rows scan.  Spool 5 and Spool 6 are joined using a hash join

     of 5 partitions, with a join condition of ("(RSKW_DATE_TERM =

     BTRN_DATE_EFF_01) AND (RISK_SK = RISK_SK)").  The result goes into

     Spool 3 (all_amps), which is redistributed by the hash code of (

     sandbox.RSKW.RSKW_CODE, sandbox.RSKW.RSKW_RATE_GRP_D_V1,

     sandbox.RSKW.RSKW_MAX_PREM_V1, sandbox.RSKW.RSKW_CAP_EXCEPT_V1,

     sandbox.RSKW.RSKW_CAP_IND_V1, sandbox.RSKW.RSKW_MVD_IND_V1,

     sandbox.RSKW.RSKW_GRID_LEVEL_V1,

     sandbox.RSKW.RSKW_GRID_CAP_DIFF_V1, sandbox.RSKW.VALID_TO_DTTM,

     sandbox.RSKW.VALID_FROM_DTTM, sandbox.RSKW.ETL_UPDATE_DTTM,

     sandbox.RSKW.RSKW_CAR_CODE_V1, sandbox.RSKW.RSKW_USE_V1,

     sandbox.RSKW.RSKW_RATE_METH_V1, sandbox.RSKW.RSKW_BUS_USE_PCT_V1,

     sandbox.RSKW.RSKW_VEHICLE_TYPE_V1,

     sandbox.RSKW.RSKW_POSTAL_CODE_V1, sandbox.RSKW.RSKW_ACQ_DT_V1,

     sandbox.RSKW.RSKW_NEW_USED_V1, sandbox.RSKW.RSKW_SPEC_USE_V1,

     sandbox.RSKW.RSKW_REG_OWN_V1, sandbox.RSKW.RSKW_CUST_MOD_V1,

     sandbox.RSKW.RSKW_UNREP_DAM_V1, sandbox.RSKW.RSKW_KM_WEEK_V1,

     sandbox.RSKW.RSKW_BODY_V1, sandbox.RSKW.RSKW_CAR_DSC_V1,

     sandbox.RSKW.RSKW_VEHICLE_MAKE_V1, sandbox.RSKW.RSKW_FACILITY_V1,

     sandbox.RSKW.RSKW_DSC_CODE_V1_1, sandbox.RSKW.RSKW_RG_TYPE_D_V1,

     sandbox.RSKW.RSKW_RG_TYPE_C_V1, sandbox.RSKW.RSKW_RG_TYPE_B_V1,

     sandbox.RSKW.RSKW_RG_TYPE_A_V1, sandbox.RSKW.RSKW_KM_YEAR_V1,

     sandbox.RSKW.RSKW_KM_WORK_V1, sandbox.RSKW.RSKW_FUEL_TYP_V1,

     sandbox.RSKW.RSKW_PRICE_V1, sandbox.RSKW.RSKW_BODY_CODE_V1,

     sandbox.RSKW.RSKW_CYL_V1, sandbox.RSKW.RSKW_RATE_GRP_C_V1,

     sandbox.RSKW.RSKW_RATE_GRP_B_V1, sandbox.RSKW.RSKW_RATE_GRP_A_V1,

     sandbox.RSKW.RSKW_SER_NO_V1, sandbox.RSKW.RSKW_MODEL_YR_V1,

     sandbox.RSKW.RSKW_STAT_LOC_V1, sandbox.RSKW.RSKW_TERRITORY_V1,

     sandbox.RSKW.RSKW_PROVINCE_V1, sandbox.RSKW.RSKW_RI_METHOD,

     sandbox.RSKW.RSKW_POLY_KEY, sandbox.RSKW.RSKW_RISK_KEY,

     sandbox.RSKW.RSKW_DATE_EFFECT, sandbox.RSKW.RSKW_DATE_TERM,

     sandbox.RSKW.RSKW_RISK_CLASS, sandbox.RSKW.RSKW_VALID_FLAG,

     sandbox.RSKW.RSKW_TRAN_NO, sandbox.RSKW.RISK_SK,

     sandbox.RSKW.POLY_SK, sandbox.RSKW.RSKWV1_SK,

     DBADMIN.BTRN.POLY_TRAN_NO, DBADMIN.BTRN.BTRN_TWO_CAR_01, NULL,

     (CAST((DBADMIN.BTRN.BTRN_VALID_TO_DTTM) AS DATE))-

     (CAST((sandbox.RSKW.VALID_TO_DTTM) AS DATE)),

     DBADMIN.BTRN.BTRN_ETL_UPDATE_DTTM, DBADMIN.BTRN.BTRN_VALID_TO_DTTM,

     DBADMIN.BTRN.RISK_CODE, DBADMIN.BTRN.BTRN_RSN_CODE_3,

     DBADMIN.BTRN.BTRN_RSN_CODE_2, DBADMIN.BTRN.BTRN_RSN_CODE_1,

     DBADMIN.BTRN.RISK_SK, DBADMIN.BTRN.BTRN_DATE_EXPIRES,

     DBADMIN.BTRN.BTRN_PRM_TYPE_01, DBADMIN.BTRN.PEDE_SK,

     DBADMIN.BTRN.BTRN_RISK_FTP_01, DBADMIN.BTRN.BTRN_TRANS_CODE,

     DBADMIN.BTRN.POLY_REASON_TERM, DBADMIN.BTRN.POLY_PDM_ID,

     DBADMIN.BTRN.POLY_POL_SUSPEND, DBADMIN.BTRN.POLY_DATE_INCEPT,

     DBADMIN.BTRN.POLY_DATE_EXPIRES, DBADMIN.BTRN.POLY_VALID_TO_DTTM,

     DBADMIN.BTRN.POLY_VALID_FROM_DTTM, DBADMIN.BTRN.POLH_VALID_TO_DTTM,

     DBADMIN.BTRN.POLH_VALID_FROM_DTTM,

     DBADMIN.BTRN.POLY_ETL_UPDATE_DTTM,

     DBADMIN.BTRN.POLH_ETL_UPDATE_DTTM, DBADMIN.BTRN.POLH_PDM_ID,

     DBADMIN.BTRN.POLH_MI_MOVE_IND,

     DBADMIN.BTRN.POLH_PORTFOLIO_XFER_IBC_CO,

     DBADMIN.BTRN.POLH_CHANNEL_IND, DBADMIN.BTRN.POLH_EXT_QUO_FACTOR,

     DBADMIN.BTRN.POLH_EXT_MAU_COMP, DBADMIN.BTRN.POLH_QUOTE_FACTOR,

     DBADMIN.BTRN.POLH_POLICY_XFER_IND,

     DBADMIN.BTRN.POLH_CREDIT_USED_IND, DBADMIN.BTRN.POLH_INT_RI_TYPE,

     DBADMIN.BTRN.POLH_INT_RI, DBADMIN.BTRN.POLH_EXT_RI_TYPE,

     DBADMIN.BTRN.POLH_EXT_RI, DBADMIN.BTRN.POLH_RI_COMPANY,

     DBADMIN.BTRN.POLH_RULE_COMPANY, DBADMIN.BTRN.POLH_MAU_COMPANY,

     DBADMIN.BTRN.POLH_CR_DATE, DBADMIN.BTRN.POLH_CR_TYPE,

     DBADMIN.BTRN.POLH_NP_CAN_COUNT, DBADMIN.BTRN.POLH_SPEC_RISK_SYS,

     DBADMIN.BTRN.POLH_SPEC_RISK_OVR,

     DBADMIN.BTRN.POLH_CREDIT_SCORE_VALUE, DBADMIN.BTRN.POLH_PROFILE_NO,

     DBADMIN.BTRN.POLH_LOYALTY_DATE, DBADMIN.BTRN.POLH_PORTFOLIO_TXF,

     DBADMIN.BTRN.POLH_HAB_ICPB_MATCH_IND,

     DBADMIN.BTRN.POLH_HAB_ICPB_ORDER_IND, DBADMIN.BTRN.POLH_BILL_TYPE,

     DBADMIN.BTRN.POLH_DATE_EXPIRES, DBADMIN.BTRN.POLH_TRANS_CODE,

     DBADMIN.BTRN.POLH_METHOD, DBADMIN.BTRN.POLH_SPECIAL_COMM,

     DBADMIN.BTRN.POLH_PROVINCE, DBADMIN.BTRN.POLH_POL_BRANCH,

     DBADMIN.BTRN.POLH_POL_PAY_PLAN, DBADMIN.BTRN.POLH_DATE_ORIG_IN,

     DBADMIN.BTRN.POLH_REPL_POLICY, DBADMIN.BTRN.POLH_ACCT_KEY_01,

     DBADMIN.BTRN.POLH_POLY_KEY_01, DBADMIN.BTRN.POLH_POL_STATUS,

     DBADMIN.BTRN.POLH_VALID_FLAG, DBADMIN.BTRN.POLY_POLY_SK,

     DBADMIN.BTRN.POLH_DATE_INCEPT, DBADMIN.BTRN.POLH_TRAN_NO,

     DBADMIN.BTRN.POLH_DATE_EFFECT, DBADMIN.BTRN.STEP,

     DBADMIN.BTRN.BTRN_CANC_FLAG_01, DBADMIN.BTRN.BTRN_DATE_EFF_01,

     DBADMIN.BTRN.BTRN_DATE_INC_01, DBADMIN.BTRN.BTRN_TRANS_NO_01,

     DBADMIN.BTRN.BTRN_TRAN_NO, DBADMIN.BTRN.BTRN_FIN_CHRG_01,

     DBADMIN.BTRN.BTRN_NWFL_TAX_01, DBADMIN.BTRN.BTRN_BROKER_FEE,

     DBADMIN.BTRN.BTRN_POLY_SK, DBADMIN.BTRN.BTRN_SK,

     DBADMIN.BTRN.POLH_POLY_SK, DBADMIN.BTRN.POLH_POLH_SK) to all AMPs. 

     Then we do a SORT to order Spool 3 by row hash and the sort key in

     spool field1 eliminating duplicate rows.  The size of Spool 3 is

     estimated with low confidence to be 14,902,835 rows (

     24,679,094,760 bytes).  The estimated time for this step is 1

     minute and 4 seconds. 

  7) We do an all-AMPs JOIN step from Spool 1 (Last Use) by way of an

     all-rows scan, which is joined to Spool 3 (Last Use) by way of an

     all-rows scan.  Spool 1 and Spool 3 are joined using an exclusion

     merge join, with a join condition of ("Field_1 = Field_1").  The

     result goes into Spool 7 (group_amps), which is built locally on

     the AMPs.  The size of Spool 7 is estimated with low confidence to

     be 7,432,988 rows (13,981,450,428 bytes).  The estimated time for

     this step is 9.14 seconds. 

  8) Finally, we send out an END TRANSACTION step to all AMPs involved

     in processing the request.

  -> The contents of Spool 7 are sent back to the user as the result of

     statement 1.  The total estimated time is 5 minutes and 33 seconds. 

-------------------------------------------------

CREATE VOLATILE TABLE SAC_VT_1

AS (SEL POLH_POLH_SK , POLH_POLY_SK , 

BTRN_SK , BTRN_POLY_SK , 

BTRN_BROKER_FEE , BTRN_NWFL_TAX_01 ,

 BTRN_FIN_CHRG_01 , BTRN_TRAN_NO , 

 BTRN_TRANS_NO_01 , BTRN_DATE_INC_01 , 

 BTRN_DATE_EFF_01 , BTRN_CANC_FLAG_01 , 

 STEP , POLH_DATE_EFFECT , POLH_TRAN_NO , 

 POLH_DATE_INCEPT , 

 POLY_POLY_SK , POLH_VALID_FLAG , POLH_POL_STATUS , 

 POLH_POLY_KEY_01 , POLH_ACCT_KEY_01 , POLH_REPL_POLICY , POLH_DATE_ORIG_IN , 

 POLH_POL_PAY_PLAN , POLH_POL_BRANCH , POLH_PROVINCE , POLH_SPECIAL_COMM , POLH_METHOD , 

 POLH_TRANS_CODE , POLH_DATE_EXPIRES , POLH_BILL_TYPE , POLH_HAB_ICPB_ORDER_IND ,

  POLH_HAB_ICPB_MATCH_IND , POLH_PORTFOLIO_TXF , POLH_LOYALTY_DATE , POLH_PROFILE_NO ,

   POLH_CREDIT_SCORE_VALUE , POLH_SPEC_RISK_OVR , POLH_SPEC_RISK_SYS , POLH_NP_CAN_COUNT , 

   POLH_CR_TYPE , POLH_CR_DATE , POLH_MAU_COMPANY , POLH_RULE_COMPANY , POLH_RI_COMPANY , POLH_EXT_RI , POLH_EXT_RI_TYPE , 

   POLH_INT_RI , POLH_INT_RI_TYPE , POLH_CREDIT_USED_IND , POLH_POLICY_XFER_IND , POLH_QUOTE_FACTOR , POLH_EXT_MAU_COMP , 

   POLH_EXT_QUO_FACTOR , POLH_CHANNEL_IND , POLH_PORTFOLIO_XFER_IBC_CO , POLH_MI_MOVE_IND , POLH_PDM_ID , 

   POLH_ETL_UPDATE_DTTM , POLY_ETL_UPDATE_DTTM , POLH_VALID_FROM_DTTM , POLH_VALID_TO_DTTM , 

   POLY_VALID_FROM_DTTM , POLY_VALID_TO_DTTM , POLY_DATE_EXPIRES , POLY_DATE_INCEPT ,

    POLY_POL_SUSPEND , POLY_PDM_ID , POLY_REASON_TERM , BTRN_TRANS_CODE , BTRN_RISK_FTP_01 , 

    PEDE_SK , BTRN_PRM_TYPE_01 , BTRN_DATE_EXPIRES , RISK_SK , BTRN_RSN_CODE_1 , BTRN_RSN_CODE_2 , 

    BTRN_RSN_CODE_3 , RISK_CODE , BTRN_VALID_TO_DTTM , 

    BTRN_ETL_UPDATE_DTTM 

    FROM  sandbox.STG_INS_POLICY_STEP_2_AUTO_PPI  WHERE RISK_CODE = 'V1')

    WITH DATA 

primary index (RISK_SK) 

Partition by  RANGE_N(BTRN_ETL_UPDATE_DTTM BETWEEN  TIMESTAMP'2015-03-01 00:00:00' AND  TIMESTAMP'2016-12-01 23:59:59' EACH INTERVAL '1' day,

No RANGE, UnKNOWN)

ON COMMIT PRESERVE ROWS


---- Table sandbox.STG_INS_POLICY_STEP_2_AUTO_PPI  (Row Count 26455498)

  --- Partition and Primary index subpart

PRIMARY INDEX IDX_STG_INS_POLICY_STEP_2_A (POLH_POLY_SK )

Partition by  RANGE_N(BTRN_ETL_UPDATE_DTTM BETWEEN  TIMESTAMP'2015-03-01 00:00:00' AND  TIMESTAMP'2016-12-01 23:59:59' EACH INTERVAL '1' day,

No RANGE, UnKNOWN)

---- Table sandbox.RSKWV1_PPI  ( Row count 37871745)

PRIMARY INDEX RSKWV1_NUPI ( RSKW_DATE_EFFECT ,RSKW_RISK_KEY,PDM_ID )

Partition by  RANGE_N(ETL_UPDATE_DTTM BETWEEN  TIMESTAMP'2015-03-01 00:00:00' AND  TIMESTAMP'2016-12-01 23:59:59' EACH INTERVAL '1' day,

No RANGE, UnKNOWN)



--------------

Tags (1)
3 REPLIES
Teradata Employee

Re: Sliding Window Merge Join Running for long time

Correct Volatile Table definition, please ignore previous:

create volatile table SAC_VT_1

AS (SEL * FROM  SANDBOX.STG_INS_POLICY_STEP_2_AUtO_PPI WHERE RISK_CODE='V1') WITH DATA

primary index (Risk_SK)

PARTITION RANGE_N(BTRN_ETL_UPDATE_DTTM BETWEEN TIMESTAMP'2015-03-01 00:00:00' and TIMESTAMP'2016-12-01 23:59:59' Each Interval '1' Day, No Range, Unknown)

On Commit Preserve Rows;

Teradata Employee

Re: Sliding Window Merge Join Running for long time

The query does not specify any relationship between the two tables on the dates in the partitioning of the table. Because there is no relationship specified, a sliding window join must be done to join every partition to every partition. Without knowing your data is is hard to know for sure but it appears that the date for the rows that are to be joined is the same in each table and could have an equality condition. This would make the join a direcct merge join that would be quite a lot faster.

Teradata Employee

Re: Sliding Window Merge Join Running for long time

Thanks for your reply,

i rewrote the query using Volatile Tables and it resolved the issue.