All Forums Database
KVB 124 posts Joined 09/12
23 Oct 2013
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

VBurmist 96 posts Joined 12/09
23 Oct 2013

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.

Tdarc1 14 posts Joined 02/12
23 Oct 2013

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

kelvsdotph 14 posts Joined 06/13
26 Oct 2013

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

dnoeth 4628 posts Joined 11/04
26 Oct 2013

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

Dieter

You must sign in to leave a comment.