Oracle LOB数据类型存储

前段时间项目上线涉及到blob字段在数据库存储问题，现总结一下oracle LOB字段：

 

一 LOB类型：
 CLOB:存储大量、单字节、字符数据，存储在内部表空间。
 NLOB:存储定宽、多字节、字符数据，存储在内部表空间。
 BLOB:存储较大无结构的二进制数据，存储在内部表空间。
 BFILE:将二进制文件存储在数据库外部的操作系统文件中。存放文件路径。

二 测试语句

       测试用户是test，缺省表空间是test_tab、缺省索引表空间是test_ind、缺省临时表空间是test_tmp、bolb字段缺省表空间是test_blob(缺省段名test_lob)。

 

1 创建用户
conn  / as sysdba

create tablespace test_tab  datafile '/test/test_tab01.dbf' size 10m;

create tablespace test_ind  datafile '/test/test_ind01.dbf' size 10m;

create tablespace test_tmp datafile '/test/test_tmp01.dbf' size 10m;

create tablespace test_blob datafile '/test/test_blob01.dbf' size 10m;

create user test identified by test default tablespace test_tab temporary tablespace test_tmp;

grant connect,resource to test; 

 

2 创建含lob字段表

conn test/test

create table student(

                            id  number(8),

                            name varchar2(10),

                            addr  blob,

                            resu  clob,

                            photo bfile

                            )  lob (addr,resu) store as test_lob

                                             (  tablespace test_blob             --指定存储的表空间
                                                 chunk 16k                            --指定数据块大小
                                                 disable storage in  row         ---只存储20字节指针

                                                );

 

对含blob字段表迁移：

select  'alter table  '||t.table_name || ' move tablespace tabespace_name lob('||t.columm_name||') store as (tablespace tablespace_name);' from user_lobs t

 

alter table test move lob (addr,resu) store as test_lob( tablespace test_blob);

3 对lob定义参数说明

enable|disable storage in row

chunk integer

cache|nocache|cache reads

pctversion integer

retention

freepools integer

index lobindexname

enable storage in row : oracle自动将小于4000bytes的数据存储在行内, 对于大于4000字节的lob字段保存在lob段。

disable storage in row: oracle将lob字段保存在lob段中，在行位置保留20字节的指针。对于保存在lob段的这部分数据，undo仅仅是记录指针与相关lob索引改变，如果发生更新操作等dml操作，原始数据将保留在lob段。创建后只能在move表时才可以被改变

chunk:对一次LOB数据的操作(插入或更新)该分配多少存储空间,指定的值最好是数据库块的倍数,而且指定的值不能大于表空间区间中NEXT的值, 要不然ORACLE会return一个错误,如果以前已经设置这个值了,那么在后期指定的值是不能被改变的。

cache|nocahce:表示是否允许lob段经过buffer cache并缓存。默认nocache，不经过数据库的data buffer。
nocache logging |nocache nologging:logging/nologging属性只对nocache方式生效，默认是logging。如果nologging方式，对于保存在行外的log部分，在update等DML操作时将不记录redo日志。

pctversion 、retention:都是oracle用来管理lob字段镜像数据的。在lob 数据的更新过程中, oracle没有用undo tablespace空间，而是从lob字段所在的表空间里划分一段空间来做镜像空间的, 这个空间的大小由pctversion参数控制,默认值为10,代表划分表空间的10%作为镜像空间, 每个镜像空间的单元大小由chunk参数指定,pctversion可以使用在manual undo mode和automatic undo mode 环境中. retention应用了automatic undo mode中的undo_retention通过时间来管理lob镜像空间.
pctversion和retention不能同时被指定.建议数据库在automatic undo mode下使用retention参数。

freepools integer:给log segment指定free list.RAC环境下integer为实例的个数.单实例环境下为1

index lobindexname (tablespace tablesapce_name ((storage.....):给lob列指定索引存储参数

 

三 LOB性能问题

1  lob字段默认生成lobindex和lobsegment。在不指定特定表空间情况下，lob字段索引存储到系统表空间下。

2   当lob存放在表中的时候，它可以被缓存，对于它的操作效率远远高于存储在lobsegment中的lob（不用lobindex）

3 当lob存放logsegment中，缺省不在缓冲区缓存，对于lob的读写都是物理IO，代价非常高，所以对于大于4kb的lob字段千万不要频繁更新，效率非常低。

4 当lob存放logsegment中，可定义指定使用cache（默认是nocache），这对于中等大小的lob（比如几k～几十k）很有效果，减少物理IO

综上看来，尽量不用blob字段，假设用的话要分配好表空间和配置合适参数。