Re: CLOB

Hi!

> Basically making a column type as Clob - instead of varchar2(4000) - how
> much extra overhead (as far as bytes/size) will it add to the table size?

When creating table with clob columns, you can specify whether you can allow storing of small lobs inline with the actual data row. The clob data maximum inline limit is 3964 bytes (4000 bytes - 20 bytes for lob locator -16 bytes for lob inode structure), anything larger will go to external lob segment. Note that for varying-width characterset clobs, Oracle converts data to 2-byte fixed character set, so with UTF-8, a letter 'X' will be stored as hex value 00 58 or 58 00 to the datablock, depending on which platform you are. If you store your 'X' into normal varchar field, it'll be stored just as hex value 58, thus in one byte.
So, with variable-width charsets you can actually store only 1982 characters inline with lob column. Note that nclobs are a bit different, they can have even longer "byte-length". Also they always seem to be stored in little endian format, even on Intel platform (that means 00 58 for 'X' not 58 00 like with regular clob).

Note that there's a difference between "enable storage in row" lob which is forced to store its lob item out-of-line (because it's size >3964 bytes) and regular "disable storage in row" lob. In first case, more space is used in a row because inode structure, which points directly to physical lob location. But native out-of-line lobs created with "disable storage in row" do have only lob locator inline, thus needing additional sequential IOs for scanning lob index which directs them to lob item physical location. In other words, inline lobs don't normally need lob index scanning.

>
> I have a table with 6000 rows, and about 500 of them have 1 column with
more
> that 4000 chars, so they need to go to a clob column. now I just want to
> know if I make a clob column and put the bigger column into clob for all
the
> 6000 rows, how much disk space would I waste by doing this, or would
Oracle
> use the space efficient enough on the clobs so that I shouldn't worry
about

With so small table you don't actually have to worry about size at all. Anyway, depending on your data and character set, your table might grow because lob locator&inode structures are bigger than varchar2 control structure (only 1 or 3 bytes stating it's length) and if using variable-width charsets, your inline data will probably grow. On the other hand, large lobs will be stored in lob segment. But: space in lob segments is allocated in chunks, which are multiplies of Oracle blocks. Thus if you have 8kB chunk size for lobs, then every out-of-line lob item will take at least 8k. If lobs size is 9000 bytes, then it takes 16k and so on (situation changes a bit with big lobs).

So, overall size of your data will grow.

> size ? Other question is it true that Clobs are generally slower even the
> data in them are small ?

Querying inline clobs shouldn't be much slower, if you are using indexed access to your table. But since you have very big rows, your buffer cache might get saturated, since in normal envrionment you'd have tens to hundreds of rows in a block, now you got only few. Full table scans and analyzing will of course be slower, since table is much bigger.

Sorry for answering a simple question with too long answer, I got carried away...
Tanel.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).