Re: Table with ~255+ columns

Mark,
One of the comments on a blog note I wrote about catastrophic chaining pointed that it was possible to chain a row across an arbitrary number of blocks - I think the author had pused it to 10,000 by trigger a row chain then repeatedly adding a column and dropping a column ;)

Don't know if that would still happen in 19/20/21, of course.

The big issue is that even with trying to create "realistic" cases it's a very messy one to investigate. I know that in the (distant) past I've Oracle behave as if it doesn't know that there are any "prior" row pieces, and this tends to mean that rows won't "unchain" - but that's still something I'd have to investigate from about 11.2 onwards.

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