Re: LOB segment space usage

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 Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"andreik" <spamme.andreik_at_gmail.com> wrote in message 
news:1187352338.299959.173910_at_22g2000hsm.googlegroups.com...


> Hello,

>

> can anyway here explain me, what is oracle storing inside the LOB

> segments besides the data which I am telling it to store?

>

>

> I have searched metalink and docs and forums and have found nothing.

> Here -> 

> http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14249/adlob_tables.htm#i1006363

> it says absolutely nothing about such an overhead. On the contrary, is

> it said there that:

> [Varying-width character data in CLOB and NCLOB datatypes is stored in

> an internal format that is compatible with UCS2 Unicode character set

> format. This ensures that there is no storage loss of character data

> in a varying-width format]

>

> note the "no storage loss" part ;)

>

> How can I be estimating my data structures in this case? Such a

> behavior makes this task very difficult if not impossible. Or do I

> need to simply always multiply  by 2? (if so then I want to see it in

> the docs)

> Why would I use oracle to store my large objects, if I know I'm going

> to waste about 2 or 3 times more disk space than storing them simply

> inside a file system...?

>