Re: CLOB storage uses more than it need

You've answered your own question correctly. Your average LOB size appears to be 12K

    (that's 60GB / 5M rows)

The smallest unit of space you can allocate for a CLOB is the CHUNK size - so you probably need to rebuild the LOB segment with a more appropriate chunk size - do a statistical analysis of the lobs to pick a best size, trading number of chunks against typical waste space.

By the way - even with a perfect build, you could end up with a 75GB load after some time, as you are allowing 20% of the space to hold old copies of LOBs. (so your 60 GB could end up as being 80% of 75GB).

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Now running 3-day intensive seminars
http://www.jlcomp.demon.co.uk/seminar.html

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

Author of:
Practical Oracle 8i: Building Efficient Databases


Ed Wong wrote in message ...


>I have a 5M records table with a big clob column.  I allocate this


>particular clob column to a single tablespace(nothing else uses it).

>I checked the total size of the clob column it's 60GB.  However, it

>uses 100GB in the tablespace.  Why is it?  Is it because I set the

>chunk to 32k?

>

>Table definition:

>

>CREATE TABLE Result

>(

>        id                      NUMBER(10),

>        len                     NUMBER(10),

>        result                  CLOB,

>        CONSTRAINT pk_Result PRIMARY KEY (id) storage ...

>)

>TABLESPACE prod_data

>storage ...

>LOB (result) STORE AS lob_result

>(

>    TABLESPACE prod_result

>    STORAGE (INITIAL 200M NEXT 200M MINEXTENTS 1

>    MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)

>    CHUNK 32K PCTVERSION 20 CACHE DISABLE STORAGE IN ROW

>    INDEX lobi_result_output

>);