Deleting LOB values taking very long time !!

[KiranKGone]

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.

Thanks,
Kiran.

[docdiesel]

Hi,

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?

Regards,

Bernd