Re: massive delete

On Tue, 14 Apr 1998 18:41:36 GMT, ramdan_at_mailexcite.com (joe) wrote:

>
>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,
>>> > Regards,
>>> > George

You may want to do a couple of things. use hints to tell the server how best to proceed with the delete. use plsql to commit each, or a portion, of your deletes. use the analyze statment to make sorts go faster. otherwise You will be using a large temp segment to sort your delete and a large rollback segment to hold the changes before the commit. Received on Wed Apr 15 1998 - 09:51:39 CDT