Re: Chained Rows VS new extent allocation

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 Tue Oct 28 2003 - 22:39:39 CST