Re: Rollback performance and delete

Allen wrote:
>
> > Oracle copies before image copies of all changed blocks into the
> > rollback segment. So if your 1G of deleted data spread over 5G of
> > data blocks, 5G will be written into rollback segment.
>
> So far the above explanation is the only one I have seen to explain this. The
> index I have is about 1 G also, so if I delete 1G of data and 1/5th of the
> index, how does this result in a rollback segment that is >5G in size!
>
> "Howard J. Rogers" wrote:
>
> > "Alex Filonov" <afilonov_at_pro-ns.net> wrote in message
> > news:8u7chm$3mn$1_at_nnrp1.deja.com...
> > > In article <3A06DE5D.30933844_at_motorola.com>,
> > > Allen <allen.brost_at_motorola.com> wrote:
> > > > I have looked through the Oracle documenation to understand exactly
 what the
> > > > rollback segment is doing during a DELETE and I am still a little
 confused with
> > > > the results I am finding.
> > > >
> > > > I am doing a delete of a large table. The table is 5G. I am deleting
 about 1G
> > > > of the the table.
> > > >
> > > > I have created a large rollback segment to handle the transaction and
 I have set
> > > > the size of the rollback segment to about 2G. I figured that this
 should be
> > > > plenty. When I do the delete, I see the rollback segment grow to 5G.
 I don't
> > > > understand this. I thought the rollback segment held all of the
 original rows
> > > > before the change. If this is so, then I would expect to see about 1G
 in the
> > > > segment. It seems like Oracle is copying everything in the table to
 the
> > > > rollback segment. Anyone know why?
> > > >
> > > > - Allen
> > >
> > > Oracle copies before image copies of all changed blocks into the
> > > rollback segment. So if your 1G of deleted data spread over 5G of
> > > data blocks, 5G will be written into rollback segment.
> >
> > If you are suggesting that an update to one record will result in the entire
> > block being copied into rollback, then that's not correct.
> >
> > Regards
> > HJR
> >
> > > >
> > > >
> > >
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.

How many indexes do you have on the table ???

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)

"Early to bed and early to rise, 
 makes a man healthy, wealthy and wise." - some dead guy