Re: Table with ~255+ columns

Hi,

I was studying block dumps for several weeks to find out what is going on. In my test it was like that:
On an insert /*+ APPEND */ the db was filling a block bottom  to top(low block address)  and fit as much as possible in one block. On conventional insert it was filling the block top to bottom and was using a new block after a number of row pieces. There seems to be a threshold of some kind.
Alter table move is insert /*+ APPEND */, thus it can be used to consolidate the result of a conventional insert.

Regards

Lothar

**
>
> *Sent:* Wednesday, December 02, 2020 2:44 PM
> *To:* Oracle L
> *Subject:* 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
> <mailto: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>
> [mailto: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 <mailto: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/
> <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