468,770 Members | 2,186 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,770 developers. It's quick & easy.

Delete Performance improvement

Hello, I have a requirement to delete millions of rows from a table
which has billions of rows. Hence I have coded a korn script to delete
rows recursively and with a commit interval of 10000. However I feel
that the delete is taking some time to delete, hence would it be a good
idea to code a stored procedure to implement the same ?

If you have a similar exp can you please share your thoughts.

Here is the korn script which I'm using for reference.

COUNT=`db2 -x "select count(*) FROM TXN_PART WHERE source_system_cd=8
and dwh_efctv_timstm='2006-05-23-20.00.00.000000' and transaction_id <
25000000 for read only with ur" `
COUNT=`echo $COUNT | cut -d'.' -f1`
echo No of rows present is =$COUNT
while [ $COUNT -gt 0 ]
do
dec_count=10000
db2 "delete from (select row_number() over(order by
dwh_efctv_timstm ) as row_id from card.TXN_PART where dwh_efctv_timstm
= '2006-05-23-20.00.00.000000' and source_system_Cd = 8 and
transaction_id < 25000000 ) where row_id < $dec_count"
db2 "commit"
COUNT=`expr $COUNT - $dec_count`
scriptdate=`date +%Y%m%d%H%M%S`
echo "Time after delete : $scriptdate"
COUNT=`db2 -x "select count(*) FROM card.TXN_PART WHERE
source_system_cd=8 and dwh_efctv_timstm='2006-05-23-20.00.00.000000'
and transaction_id < 25000000 for read only with ur" `
COUNT=`echo $COUNT | cut -d'.' -f1`
scriptdate=`date +%Y%m%d%H%M%S`
echo "Time after count : $scriptdate"
echo No of rows present is =$COUNT
done
Thanks,
Sam.

Jun 15 '06 #1
4 4883
Why the order by?
Try this:
db2 "delete from (select 1 from card.TXN_PART where
dwh_efctv_timstm
= '2006-05-23-20.00.00.000000' and source_system_Cd = 8 and
transaction_id < 25000000 fetch first $dec_count rows only"
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jun 15 '06 #2
Serge Rielau wrote:
Why the order by?
Try this:
db2 "delete from (select 1 from card.TXN_PART where
dwh_efctv_timstm
= '2006-05-23-20.00.00.000000' and source_system_Cd = 8 and
transaction_id < 25000000 fetch first $dec_count rows only"

I missed a ')' :-)

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jun 15 '06 #3
Thanks Serge for your suggestion. Access plan shows a performance
improvement of 50% using the new query.

Sam..

Serge Rielau wrote:
Serge Rielau wrote:
Why the order by?
Try this:
db2 "delete from (select 1 from card.TXN_PART where
dwh_efctv_timstm
= '2006-05-23-20.00.00.000000' and source_system_Cd = 8 and
transaction_id < 25000000 fetch first $dec_count rows only"

I missed a ')' :-)

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/


Jun 16 '06 #4
Sam Durai wrote:
Thanks Serge for your suggestion. Access plan shows a performance
improvement of 50% using the new query.

Theory.... but does it work?

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jun 16 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

18 posts views Thread by Alex Vinokur | last post: by
4 posts views Thread by Aaron | last post: by
15 posts views Thread by MuZZy | last post: by
12 posts views Thread by Lloyd Dupont | last post: by
8 posts views Thread by cppquester | last post: by
2 posts views Thread by Paul McGuire | last post: by
1 post views Thread by CARIGAR | last post: by
1 post views Thread by Marin | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.