How to efficiently store LOB?

From: yong huang <yong321_at_yahoo.com>
Date: Wed, 13 Sep 2000 11:28:10 -0700 (PDT)
Message-Id: <10618.116875@fatcity.com>

My table has a BLOB field. The table is created with the same parameters as other tables in the tablespace, and in the same tablespace as others. INITIAL and NEXT are 32K, PCTFREE 10, PCTUSED 40, PCTINCREASE 0. There's no special parameter for the BLOB column: no DISABLE STORAGE IN ROW (meaning if the field is ca. 4000 characters or less, it's stored in-line with other column data; otherwise out-of-line somewhere else), no STORE AS (simply meaning Oracle will assign a SYS_blahblah name to the LOB segment), no CHUNK (so Oracle maniputes LOB data in unit of one DB block size). The files loaded into this BLOB field are of variable length. Data in this column could be deleted and inserted. The following is part of the extent map showing my blob storage:

SQL> select substr(segment_name,1,25), substr(segment_type,1,10), extent_id, block_id, bytes, blocks from dba_extents where block_id between 14600 and 14750 order by block_id;

SUBSTR(SEGMENT_NAME,1,25) SUBSTR(SEG EXTENT_ID BLOCK_ID BYTES BLOCKS

------------------------- ---------- --------- --------- --------- ---------
SYS_LOB0000010239C00009$$ LOBSEGMENT      3065     14600     32768         4
SYS_LOB0000010239C00009$$ LOBSEGMENT      2544     14604     32768         4
SYS_LOB0000010239C00009$$ LOBSEGMENT       576     14608     32768         4
SYS_LOB0000010239C00009$$ LOBSEGMENT       492     14612     32768         4
SYS_LOB0000010239C00009$$ LOBSEGMENT      2455     14616     32768         4
SYS_LOB0000010239C00009$$ LOBSEGMENT      2193     14620     32768         4
XPKSTATISTIC              INDEX              1     14632     32768         4
TEXT_ATTACHMENT           TABLE              3     14636     32768         4
SYS_LOB0000010239C00009$$ LOBSEGMENT        97     14640     32768         4
XIF527TABLE_OBJECT        INDEX              0     14644     32768         4
XPKTEXT_ATTACHMENT        INDEX              1     14656     32768         4
SYS_LOB0000010239C00009$$ LOBSEGMENT       104     14668     32768         4
SYS_LOB0000010239C00009$$ LOBSEGMENT       936     14692     32768         4
SYS_LOB0000010239C00009$$ LOBSEGMENT      2781     14696     32768         4
XPKAPPLICATION_FEEDBACK   INDEX              0     14700     32768         4
SYS_LOB0000010239C00009$$ LOBSEGMENT      1197     14704     32768         4
SYS_LOB0000010239C00009$$ LOBSEGMENT       533     14708     32768         4
SYS_LOB0000010239C00009$$ LOBSEGMENT         8     14712     32768         4
ESEISLINE_BAK             TABLE             12     14716     32768         4
SYS_LOB0000010239C00009$$ LOBSEGMENT      2975     14720     32768         4
SYS_LOB0000010239C00009$$ LOBSEGMENT      1903     14724     32768         4
SYS_LOB0000010239C00009$$ LOBSEGMENT       577     14728     32768         4
SAVED_SEARCH_SESSION      TABLE              4     14732     32768         4
SYS_LOB0000010239C00009$$ LOBSEGMENT      1807     14736     32768         4
SYS_LOB0000010239C00009$$ LOBSEGMENT       949     14740     32768         4
SYS_LOB0000010239C00009$$ LOBSEGMENT      1263     14744     32768         4
SYS_LOB0000010239C00009$$ LOBSEGMENT      1457     14748     32768         4

27 rows selected.

SQL> analyze table file_attachment compute statistics;

Table analyzed.

SQL> select chain_cnt from user_tables where table_name = 'FILE_ATTACHMENT';

CHAIN_CNT

SQL> select chain_cnt from user_tables where table_name = 'SYS_LOB0000010239C00009$$'; no rows selected

Does anyone have any suggestion on LOB storage or any comment? Any white paper?

Yong Huang
yong321_at_yahoo.com

Do You Yahoo!?
Yahoo! Mail - Free email you can access from anywhere! Received on Wed Sep 13 2000 - 13:28:10 CDT