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@xxxxxxxx <mailto:mwf@xxxxxxxx>> 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@xxxxxxxxxxxxx
<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[mailto:oracle-l-bounce@xxxxxxxxxxxxx
<mailto:oracle-l-bounce@xxxxxxxxxxxxx>] *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@xxxxxxxxx <mailto:gogala.mladen@xxxxxxxxx>> 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
