19 | 08 | 2017
Latest Articles
Popular Articles

Database Design

Lob Segments

User Rating:  / 0
PoorBest 

 Lob Segments

 

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