Merge Script is Taking alot of Time

Database
Teradata Employee

Merge Script is Taking alot of Time

LD tables is merging in final target table and this one merge into script is taking more then 1.5 hrs.

Target Table: MULTISET with UPI, USI and PPI (Size 8.5 TB)

LD Table: SET with PI (3.4 million rows)

Merge Script

MERGE INTO DD_TAB.PREPD_NON_USG USING DD_STG.PREPD_NON_USG_HXC_LD PNU_LD

ON

        PNU_LD.ACCESS_METHOD_ID=DD_TAB.PREPD_NON_USG.ACCESS_METHOD_ID

AND PNU_LD.NON_USG_START_DT=DD_TAB.PREPD_NON_USG.NON_USG_START_DT

AND PNU_LD.NON_USG_CLASS_CD=DD_TAB.PREPD_NON_USG.NON_USG_CLASS_CD

AND PNU_LD.SUB_OFFRNG_ID=DD_TAB.PREPD_NON_USG.SUB_OFFRNG_ID

AND PNU_LD.NON_USG_START_TM=DD_TAB.PREPD_NON_USG.NON_USG_START_TM

WHEN NOT MATCHED THEN

INSERT(

NON_USG_CLASS_CD

,NON_USG_CAT_CD

,NON_USG_START_DT

,NON_USG_START_TM

,SRC_TRANS_ID

,SUB_OFFRNG_ID

--,OLD_SUB_OFFRNG_CD

,ACCESS_METHOD_ID

,SYSTEM_TYPE_CD

,PROCESSING_MODE_CD

,NON_USG_PROVISION_TYPE_CD

,NON_USG_PERIOD_CD

,NON_USG_SCHEDULED_EXPIRY_DTTM

,SUBSCBR_INITIAL_BALANCE

,ACTION_MODE_CD

,NON_USG_ACTION_TYPE_CD

,NEW_EXPIRY_DT

,NEW_REMOVAL_DT

,SUBSCBR_END_BALANCE_AMT

,OFFRNG_ID

,TOTAL_CHARGE_AMT

,TOTAL_TAX_AMT

,NON_USG_SUB_CAT_TYPE_CD

) VALUES

(

PNU_LD.NON_USG_CLASS_CD

,PNU_LD.NON_USG_CAT_CD

,PNU_LD.NON_USG_START_DT

,PNU_LD.NON_USG_START_TM

,PNU_LD.SRC_TRANS_ID

,PNU_LD.SUB_OFFRNG_ID

--,PNU_LD.OLD_SUB_OFFRNG_CD

,PNU_LD.ACCESS_METHOD_ID

,PNU_LD.SYSTEM_TYPE_CD

,PNU_LD.PROCESSING_MODE_CD

,PNU_LD.NON_USG_PROVISION_TYPE_CD

,PNU_LD.NON_USG_PERIOD_CD

,PNU_LD.NON_USG_SCHEDULED_EXPIRY_DTTM

,PNU_LD.SUBSCBR_INITIAL_BALANCE

,PNU_LD.ACTION_MODE_CD

,PNU_LD.NON_USG_ACTION_TYPE_CD

,PNU_LD.NEW_EXPIRY_DT

,PNU_LD.NEW_REMOVAL_DT

,PNU_LD.SUBSCBR_END_BALANCE_AMT

,PNU_LD.OFFRNG_ID

,PNU_LD.TOTAL_CHARGE_AMT

,PNU_LD.TOTAL_TAX_AMT

,PNU_LD.NON_USG_SUB_CAT_TYPE_CD

)LOGGING ALL ERRORS WITH NO LIMIT;

Explain

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

     RowHash to prevent global deadlock for DP_STG.PREPD_NON_USG_HXC_LD. 


  2) Next, we lock a distinct DD_UTL."pseudo table" for write on a


     RowHash to prevent global deadlock for DD_UTL.PREPD_NON_USG_ERR. 


  3) We lock a distinct Dd_TAB."pseudo table" for write on a RowHash to


     prevent global deadlock for Dd_TAB.PREPD_NON_USG. 


  4) We lock DP_STG.PREPD_NON_USG_HXC_LD for read, we lock


     DD_UTL.PREPD_NON_USG_ERR for write, and we lock


     Dd_TAB.PREPD_NON_USG for write. 


  5) We do an all-AMPs RETRIEVE step from DP_STG.PREPD_NON_USG_HXC_LD


     by way of an all-rows scan with no residual conditions into Spool


     1 (used to materialize view, derived table or table function


     PNU_LD) (all_amps) (compressed columns allowed), which is


     redistributed by the rowkey of (


     DP_STG.PREPD_NON_USG_HXC_LD.NON_USG_CLASS_CD,


     DP_STG.PREPD_NON_USG_HXC_LD.NON_USG_START_DT,


     DP_STG.PREPD_NON_USG_HXC_LD.NON_USG_START_TM,


     DP_STG.PREPD_NON_USG_HXC_LD.SUB_OFFRNG_ID,


     DP_STG.PREPD_NON_USG_HXC_LD.ACCESS_METHOD_ID) to all AMPs.  Then


     we do a SORT to partition Spool 1 by rowkey.  The size of Spool 1


     is estimated with high confidence to be 34,320,156 rows (


     9,266,442,120 bytes).  The estimated time for this step is 3.01


     seconds. 


  6) We do an all-AMPs merge with unmatched inserts into


     Dd_TAB.PREPD_NON_USG from Spool 1 (Last Use) with a condition of (


     "(Dd_TAB.PREPD_NON_USG.ACCESS_METHOD_ID = ACCESS_METHOD_ID) AND


     ((Dd_TAB.PREPD_NON_USG.NON_USG_START_DT = NON_USG_START_DT) AND


     ((Dd_TAB.PREPD_NON_USG.NON_USG_CLASS_CD = NON_USG_CLASS_CD) AND


     ((Dd_TAB.PREPD_NON_USG.SUB_OFFRNG_ID = SUB_OFFRNG_ID) AND


     (Dd_TAB.PREPD_NON_USG.NON_USG_START_TM = NON_USG_START_TM ))))"). 


     The number of rows merged is estimated with high confidence to be


     34,320,156 rows. 


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


     in processing the request.


  -> No rows are returned to the user as the result of statement 1. 

Target Table DDL

CREATE MULTISET TABLE dp_tab.prepd_non_usg ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      NON_USG_CLASS_CD BYTEINT NOT NULL,

      NON_USG_CAT_CD SMALLINT COMPRESS (0 ,2 ,6 ,7 ,9 ,12 ,15 ,27 ,50 ,64 ,161 ,164 ,168 ,169 ,172 ),

      NON_USG_START_DT DATE FORMAT 'YYYY-MM-DD',

      NON_USG_START_TM INTEGER FORMAT '99:99:99',

      SRC_TRANS_ID VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('TOPUPAPI','ICS_Renewal','2011081212171848132','ussdfreemissedcall_VAS'),

      SUB_OFFRNG_ID INTEGER,

      ACCESS_METHOD_ID INTEGER,

      SYSTEM_TYPE_CD BYTEINT COMPRESS (0 ,3 ,101 ),

      PROCESSING_MODE_CD BYTEINT COMPRESS (0 ,1 ,3 ),

      NON_USG_PROVISION_TYPE_CD BYTEINT COMPRESS (0 ,1 ,2 ),

      NON_USG_PERIOD_CD BYTEINT COMPRESS (0 ,1 ,-1 ),

      NON_USG_SCHEDULED_EXPIRY_DTTM TIMESTAMP(0),

      SUBSCBR_INITIAL_BALANCE DECIMAL(18,6) COMPRESS (0.000000 ,0.800000 ,0.480000 ,0.160000 ,0.640000 ,0.320000 ,0.330000 ,0.010000 ,0.810000 ,0.490000 ,0.170000 ,0.650000 ,0.660000 ,0.340000 ,0.020000 ,0.820000 ,0.500000 ,0.180000 ,0.670000 ,0.350000 ,0.030000 ,0.830000 ,0.510000 ,0.190000 ,0.200000 ,0.680000 ,0.360000 ,0.040000 ,0.840000 ,0.520000 ,0.530000 ,0.210000 ,0.690000 ,0.370000 ,0.050000 ,0.850000 ,0.860000 ,0.540000 ,0.220000 ,0.700000 ,0.380000 ,0.060000 ,0.070000 ,0.870000 ,0.550000 ,0.230000 ,0.710000 ,0.390000 ,0.400000 ,0.080000 ,0.880000 ,0.560000 ,0.240000 ,0.720000 ,0.730000 ,0.410000 ,0.090000 ,0.890000 ,0.570000 ,0.250000 ,0.740000 ,0.420000 ,0.100000 ,0.580000 ,0.260000 ,0.590000 ,0.270000 ,0.750000 ,0.430000 ,0.110000 ,0.600000 ,0.280000 ,0.760000 ,0.440000 ,0.120000 ,0.610000 ,0.290000 ,0.770000 ,0.450000 ,0.130000 ,0.460000 ,0.140000 ,0.620000 ,0.300000 ,0.780000 ,0.790000 ,0.470000 ,0.150000 ,0.630000 ,0.310000 ),

      ACTION_MODE_CD BYTEINT COMPRESS 0 ,

      NON_USG_ACTION_TYPE_CD BYTEINT COMPRESS (0 ,1 ,3 ),

      NEW_EXPIRY_DT VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('86400','604800','259200','2592000'),

      NEW_REMOVAL_DT DATE FORMAT 'YYYY-MM-DD' COMPRESS ,

      SUBSCBR_END_BALANCE_AMT DECIMAL(18,6) COMPRESS (0.000000 ,0.480000 ,0.160000 ,0.320000 ,0.330000 ,0.010000 ,10.250000 ,0.490000 ,0.170000 ,0.340000 ,0.020000 ,0.500000 ,0.180000 ,0.350000 ,0.030000 ,0.510000 ,0.190000 ,0.200000 ,0.360000 ,0.040000 ,0.520000 ,0.530000 ,0.210000 ,0.370000 ,0.050000 ,0.540000 ,0.220000 ,0.380000 ,0.060000 ,0.070000 ,0.550000 ,0.230000 ,0.390000 ,0.400000 ,0.080000 ,0.560000 ,0.240000 ,0.410000 ,0.090000 ,0.570000 ,10.010000 ,0.250000 ,0.420000 ,0.100000 ,0.580000 ,0.260000 ,0.270000 ,0.430000 ,0.110000 ,0.280000 ,0.440000 ,0.120000 ,10.050000 ,0.290000 ,0.450000 ,0.130000 ,0.460000 ,0.140000 ,0.300000 ,0.470000 ,0.150000 ,0.310000 ),

      OFFRNG_ID SMALLINT COMPRESS (35 ,41 ,42 ,43 ,1340 ,1399 ,1411 ,1412 ,1413 ,1414 ,1415 ,1416 ,1417 ,1420 ,1422 ),

      TOTAL_CHARGE_AMT DECIMAL(18,6) COMPRESS (0.000000 ,8.000000 ,3.004184 ,11.004184 ,0.500000 ,0.050209 ,1.000000 ,8.368201 ,0.502092 ,0.150628 ,10.000000 ,18.000000 ,0.200837 ,10.987448 ,0.753138 ),

      TOTAL_TAX_AMT DECIMAL(18,6) COMPRESS (0.000000 ,2.145816 ,1.950000 ,1.631799 ,0.009791 ,0.200000 ,2.142552 ,0.585816 ,0.097908 ,0.100000 ,0.146862 ,0.029372 ,1.560000 ,3.510000 ,0.039163 ),

      NON_USG_SUB_CAT_TYPE_CD SMALLINT COMPRESS (0 ,2 ,50 ,320 ,622 ,376 ,497 ),

      NON_USAGE_CHRG_TYPE_CD SMALLINT COMPRESS ,

      SEG_ID SMALLINT COMPRESS ,

      RECORD_DETAIL_TYPE_ID SMALLINT COMPRESS ,

      PMT_IND BYTEINT DEFAULT 0  COMPRESS 0 )

UNIQUE PRIMARY INDEX UPI_PREPD_NON_USG ( NON_USG_CLASS_CD ,NON_USG_START_DT ,

NON_USG_START_TM ,SUB_OFFRNG_ID ,ACCESS_METHOD_ID )

PARTITION BY ( RANGE_N(NON_USG_START_DT  BETWEEN DATE '2004-07-01' AND DATE '2015-12-31' EACH INTERVAL '1' DAY ),

RANGE_N(NON_USG_CLASS_CD  BETWEEN -1  AND 7  EACH 1 ) )

INDEX IDX_AMI ( ACCESS_METHOD_ID );


2 REPLIES
Teradata Employee

Re: Merge Script is Taking alot of Time

As per my analysis, SI and 8.5 TB table size is causing the issue.

But still recommendation will be appreciated.

Teradata Employee

Re: Merge Script is Taking alot of Time

How skewed is the distribution of NUSI values? How critical is the NUSI for query?

Could a single-table JI be used instead? (Maybe sparse JI, omitting the highly skewed values?)