RE: Table with ~255+ columns

From: Mark W. Farnham <>
Date: Sun, 6 Dec 2020 15:10:02 -0500
Message-ID: <0f3f01d6cc0b$c93971e0$5bac55a0$>


Probably you narrow it by searching for “mathematician Håkan”, and my guess would be to add “sparse” or “sparseness” to the search terms.  

I would have called it “desired rows per block” or des_rpb and let folks just set it as a table attribute on creation, or alter for future (including move), and of course calculate it for you from actuals if you didn’t set it.  

From: [] On Behalf Of Michael D O'Shea/Woodward Informatics Ltd Sent: Sunday, December 06, 2020 7:38 AM
Cc: Oracle L
Subject: AW: Table with ~255+ columns    

> and (possibly) side effects of the Hakan factor.

From where does this name come? I did a quick online search, expecting the top hit to be Wikipedia and something to do with bitmap indexes and Bloom filters, but no, the top hits are Oracle „names“, including Jonathan explaining what it is and side effects etc.  

Furthermore the search hit just a bit down the page is „Everything about Hakan Factor, yes EVERYTHING“, except it seems the origin of the name Hakan.  

Can anyone throw any light onto the source of this name?  


Am 06/12/2020 um 10:26 schrieb Jonathan Lewis <>:  

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.    

There's a parameter in that's relevant:

name: _rowlen_for_chaining_threshold

default value: 1000

description: maximum rowlen above which rows may be chained across blocks      


Jonathan Lewis    

On Sun, 6 Dec 2020 at 08:49, Lothar Flatz <> wrote:


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.



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.  


Jonathan Lewis      

Received on Sun Dec 06 2020 - 21:10:02 CET

Original text of this message