Re: Table with ~255+ columns

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_at_rsiz.com> 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_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *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_at_gmail.com>
> 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
>
>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l