Storage used by LOB store in row vs. out of row
Env: Oracle 8i, Win2K, DB Block size 8K
I have a question on how LOB are stored. Are LOB stored in row more efficient in terms of space than LOB stored outside of the row?
My original TableData + Lob segment size was 8Gig (1.1 Gig data, 6.9 gig LOB Segment)
The reloaded table was 141 Meg + 11.6 Gig
What happened here ? where does the extra 4Gig come from?
---------- Details ---------------------
Original Table
==============
create table HISTORY
(
COL1 INT,
COL2 CLOB
)
/
so the default is PCTUSED 40 PCTFREE 10 and enable storage in row
The default storage clause has a pctincrease of 0
New Table
==============
As part of our tuning exersise, I did the following
1. export the original table
2. drop the table and recreate it using the ddl below
- Since the rows are never updated after insert, i reset the pctfree to 2 pctused to 98 and pctversion to 5
- Most of the CLOB in the beginning are less than 4K ,
but now have mixed size LOB of anywhere from 1K to 500K
In attempt to manage to clob more efficiently with large lob
I've moved the lob into its own tablespace (TBLSPC_LOB)
create table HISTORY (
COL1 INT,
COL2 CLOB
)
pctfree 2
pctused 98
tablespace TBLSPC_DATA
logging
lob ( COL2 )
store as COL2_LOB (
tablespace TBLSPC_LOB
disable storage in row
storage (
initial 30
next 10
pctincrease 0
)
chunk 8192
pctversion 5
nocache
)
/
3. import the data again.