By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,225 Members | 2,170 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,225 IT Pros & Developers. It's quick & easy.

Deleting LOB values taking very long time !!

P: 6
Hi All,

We have a requirement to archive ( delete and store on a tape device ) the transaction data which is more than 90 days old. Have written the export commands, followed by the delete commands for all the transaction tables. However, I have a table in which 90% of the data is LOBs. The export is working fine on all the tables ( though slower for LOB table ), however, delete is taking some hours for LOB table, in order to delete a few thousand rows.

Have looked for NOT LOGGED INITIALLY option, however, the delete should be within the same transaction within which the table is created.

Any other options ??

Also, would be great if there is any other option for archival of data, other than exports, followed by deletes.

Jan 11 '08 #1
Share this Question
Share on Google+
1 Reply

Expert 100+
P: 297

well, deleting means writing logs in the background, so this is definetely what's time-consuming. Did you already try to break it down to smaller steps, e.g. 26 steps from name=a..z or similar? This could speed up a little bit. Even a Runstats or maybe even Reorg before deleting could help.

How often are you running this export/archive job? How is the ratio 'data deleted' / ' data kept' ? Is the LOB table needed to be kept online while deleting?


Jan 11 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.