Delete of small subset on large table

[rwozniak71]

Hello Folks,

I need to delete and reload a small subset of data (3-4,000,000 rows) from a very large table (140,000,000 rows). The large table had indexes and a partition (that cannot be truncated for this purpose).

Any advice on an approach? Most of what I found seems to take forever (cursor loop, delete statement based on selected keys) even when the delete statement uses the index. I tried subpartitioning and the hash partition put all the "sub" records in with the others.

Any advice for speed? I'm running out of options. Suggestions appreciated.

Rob

 

[sem]

Could you provide us with better explanation of "reload"? Replace(update) some data by key? Is that a must to process each record separately? Can it be that your "forever" is caused by improper RBS size or some locking issue?

Regards, Dima

http://seminihin.narod.ru