And while we're on the topic of row chaining because of excessive (> 255)
columns, I have seen a table get BIGGER because of a rebuild because when
you do the move Oracle tries to keep all the row pieces of a single row in
the same block as they are re-created, but this can mean a block gets full
prematurely because (e.g.) two rows that were sharing their fragments
across a couple of blocks with another couple of rows both end up "filling"
a block and denying the available space to any other row because no other
row will fit completely in the available free space in their blocks. LIke:
I can fit 5 rows in 4 blocks if I put 1 anda quarter rows per block, but
I'm not going to spread a row over 4 blocks on a new insert, I'll put it in
its own block.
Regards
Jonathan Lewis
On Wed, 2 Dec 2020 at 19:23, Mark W. Farnham <mwf@xxxxxxxx> wrote:
That all makes sense, I DID miss that memo.
I was always clear on the “one step away” part. Which is n+1 for
multi-piece rows maximum where n is the number of pieces, right?
But the un-migration was news to me. I’m actually a bit surprised, since
that goes against the “least work in real time” general rule, but on the
other hand they are changing the block anyway, so the marginal work is very
low and the every time hence forth worth is high.
Now the question about “un-piecing” remains, right?
mwf
*From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Jonathan Lewis
*Sent:* Tuesday, December 01, 2020 6:16 PM
*To:* Oracle L
*Subject:* Re: Table with ~255+ columns
Mladen,
My comment was about migrated rows, not chained rows, ,and I specifically
said that Oracle "MIGHT also do something sensible with chained rows".
There are too many variations that would need investigating with chained
rows (especially with the 11g bugs) to make it worth anyone's time looking
at them unless they really, really, needed to.
The suggestion you've heard, though, SOUNDS LIKE a red herring. Chained
rows (like migrated rows) have not moved - i.e. their rowids have not
changed - therefore enabling row movement should be irrelevant. On the
other hand the chained row pieces have to have their directory entries
deleted if they're going to taken back to their original block, so there
may be some cases where it matters.
Regards
Jonathan Lewis
On Tue, 1 Dec 2020 at 22:41, Mladen Gogala <gogala.mladen@xxxxxxxxx>
wrote:
Hi Jonathan,
I have known about that for some time, but I missed your article. However,
I was told that enabling row movement is a requirement for moving chained
rows back to the original block. You didn't enable row movement in your
example. Is there any difference, can you comment on that?
Regards
On 11/30/20 5:05 PM, Jonathan Lewis wrote:
Mark,
You missed a memo - Oracle can MIGRATE a row back to its original location
on an update:
https://jonathanlewis.wordpress.com/2014/02/10/row-migration/
The note is a bit old, so I really ought to check it for newer versions, I
guess.
Oracle MIGHT also do something sensible with rows that are chained because
of column lengths when the number of columns is no more than 255, but
anything might happen if there are more than 255 non-null columns in the
row.
Regards
Jonathan Lewis
