Oracle Database Discussions

here's an interesting test:

1. I created a tablespace called "smallblock" with 2K blocksize
2. I created a table with a CLOB type field and specified the smallblock tablespace as a storage for the LOB segment:

SCOTT@andrkydb> create table t1 (i int, b clob) lob (b) store as
t1_lob (chunk 2K disable storage in row tablespace smallblock);



3. I insert data into the table, using a bit less than 2K of data for the clob type column:

SCOTT@andrkydb> begin
2 for i in 1..1000 loop
3 insert into t1 values (mod(i,5), rpad('*',2000,'*'));
4 end loop;
5 end;
6 /



4. Now I can see that I have an average of 2000 bytes for each lob item:

SCOTT@andrkydb> select avg(dbms_lob.getlength(b)) from t1;

AVG(DBMS_LOB.GETLENGTH(B))
--------------------------
2000



and that all together they take up:

SCOTT@andrkydb> select sum(dbms_lob.getlength(b)) from t1;

SUM(DBMS_LOB.GETLENGTH(B))
--------------------------
2000000



But when I take a look at how much is the LOB segment actually taking, I get a result, which is being a total mystery to me:

SCOTT@andrkydb> select bytes from dba_segments where segment_name = 'T1_LOB';

BYTES
----------
5242880



What am I missing? Why is LOB segment is being ~2 times bigger than it is required by the data?

I am on 10.2.0.3 EE, Solaris 5.10 sparc 64bit.

Message was edited by:
Andrei Kübar