AW: Table with ~255+ columns

From: Michael D O'Shea/Woodward Informatics Ltd <woodwardinformatics_at_strychnine.co.uk>
Date: Sun, 6 Dec 2020 12:38:02 +0000
Message-Id: <C925768C-9AE6-4D3C-BC39-67E090D4E774_at_strychnine.co.uk>



> 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 https://jonathanlewis.wordpress.com/2013/05/10/hakan-factor/ <https://jonathanlewis.wordpress.com/2013/05/10/hakan-factor/> 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“, http://secondhalforacle.blogspot.com/2013/10/everything-about-hakan-factor.html <http://secondhalforacle.blogspot.com/2013/10/everything-about-hakan-factor.html> except it seems the origin of the name Hakan.

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

Mike

> Am 06/12/2020 um 10:26 schrieb Jonathan Lewis <jlewisoracle_at_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/ <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 <mailto: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 - 13:38:02 CET

Original text of this message