I have below insert which took longer in production Machine. Sometimes it got stuck. Please help me on this.
I used to kill and re run the job but the query stuck at the same insert query. I checked the "select" and found that there isn’t any record being pulled by the sub query so ideally it is inserting 0 records then also the query is stuck. Not sure why it is running longer.
/*INSERTING INTO SLS_ERROR WHEN THE QTY_ACT IS LESS THAN 0 */INSERT /*+ append */ INTO STSC.SLS_ERROR (DMDUNIT ,DMDGROUP ,RIN_LOC ,DLV_LOC ,SLS_DAT_SRC ,SLS_TRS_TYP ,ZIP_CD ,MDS_STS ,TRS_DT ,WK_STA_DT ,QTY_ACT ,QTY_LST ,QTY_USR ,QTY_LIFT ,SLL_AMT ,SLS_AGG_MTD ,ON_PROMO_FL ,SCM_IND ,ROW_CRT_DT ,ROW_LST_UPD_SRC ,ROW_LST_UPD_DT ,ERROR_CD ,ERROR_TS ,STEP_NO ,ERROR_DESCR)-- added use_nl hint because for some reason Oracle chose to do hash join with SH table first and taking nearly 20 hours to complete.SELECT /*+ index(sh) use_nl(sh) */ -- 3/22/12 PawelS - changed hint to index(sh) because of limiting records to only those from SLS_WRK DMDUNIT ,DMDGROUP ,RIN_LOC ,DLV_LOC ,SLS_DAT_SRC ,SLS_TRS_TYP ,ZIP_CD ,MDS_STS ,TRS_DT ,WK_STA_DT ,QTY_ACT ,QTY_LST ,QTY_USR ,QTY_LIFT ,SLL_AMT ,SLS_AGG_MTD ,ON_PROMO_FL ,SCM_IND ,ROW_CRT_DT ,ROW_LST_UPD_SRC ,SYSDATE ,200 ,SYSDATE ,80 ,'ZERO OR NEGATIVE QUANTITY' -- - added alias sh for the hint above to workFROM STSC.UDT_SLS_HIST shWHERE ROW_LST_UPD_SRC='HFMADJ'AND QTY_ACT<=0-- - added limiting the search to records from SLS_WRKAND (DMDUNIT, RIN_LOC, DLV_LOC, MDS_STS, SLS_TRS_TYP, ZIP_CD, TRS_DT, SCM_IND, SLS_DAT_SRC) IN (SELECT /*+ PARALLEL(SW,18) FULL(SW) */ DMDUNIT, RIN_LOC, DLV_LOC, MDS_STS, SLS_TRS_TYP, ZIP_CD, TRS_DT, SCM_IND, SLS_DAT_SRC FROM STSC.SLS_WRK SW);
Open in new window