Re: massive delete

I have had similar situation. What I did was have the DBA index the field that was part of the delete and also set up a very large rollback seqment that we accessed thru - set transaction use rolback seqment xxxx - command. Deleted 100,000 rows in few minutes (Granted we use a SUN Enterprise 6000 server with 10 CPUs and 4 gigs of ram, but is ran much faster than before we made the changes

ramdan_at_mailexcite.com

>>
>> George Meltser wrote:
>>
>> > I hope somebody can help me.
>> > At night we have to replace about 20% of our data.
>> > First we delete the rows then insert the new ones.
>> > Delete may take any time from 2 to 4 hours.
>> > I did SQL_TRACE many times and was really surprised that amount of time it
>> > takes to delete the same number of rows from the same table changes from

> day


>> > to day dramatically, let's say from 3 min to 28 min or from 54 min to 22.


>> > The load on the server is the same at this time.


>> > What may cause those delays?


>> > None of these tables have chained rows, all are stored in 1 extent, i have


>> > not changed indexes on them for about 6 months.


>> > Is it Rollback segment shrinking?


>> > Thanx in advance,