and (possibly) side effects of the Hakan factor.
Am 06/12/2020 um 10:26 schrieb Jonathan Lewis <jlewisoracle@xxxxxxxxx>:
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/ ;
<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@xxxxxxxxxx
<mailto:l.flatz@xxxxxxxxxx>> 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
