Re: Table with ~255+ columns

From: Jonathan Lewis <jlewisoracle_at_gmail.com>
Date: Sun, 6 Dec 2020 10:26:01 +0000
Message-ID: <CAGtsp8mWL1gLp4STvVC3DPgJN2EvBaaKe9Z6dmxxzT3ivccz7A_at_mail.gmail.com>



Not made any easier by the fact that it varies with version of Oracle (though after 11.2.?.? it may have become consistent across versions - there are too many to check.
And ctas, insert /*+append */, insert, alter table move can introduce variations, and then there's the possibility of Exadata specials, and
(possibly) side effects of the Hakan factor. You just don't know what you've missed until someone else gets a result that says that you 've missed SOMETHING.

You've reminded me, by the way, of a note that I wrote some time ago that included a demonstration of the differences that might appear. https://jonathanlewis.wordpress.com/2017/05/19/255-columns-2/

There's a parameter in 11.2.0.4 that's relevant: name: _rowlen_for_chaining_threshold
default value: 1000
description: maximum rowlen above which rows may be chained across blocks

Regards
Jonathan Lewis

On Sun, 6 Dec 2020 at 08:49, Lothar Flatz <l.flatz_at_bluewin.ch> wrote:

> 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
>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Sun Dec 06 2020 - 11:26:01 CET

Original text of this message