Oracle's LOB Introduction

Definition of LOB

Like Oracle, a relational database that is better at dealing with structured data, how does Oracle handle and store unstructured data? Lob (Large object) is an oracle designed to handle semi-structured and unstructured data, a large object data type that can store more than 4000 bytes of string, binary data.

Type of LOB

There are two types of lobs, very internal lob and external lob. The so-called internal lob is where the LOB data is stored in an Oracle data file, and the External lob refers to the operating system that the LOB data is stored outside the database.

Internale LOB includes:

1 CLOB (Character LOB): Used to store lob of string type, such as text and XML file, string has database character encoding;

2 BLOB (Binary LOB): LOB for storing binary types;

3) NCLOB (National Character Lob): Similar to Clob, but the string has been encoded in the country character set.

External LOB includes:

1 BFILE (Binary file Lob): The database stores a pointer to an external file, so it is read-only

Storage of LOB

When we create a table with LOB fields, Oracle automatically establishes two separate segment for LOB fields, one for data and the other for indexes, and they are stored in the table space specified by the corresponding table. Look at the following example:

Sys@test16> CREATE TABLE test.lobtest (b blob,c clob);  
      
Table created.  
      
Sys@test16> Select Owner,table_name,column_name,segment_name,index_name from Dba_lobs 

where Table_name= ' Lobtest ';  
      
OWNER                          table_name                     column_name                  

  segment_name                   index_name  
------------------------------------- ----------------------- ---------------------------

--- ------------------------------ ---------------------- --------  
test                           lobtest                        B                            

  sys_lob0000101291c00001$$      sys_il0000101291c00001$$  
test                           lobtest                        C                            

  sys_lob0000101291c00002$$      sys_il0000101291c00002$$

As shown in the example above, each LOB field corresponds to two segment, where the LOB data is held at the beginning of Sys_lob and indexed to Sys_il.

LOB and other types of conversions

The Char,nchar,varchar2,nvarchar2,nclob type can be converted into CLOB by To_clob.

The TO_LOB can convert long raw to blob,long into CLOB;

You can convert char, Nchar,varchar2,nvarchar2,clob to Nclob by To_nclob.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/