《Oracle编程艺术》学习笔记(37)-数据类型-LOB

1） BLOB
二进制LOB。用于存储大量的二进制信息，不会进行字符集转换。
允许存储最多（4GB）×（创建LOB时指定的CHUNK参数，默认为数据库块大小）字节的数据。

2) CLOB
字符LOB。这种类型用于存储大量的文本信息，要进行字符集转换。
允许存储最多（4GB）×（创建LOB时指定的CHUNK参数，默认为数据库块大小）字节的数据。

3) NCLOB
存储用Unicode编码的信息，而且像CLOB一样，这些信息要进行字符集转换。
允许存储最多（4GB）×（创建LOB时指定的CHUNK参数，默认为数据库块大小）字节的数据。
NCLOB中存储按照NLS_NCHAR_CHARACTERSET参数指定的字符集来编码的数据。

4）BFILE
允许在数据库列中存储一个Oracle目录对象（操作系统目录的一个指针）和一个文件名，并读取这个文件。
实际上允许以一种只读的方式访问数据库服务器上可用的操作系统文件，就好像它们存储在数据库表本身中一样。

BLOB，CLOB和NCLOB
CLOB、BLOB和NCLOB在数据库中的存储，还有创建时的参数选项都是类似的。

tony@ORA11GR2> create table t(x int, y clob) segment creation immediate;
Table created.

tony@ORA11GR2> select segment_name, segment_type from user_segments;
SEGMENT_NAME                     SEGMENT_TYPE
-------------------------------- ------------
T                                TABLE
SYS_IL0000073988C00002$         LOBINDEX
SYS_LOB0000073988C00002$        LOBSEGMENT

可以看到为CLOB创建了2个段，即LOBINDEX和LOBSEGMENT。
LOBINDEX用于执行LOB的导航。创建一个LOB列时，一般来说，存储在行中的这是一个指针（pointer），或LOB 定位器（LOB locator）。
当查询时，将对LOBINDEX使用LOB定位器来找出这些字节存储在哪里，然后再访问LOBSEGMENT。

通过下面语句可以查看LOB可用的属性有哪些。

tony@ORA11GR2> select dbms_metadata.get_ddl('TABLE','T') from dual;
DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------------

  CREATE TABLE "TONY"."T"
   (    "X" NUMBER(*,0),
        "Y" CLOB
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT
  CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
 LOB ("Y") STORE AS BASICFILE (
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
  NOCACHE LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT
 CELL_FLASH_CACHE DEFAULT))

1）STORE AS [BASICFILE/SECUREFILE]
指定BASICFILE/SECUREFILE是11g之后增加的特性。默认为BASICFILE，这也是11g之前的唯一选择。
与高级压缩选项或者高级安全选项结合使用时，SECUREFILE可以提供更多选择（压缩，加密，消除复制）

2）LOB表空间
指定存储LOBINDEX和LOBSEGMENT的表空间。
基于管理和性能的考虑，需要为LOB数据另外指定一个表空间。
LOB数据庞大，另外分配一个表空间有利于备份，恢复，空间管理。
默认情况下，LOB数据不在缓冲区缓存中进行缓存，每次访问都会带来一个物理I/O，这种情况下，把LOB数据单独储存到一个磁盘上就非常有意义。

3）ENABLE/DISABLE STORAGE IN ROW
控制是否将LOB数据和表分开存储。如果使用ENABLE STORAGE IN ROW，这也是默认行为，对于小的LOB(最多4000字节)，就会像VARCHAR2一样直接存储在表中，这会带来性能上的提高。

4）CHUNK
LOB存储在CHUNK中；指向LOB数据的索引会指向各个CHUNK。
CHUNK是逻辑上连续的一组数据库块（block），这也是LOB的最小分配单元，而通常数据库的最小分配单元是数据库块。
CHUNK大小必须是数据库块大小的整数倍。
CHUNK大小必须合适。如果过大，会造成空间浪费；如果过小，会造成CHUNK数很多，这就会造成LOBINDEX很大，降低性能。

5）RETENTION/PCTVERSION
LOB实现读一致性(http://blog.csdn.net/fw0124/article/details/6899246)的方式和一般的表数据有所不同。
LOBINDEX会像其他段一样生成UNDO，但是LOBSEGMENT并不使用UNDO来记录其修改，而是直接在LOBSEGMENT本身中维护数据的版本。
修改一个LOB时，Oracle会分配一个新的CHUNK，并且仍保留原来的CHUNK。如果回滚了事务，对LOB索引所做的修改会回滚，索引将再次指向原来的CHUNK。
Oracle使用LOBINDEX的读一致视图来撤销对LOB的修改，从而获得读一致性。

RETENTION子句告诉Oracle按照UNDO_RETENTION参数来决定将修改过的LOB历史数据保存在LOBSEGMENT中多久的时间。
PCTVERSION子句控制着用于保存修改过的LOB历史数据的存储空间占已分配LOB空间的百分比。

6）CACHE/CACHE READS/NOCACHE
默认为NOCACHE，LOB数据不在缓冲区缓存中进行缓存，每个访问都是对磁盘直接读写。
CACHE READS允许缓存从磁盘读的LOB数据，但是LOB数据的写操作直接写至磁盘。
CACHE则允许读和写时都能缓存LOB数据。
对于频繁读写，并且较小的LOB，比较适合使用缓存。

BFILE
BFILE类型只是操作系统上一个文件的指针。它用于为这些操作系统文件提供只读访问。
可以使用dbms_lob包，把BFILE当成一个LOB来处理。

不过需要注意的是，如果要从BFILE中加载文字数据，需要注意字符集问题。
为此可以使用DBMS_LOB.LOADCLOBFROMFILE过程，它的bfile_csid参数用来指定BFILE的character set id。
可以用下面的语句查询所使用的字符集对应的character set id。
select nls_charset_id(value) charset_id, value charset_name
  from v$nls_valid_values where parameter = 'CHARACTERSET'
  order by value;
例如，通过上面语句查到ZHS16GBK对应的id是852。

在D盘根目录下建立文件test.txt，存入文字，当前是中文系统，因此默认是中文(ZHS16GBK)编码方式。
下面来从此文件读入文本到一个CLOB对象中。

tony@ORA11GR2> create or replace directory os_dir as 'D:\';
Directory created.

tony@ORA11GR2> create table t(os_file bfile, txt clob);
Table created.

tony@ORA11GR2> declare
  2    l_clob clob;
  3    l_file bfile := bfilename('OS_DIR', 'test.txt');
  4    l_dest_offset number := 1;
  5    l_src_offset number := 1;
  6    l_bfile_csid number := 852;
  7    l_lang_context number := 0;
  8    l_warning number := 0;
  9  begin
 10    insert into t(os_file, txt) values(l_file, empty_clob())
 11      returning txt into l_clob;
 12
 13    if (dbms_lob.fileexists(l_file) != 0) then
 14      dbms_lob.fileopen(l_file, dbms_lob.lob_readonly);
 15      dbms_lob.LOADCLOBFROMFILE(
 16          dest_lob => l_clob,
 17          src_bfile => l_file,
 18          amount => dbms_lob.getlength(l_file),
 19          dest_offset => l_dest_offset,
 20          src_offset => l_src_offset,
 21          bfile_csid => l_bfile_csid,
 22          lang_context => l_lang_context,
 23          warning => l_warning);
 24      dbms_lob.fileclose(l_file);
 25    end if;
 26  end;
 27  /

PL/SQL procedure successfully completed.

tony@ORA11GR2> column os_file format a35
tony@ORA11GR2> select * from t;

OS_FILE                             TXT
----------------------------------- --------------------------------------------------
bfilename('OS_DIR', 'test.txt')     查看数据库字符集和National Character字符集的方法。
                                     select * from v$nls_parameters;
                                     NLS_CHARACTERSET - 数据库字符集
                                     NLS_NCHAR_CHARACTERSET - National Character字符集