There are internally stored LOB' s
CLOB : character lobs
BLOB : binary lobs
There are Externally stored LOB
BFILES, we only store a pointer into the database- a directory and a file name - which points to an external file somewhere on a local volume.
Internally stored lobs have some storage attributes
ENABLE STORAGE IN ROW
Rather for small lobs. Stored in the same data blocks as the blocks allocated for the table segment.
Do not bypass buffer cache behaviour and algorithm. ( lob blocks are read into the buffer cache ).
Also undo and redo is generated similar to undo and redo for table and index segments.
if the size exceeds a threshold ( threshold is about 4K ) the lob data is stored in a dedicated lob segment anyway.
DISABLE STORAGE IN ROW
Rather for large lobs. Always stored in n a dedicated lob segment.
If defined with the CACHE attribute then reads an writes do not bypass the buffer cache, lob segment blocks are put in the buffer cache.
If defined with the CACHE READS attribute then only reads put lob segment blocks in the buffer cache
If defined with the NOCACHE attribute then reads an writes bypass the buffer cache and DIRECT PATH READ & DIRECt PATH WRITE approach is used ( temp segments )
Do not bypass buffer cache behaviour and algorithm. ( lob blocks are read into the buffer cache ).
Also undo and redo is generated similar to undo and redo for table and index segments.
if the size exceeds a threshold ( threshold is about 4K ) the lob data is stored in a dedicated lob segment anyway.
CHUNK SIZE
Is an allocation unit, a multiple from the block size of the tablkespace in which the lob segment is built.
Minimum allocation is 1 chunk. Larger chunks can improve read & write performance at the price of some lost space.
PCTVERSION and RETENTION is used for read consistency the higher pctversion or retention the lesser the likelyhood of ORA 1555 at the price of additional space.
CACHE / NOCACHE / CACHE READS
See above. CACHE / NOCACHE / CACHE READS is only relevant for lobs which have the the disable storage in row attribute.
NOCACHE LOGGING / NOCACHE NOLOGGING / CACHE READS LOGGING / CACHE READS NOLOGGING
If lobs are stored inline they actually have the cache logging attribute.
If lobs are not stored inline ( disable storage in row ) they can have :
1) the cache attribute which implies the logging attribute as well. ( redo and undo )
2) the nocache logging attribute : direct path reads and writes happens with redo and undo for the whole block(s)
3) the nocache nologging attribute : direct path reads and writes happens WITHOUT redo and undo ( recovery ! )
4) the cache reads logging attribute : lob segment blocks are put in the database buffer cache only when they are subject of a read operation, writes happens with redo and undo for the whole block(s)
5) the cache reads nologging attribute : lob segment blocks are put in the database buffer cache only when they are subject of a read operation, writes happens WITHOUT redo and undo
With this example we create a heap organized table of which the lob segment
1) is created in a dedicated tablespace
2) with a non default chunk size
3) with the disable storage in row attribute
4) with a non default pctversion
5) with the nocache attribute
6) with the logging attribute
SQL> create table TABLE_T9 ( T9_ID NUMBER(18),
2 T9_SOMEDATE DATE,
3 T9_SOMEFIELD1 VARCHAR(20),
4 T9_SOMEFIELD2 CLOB,
5 constraint PK_T9 primary key ( T9_ID )
6 using index
7 initrans 4
8 tablespace COCONUT_INDX
9 )
10 pctfree 5
11 initrans 4
12 tablespace COCONUT_DATA
13 lob
14 ( T9_SOMEFIELD2 )
15 store as
16 T9_LOB
17 (
18 tablespace COCONUT_LOB
19 disable storage in row
20 chunk 32K
21 pctversion 5
22 nocache
23 logging
24 );
Table created.
We can query user_lobs in order to list the lob attributes
SQL> select table_name,tablespace_name,column_name,segment_name,chunk,pctversion, cache,logging,in_row from user_lobs;
TABLE_NAME TABLESPACE_NAME
COLUMN_NAME
SEGMENT_NAME CHUNK PCTVERSION CACHE LOGGING IN_ROW
TABLE_T9 COCONUT_LOB
T9_SOMEFIELD2
T9_LOB 32768 5 NO YES NO
With this example we create a list partitioned heap organized table of which the lob segment
1) is also partioned and every lob partition is created in the same dedicated tablespace as the dedicated table partition tablespace
2) with a non default chunk size
3) with the disable storage in row attribute
4) with a non default pctversion
5) with the nocache attribute
6) with the logging attribute
create table TABLE_T10 (
T10_ID NUMBER(18),
T10_SOMEDATE DATE,
T10_SOMEFIELD1 VARCHAR(20),
T10_SOMEFIELD2 CLOB,
constraint PK_T10 primary key ( T10_ID )
using index
initrans 4
tablespace COCONUT_INDX
)
pctfree 5
initrans 4
lob
( T10_SOMEFIELD2 )
store as
T10_LOB
(
disable storage in row
chunk 32K
pctversion 5
nocache
logging
)
partition by list
( T10_SOMEFIELD1 )
(
partition T10_P01
values ( '01' )
tablespace COCONUT_DATA_01,
partition T10_P02
values ( '02' )
tablespace COCONUT_DATA_02
);
We can query user_lob_partitions
SQL> select table_name,tablespace_name,column_name,lob_name,chunk,pctversion,cache,logging,in_row from user_lob_partitions;
TABLE_NAME TABLESPACE_NAME
COLUMN_NAME
LOB_NAME CHUNK PCTVERSION CACHE LOGGING IN_ROW
TABLE_T10 COCONUT_DATA_01
T10_SOMEFIELD2
T10_LOB 32768 5 NO YES NO
TABLE_T10 COCONUT_DATA_02
T10_SOMEFIELD2
T10_LOB 32768 5 NO YES NO
See also Lob administration