Archives of the TeradataForum

Message Posted: Wed, 11 Sep 2013 @ 21:33:17 GMT


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Subj:		Re: Merge delete performance tuning

From:		Adharssh.HS

Hi Roopalini,

From the Tables structure and the explain plan, we can see that the You are trying to mtach the Integer field with the date field.

1) From the query,We can see the date field(LAST_PACE_DT) has been cast to Varchar field but the AS_OF_CALENDAR_ID is a Integer field. When we are not doing any internal cast of datatype, the Optimizer takes an maximum time to cast it.

2) We can have the OPERA_BATCH_CD='JAC' condition in the Sub query itself,instead of Joining the tables and then having the Condition which will spool out.

Below are the queries that will run in TD.

     DELETE T1
     FROM C302_PRD_FS_EDW.F_PACE T1,
     ( sel HOTEL_ID,CAST(CAST(T2.LAST_PACE_DT  AS DATE FORMAT 'YYYYMMDD' ) AS INTEGER) as LPT
     FROM C302_PRD_FS_STAGE.V_E_OPERA_BATCH_CONTROL
     WHERE OPERA_BATCH_CD='JAC'
     ) T2
      WHERE T1.HOTEL_ID= T2.HOTEL_ID
     AND T1.AS_OF_CALENDAR_ID > LPT;

     OR

     DELETE T1
     FROM C302_PRD_FS_EDW.F_PACE T1,
     ( sel HOTEL_ID,CAST(CAST(T2.LAST_PACE_DT  AS DATE FORMAT 'YYYYMMDD' ) AS CHAR(8)) as LPT
     FROM C302_PRD_FS_STAGE.V_E_OPERA_BATCH_CONTROL
     WHERE OPERA_BATCH_CD='JAC'
     ) T2
      WHERE T1.HOTEL_ID= T2.HOTEL_ID
     AND CAST(T1.AS_OF_CALENDAR_ID AS CHAR(8)) > LPT;

     OR

     DELETE T1
     FROM C302_PRD_FS_EDW.F_PACE T1,
     ( sel HOTEL_ID,CAST(CAST(T2.LAST_PACE_DT  AS DATE FORMAT 'YYYYMMDD' ) AS CHAR(8)) as LPT
     FROM C302_PRD_FS_STAGE.V_E_OPERA_BATCH_CONTROL
     WHERE OPERA_BATCH_CD='JAC'
     ) T2
      WHERE T1.HOTEL_ID= T2.HOTEL_ID
     AND CAST(T1.AS_OF_CALENDAR_ID AS CHAR(8)) > LPT;

Thanks & Regards,

Adharssh.


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Attachments

Library

Quick Reference