Re: LOB segment space usage

On Aug 18, 2:48 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> Pulling together various comments from
> your posts, here:
>
> A 2KB block allows 1,988 bytes of space for LOB
> storage - the remaining 60 bytes is the standard block
> overhead plus a few bytes of data specific to the LOB,
> such as the object number, LOB ID, and SCN. So if
> your character set is single byte then you can get 1,988
> characters per block.
>
> As the note you've quoted says, if you use a multibyte character
> set as your database character (or for the National Character
> Set if you use NCLOBs) the data is stored as a fixed two-byte
> width - so you can only store 994 characters per block.
>
> You don't have to worry too much about subtle details with
> block dumps to get some useful information: For example:
>
> create a block using rpad('x',2000) then dump the LOB blocks.
> If the clob has been stored as a single byte character set you will
> see lots of
> 78 78 78 78 78 78
> in the block.
>
> If it's been stored as the fixed two-byte set you will see
> 00 78 00 78 00 78 00 78 00 78
>
> The note you've quoted about:
> "This ensures that there is no storage loss of character data
> in a varying-width format"
> is not trying to say you don't lose space in the data segment,
> it's trying to say that you don't lose detail in the character data.
> For example, you don't end up with code that a character with
> an acute accent (say) and find that it comes back without it's
> accent.
>
> Your comment about multiplying by two is appropriate - for
> multibyte character sets - it's the price you have to pay if you
> need the character precision. Don't forget you also pay in
> whole CHUNKs - a single character LOB stored out of line
> takes up a whole CHUNK, so choose your chunk-size with
> extreme care.
>
> There's an little note on my blog about reviewing LOB storage,
> but I don't think it will tell you anything you don't know already:
>
> http://jonathanlewis.wordpress.com/2007/05/11/lob-sizing/
>
> --
> Regards
>
> Jonathan Lewishttp://jonathanlewis.wordpress.com

Thank you for your clear explanation. I have been thinking it all over again and I see where I was mislead now. I didn't know about the 60K block overhead. So now I know it. And now I get a full picture of what's going on inside lobs.

I have learned a lot new during this "investigation", so I'm quite happy about it now :) Thanks.

• Andrei