5 Replies Latest reply: Oct 23, 2013 3:22 PM by jihuyao RSS

    Delete query taking more than 2 hours

    KVB

      Hi

      I have table1 with more than 20 million rows.I am trying to delete some rows based on conditions.I have written a query to delete the rows.Butit is taking almost 2 hours.Is there any other way to achieve this?

       

      DEL FROM TABLE1

      WHERE ID=100 AND SRCE_NM='CHECK'

      AND TRAN_EFF_DT BETWEEN '2012-01-01' AND   '2012-12-31'

      AND (ACCT_ID,TRAN_ID,DW_EFF_DT) NOT IN

      (

      SEL ACCT_ID,TRAN_ID,DW_EFF_DT

      FROM TABLE1  T

      INNER JOIN TABLE2 T2

      ON T.ACCT_ID = T2.ACCT_ID

      AND T.TRAN_EFF_DT BETWEEN T2.DW_EFF_DT AND T2.DW_EXPR_DT

      AND

      COALESCE(BRANCH_CD ,'XX') NOT IN

      (

      SELECT BRANCH_CD

      FROM TABLE3

      WHERE TBL_NM='ACCT'

      AND ENR_NM='TOA')

      WHERE ID=100 AND SRCE_NM='CHECK'

      AND TRAN_EFF_DT BETWEEN '2012-01-01' AND '2012-12-31'

      )

       

      I have indexes on the columns used in where clause and range partition on the date cols used in BETWEEN operator.Is there any other alternative to change the query?

       

      Regards

      KVB