Re: Chained Rows VS new extent allocation

"Jack Wang" <nospam_at_nospam.com> wrote in message news:ubTnb.77773$EO3.19635_at_clgrps13...
> Thanks for the precise explanation. One question, if the size of a row to
be updated exceeds one
> single block size, row chain will take place, in which case the row will
be split into two pieces,
> instead of row header and body as to what happens with row migration?

If I understood the question, then yes. If a row is updated so that it no longer fits into a single Oracle block, it will be split (and hence chained).

Regards
HJR
> - Jack
>
> "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message
> news:e7410c46.0310282039.f91593a_at_posting.google.com...
> > mommy dba <mommydba_at_netscape.com> wrote in message
news:<vptj4k7spdi16_at_corp.supernews.com>...
> > > To answer your original question, Oracle prefers to have everything in
> > > one place, so the answer is: Oracle will allocate a new block to fit
> > > the entire record. It will allocate additional space if it cannot
find
> > > room to put it into a spot. Chaining, technically occurs during an
> > > update when the record can no longer fit into it's original location.
> > > Then it will put the remainder in another place.
> >
> >
> > I wish people would get their terminology on this point correct.
> >
> > Row Migration is what happens when you update. A row of a couple of
> > hundred bytes increases in size because you update it. Obviously, such
> > a small row can fit within an Oracle block, so there's no question of
> > breaking it up to have to fit. Trouble is, the block is full of other
> > rows, and there's no room for this row to grow. Therefore, Oracle
> > breaks off all the row data and moves it bodily into another block
> > (potentially a completely new one, but actually any available block
> > that happens to have enough space). It leaves the row header (which
> > gives a row its ROWID) in the original block, though, because it is a
> > fundamental tenet that rows must not change ROWID once they acquire
> > one (a rule breached, incidentally, by Oracle with the 'alter table
> > move' and 'enable row movement' commands... one could wish they'd be a
> > bit more consistent). Therefore, index access to the row requires an
> > extra physical I/O, because the index sends you to the original block,
> > not the block the row has ended up in.
> >
> > That's called row migration, and is caused by you updating a row AND
> > by the fact that the row happened to reside in a block with little or
> > no free space to accomodate that growth.
> >
> > It can be cured, however. Delete the row and re-insert it, and the
> > original row header is deleted, and the new row in its entirety is
> > placed in a suitable block. It can also be prevented, by the simple
> > expedient of leaving wasted space in a block to accomodate row growth
> > induced by updates... and that's the job of PCTFREE.
> >
> > Migration is thus small(ish) rows having to be re-housed, because you
> > forgot to leave room for them to grow.
> >
> > Row Chaining is a totally different kettle of fish. It's what happens
> > when you try and store the movie TITANIC as a BLOB. No matter what
> > compression algorithm you use, you are not going to be able to fit
> > Kate Winslett into an 8K Oracle Block. And forget Celine Dion's
> > interminable number at the end of the film, too. (I wish I could). In
> > other words, the row simply doesn't fit the Oracle block. Not because
> > it's been updated that way, but just because that's the way it is. And
> > it doesn't matter what you set PCTFREE to be, it still isn't going to
> > fit.
> >
> > If a row won't fit an Oracle block, we have to break it into pieces,
> > and store each piece in a block, and link from one block to another so
> > you can retreive the entire data. Hopefully losing Celine Dion on the
> > way, but that's another matter.
> >
> > Row Chaining is therefore a problem of huge data and/or pathetically
> > small Oracle blocks. Therefore, there is no fix for it. Delete the
> > movie and re-insert it, and it still won't fit. Set PCTFREE to
> > anything you like, it still won't fit. The only thing you could do
> > would be to increase the size of your Oracle blocks... but in 8i, that
> > requires the re-creation of the entire database, so that's a tad
> > drastic as solutions go... and it still won't get you housing Celine
> > Dion in a single Oracle block, because she's *enormous*, and Oracle
> > blocks only go up to 32K or 64K at most. In 9i, you could create a new
> > tablespace with a bigger block size, and move the table... but even
> > then, large data is not intrinsically going to fit into an Oracle
> > block, because Oracle blocks can't be absolutely huge.
> >
> > So Migration is fixable. Chaining is not. They are therefore two
> > completely different things: about as similar, indeed, as two
> > completely dissimilar things in a pod.
> >
> > So please try not to use the name of one when you actually mean the
> > other.
> >
> > Regards
> > HJR
>
>
Received on Wed Oct 29 2003 - 13:11:56 CST