DELETE statement taking 2 hours.

Database
KVB
Enthusiast

DELETE statement taking 2 hours.

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 NUPI on ACCT_ID in table1 and RANGE partition TRAN_EFF_DT  BETWEEN DATE '2000-01-01' AND DATE '2050-12-31' EACH INTERVAL '1' DAY 

NUPI on ACCT_ID in table2

NUPI on TBL_NM in table3

,KVB

4 REPLIES
Teradata Employee

Re: DELETE statement taking 2 hours.

Hello,

you can check DBQL, particularly DBQLsteps (dbc.qrylogsteps) to see what Explain step is taking too much CPU and I/O.     Either in DBQL, or in Viewpoint - use Rewind functionality, and details of Query Monitor portlet for that query.

Another possible reason that one of the tables could be blocked, so the query could be waiting until the block is released.   

Regards,

Vlad.

Enthusiast

Re: DELETE statement taking 2 hours.

Bikky6,

It may be due to the secondary index ot join index creatd on the that table. Check the table size and any AJIs on it

Teradata Employee

Re: DELETE statement taking 2 hours.

hi, id like to share something. Ive faced a situation like this.  The thing I did was to copy ONLY the records needed into a another table, verify the records, rename tables and asked DBA to do the dropping when viewpoint was clear.  Advise given to me was that dropping is faster than delete - this could be your last option as not every action could be available for you.

you can try checking Explain plan to see if partition elimination happened.  Additionally, you can try rewriting you query as NOT INs are not favorable to me. IMO.  

Hope this helps

Kelvs

Junior Contributor

Re: DELETE statement taking 2 hours.

20 million rows is not a large number, could you post the explain?

Dieter