BLOB读取怎样优化？

Question

BLOB读取怎样优化？我想把0.0 - 0.5M的许多文件，存到BLOB中，一个文件对应表中一行
希望能快速的读取
请问除了正常建表，
还有什么可优化的地方，如块的大小、单个表的大小有什么需注意的吗？
请高手多指点

Answer 1

-- 弄懂下面这些，你就清楚啦：
-- 12.7.1 内部LOB ( P545 )create table t
( id int primary key,
  txt clob
)
/eygle@SZTYORA> select dbms_metadata.get_ddl('TABLE','T') from dual;DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------------  CREATE TABLE "EYGLE"."T"
   (    "ID" NUMBER(*,0),
        "TXT" CLOB,
         PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "EYGLE"  ENABLE
   ) 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)
  TABLESPACE "EYGLE"
 LOB ("TXT") STORE AS (
  TABLESPACE "EYGLE" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
  NOCACHE LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))-- LOB显然有以下属性：
-- *(1) 一个表空间(这个例子中即为USERS)
-- *(2) ENABLE STORAGE IN ROW作为一个默认属性
-- *(3) CHUNK 8192
-- *(4) PCTVERSION 10
-- *(5) NOCACHE
-- *(6) 一个完整的STORAGE子句-- 由此说明，在底层LOB并不那么简单，而事实上确实如此。LOB列总是会带来一种多段对象(multisegment object，这是我对它的叫法)，
-- 也就是说，这个表会使用多个物理段。如果我们在一个空模式中创建这个表，就会发现以下结果：
select segment_name, segment_type
from user_segments;SEGMENT_NAME                        SEGMENT_TYPE
----------------------------------- ------------------------------
SYS_LOB0000056912C00002$$           LOBSEGMENT
T                                   TABLE
SYS_C006746                         INDEX
SYS_IL0000056912C00002$$            LOBINDEX......------------------------------------------------------------------------------------------------------
-- 注意 LOB可能是内联的(inline)，或者存储在表中。在这种情况下，LOB数据会被缓存，但是这只适用于小于4000字节的LOB。
--      我们将在“IN ROW子句”一节中进一步讨论这种情况。
------------------------------------------------------------------------------------------------------......-- *2) IN ROW子句 ( P548 )
-- 前面的DBMS_METADATA返回的CREATE TABLE语句还包括以下内容：
LOB ("TXT") STORE AS (... ENABLE STORAGE IN RAW ...-- 这控制了LOB数据是否总与表分开存储(存储在lobsegment中)，或是有时可以与表一同存储，而不用单独放在lobsegment中。如果设置了ENABLE STORAGE IN ROW，
-- 而不是DISABLE STORAGE IN ROW，小LOB(最多4000字节)就会像VARCHAR2一样存储在表本身中。只有当LOB超过了4000字节时，才会“移出”到lobsegment中。create table t
( id int primary key,
  in_row clob,
  out_row clob
)
lob (in_row) store as (enable storage in row)
lob (out_row) store as (disable storage in row);-- 在这个表中，我们将插入一些串数据，所有这些串的长度都不超过4000字节：
insert into t
select rownum,
       owner || ' ' || object_name || ' ' || object_type || ' ' || status,
       owner || ' ' || object_name || ' ' || object_type || ' ' || status
from all_objects;commit;-- 现在，如果我们想读取每一行，在此使用了DBMS_MONITOR包，并启用了SQL_TRACE，执行这个工作时，可以看到这两个表获取数据时的性能：
grant execute on dbms_monitor to eygle;declare
  l_cnt number;
  l_data varchar2(32765);
begin
  select count(*) into l_cnt from t;  dbms_monitor.session_trace_enable;
  for i in 1 .. l_cnt
  loop
    select in_row into l_data from t where id = i;
    select out_row into l_data from t where id = i;
  end loop;
end;
/......-- 这种行内/行外存储设置不仅会影响读，还会影响修改。如果我们要用小串更新前100行，并用小串插入100个新行，再使用上述同样的技术查看性能，会观察到：
create sequence s start with 100000;declare
  l_cnt number;
  l_data varchar2(32765);
begin
  dbms_monitor.session_trace_enable;
  for i in 1 .. 100
  loop
    update t set in_row = to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') where id = i;
    update t set out_row = to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') where id = i;
    insert into t(id,in_row) values ( s.nextval, 'Hello World' );
    insert into t(id,out_row) values ( s.nextval, 'Hello World' );
  end loop;
end;
/......-- 注意读和写使用的I/O都有所增加。总之，由此显示出，如果使用一个CLOB，而且很多串都能在“行内”放下(也就是说，小于4000字节)，
-- 那么使用默认的ENABLE STORAGE IN ROW设置就是一个不错的想法。-- *3) CHUNK 子句 ( P553 )
-- 前面的DBMS_METADATA返回的CREATE TABLE语句包括以下内容：
LOB ("TXT") STORE AS ( ... CHUNK 8192 ... )-- LOB存储在块(chunk)中；指向LOB数据的索引会指向各个数据块。块(chunk)是逻辑上连续的一组数据块(block)，这也是LOB的最小分配 单元，
-- 而通常数据库的最小分配单元是数据库块。CHUNK大小必须是Oracle块大小的整数倍，只有这样才是合法值。-- 从两个角度看，选择CHUNK大小时必须当心。首先，每个LOB实例(每个行外存储的LOB值)会占用至少一个CHUNK。一个CHUNK由一个LOB值使用。如果一个表有100行，
-- 而每行有一个包含7KB数据的LOB，你就会分配100个CHUNK，如果将CHUNK大小设置为32KB，就会分配100个32KB的CHUNK。如果将CHUNK大小设置为8KB，则(可能)分配100个8KB的CHUNK。
-- 关键是，一个CHUNK只能由一个LOB使用(两个LOB不会使用同一个CHUNK)。如果选择了一个CHUNK大小，但不符合你期望的LOB大小，最后就会浪费大量的空间。例如，
-- 如果表中的LOB平均有7KB，而你使用的CHUNK大小为32KB，对于每个LOB实例你都会“浪费”大约25KB的空间。另一方面，倘若使用8KB的CHUNK，就能使浪费减至最少。-- *4.) PCTVERSION 子句
-- 前面的DBMS_METADATA返回的CREATE TABLE语句包括以下内容：
LOB ("TXT") STORE AS ( ... PCTVERSION 10 ... )-- 这是用于控制LOB的读一致性。在前面的几章节中，我们已经讨论了读一致性、多版本和undo在其中所起的作用。但LOB实现读一致性的方式有所不同。
-- lobsegment并不使用undo来记录其修改；而是直接在lobsegment本身中维护信息的版本。lobindex会像其他字段一样生成undo，但是lobsegment不会。相反，修改一个LOB时，
-- Oracle会分配一个新的CHUNK，并且仍保留原来的CHUNK。如果回滚了事务，对LOB索引所做的修改会回滚，索引将再次指向原来的CHUNK。因此，undo维护会在LOB段本身中执行。
-- 修改数据时，原来的数据保持不动，此外会创建新数据。
create table t
( id int primary key,
  txt clob
)
lob ( txt ) store as ( disable storage in row )
/insert into t values (1, 'hello world' );commit;declare
  l_clob clob;  cursor c is select id from t;
  l_id number;
begin
  select txt into l_clob from t;
  open c;
  update t set id = 2, txt = 'Goodbye';
  commit;
  dbms_output.put_line( dbms_lob.substr( l_clob, 100, 1 ) );
  fetch c into l_id;
  dbms_output.put_line( 'id = ' || l_id );
  close c;
end;
/select * from t;......-- PCTVERSION控制着用于实现LOB数据版本化的已分配LOB空间的百分比(这些数据库块由某个时间点的LOB所用，并处在lobsegment的HWM以下)。对于许多使用情况来说，
-- 默认设置10%就足够了，因为在很多情况下，你只是要INSERT和获取LOB(通常不会执行LOB的更新；LOB往往会插入一次，而获取多次)。因此，
-- 不必为LOB版本化预留太多的空间(甚至可以没有)。-- 不过，如果你的应用确实经常修改LOB，倘若你频繁地读LOB，与此同时另外某个会话正在修改这些LOB，10%可能就太小了。如果处理LOB时遇到一个ORA-22924错误，
-- 解决方案并不是增加undo保留时间(UNDO_RETENTION)，如果你在使用手动undo管理，那么增加更多RBS空间也不能解决这个问题。而是应该使用以下命令：
ALTER TABLE tabname MODIFY LOB (lobname) ( PCTVERSION n );-- 并增加lobsegment中为实现数据版本化所用的空间大小。-- *5) RETENTION子句 ( P556 )
-- 这个子句与PCTVERSION子句是互斥的，如果数据库中使用自动undo管理，就可以使用这个子句。RETENTION子句并非在lobsegment中保留某个百分比的空间来实现LOB的版本化，
-- 而是使用基于时间的机制来保留数据。数据库会设置参数UNDO_RETENTION，指定要把undo信息保留多长时间来保证一致读。在这种情况下，这个参数也适用于LOB数据。-- 需要注意，不能使用这个子句来指定保留时间；而是从数据库的UNDO_RETENTION设置来继承它。-- *6) CACHE子句 ( P556 )
-- 前面的DBMS_METADATA返回的CREATE TABLE语句包括以下内容：
LOB ("TXT") STORE AS ( ... NOCACHE ... )-- 除了NOCACHE，这个选项还可以是CACHE或CACHE READS。这个子句控制了lobsegment数据是否存储在缓冲区缓存中。默认的NOCACHE指示，
-- 每个访问都是从磁盘的一个直接读，类似地，每个写/修改都是对磁盘的一个直接写。CACHE READS允许缓存从磁盘读的LOB数据，但是LOB数据的写操作必须直接写至磁盘。
-- CACHE则允许读和写时都能缓存LOB数据。-- 在许多情况下，默认设置可能对我们并不合适。如果你只有小规模或中等规模的LOB(例如，使用LOB来存储只有几KB的描述性字段)，对其缓存就很有意义。如果不缓存，
-- 当用户更新描述字段时，还必须等待I/O将数据写至磁盘(将执行一个CHUNK大小的I/O，而且用户要等待这个I/O完成)。如果你在执行多个LOB的加载，
-- 那么加载每一行时都必须等待这个I/O完成。所以启用这些LOB的缓存很合理。你可以打开和关闭缓存，来看看会有什么影响：
ALTER TABLE tbname MODIFY LOB (lobname) ( CACHE );
ALTER TABLE tbname MODIFY LOB (lobname) ( CACHE READS);
ALTER TABLE tbname MODIFY LOB (lobname) ( NOCACHE );-- 对于一个规模很大的初始加载，启用LOB的缓存就很有意义，这允许DBWR在后台将LOB数据写至磁盘，而你的客户应用可以继续加载更多的数据。
-- 对于频繁访问或修改的小到中等规模的LOB，缓存就很合理，可以避免让最终用户实时等待物理I/O完成。不过，对于一个大小为50MB的LOB，把它放在缓存中就没有道理了。-- 要记住，此时可以充分使用Keep池或回收池。并非在默认缓存中将lobsegment数据与所有“常规”数据一同缓存，可以使用保持池或回收池将其分开缓存。
-- 采用这种方式，既能缓存LOB数据，而且不影响系统中现有数据的缓存。-- *7) LOB STORAGE 子句
-- 最后，前面的DBMS_METADATA返回的CREATE TABLE语句还包括以下内容：
LOB ("TXT") STORE AS ( ... STORAGE(INITIAL 65536 NEXT 1048576
MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1
FREELIST GROUPS 1 BUFFER_POOL DEFAULT ) ... )-- 也就是说，它有一个完整的存储子句，可以用来控制物理存储特征。需要指出，这个存储子句同样适用于lobsegment 和lobindex，对一个段的设置也可以用于另一个段。
-- 假设有一个本地管理的表空间，LOB的相关设置将是FREELISTS、FREELIST GROUPS和BUFFER_POOL，我们在第10章讨论过FREELISTS和FREELIST GROUPS与表段的关系。
-- 这些讨论同样适用于lobindex段，因为lobindex与其他索引段的管理是一样的。如果需要高度并发地修改LOB，可能最好在索引段上设置多个FREELISTS。-- 上一节已经提到，对LOB段使用保持池或回收池可能是一个很有用的技术，这样就能缓存LOB数据，而且不会“破坏”现有的默认缓冲区缓存。
-- 并不是将LOB与常规表一同放在块缓冲区中，可以在SGA中专门为这些LOB对象预留一段专用的内存。BUFFER_POOL子句可以达到这个目的。

Answer 2

BLOB类型 默认就是类似于外部表结构，本身就能提供很快的读取。

Answer 3

SQL code
-- 弄懂下面这些，你就清楚啦：
-- 12.7.1 内部LOB ( P545 )create table t
( id int primary key,
txt clob
)
/eygle@SZTYORA> select dbms_metadata.get_ddl('TABLE','T') from dual;DBMS_METADATA.GET_DDL('T……

Answer 4

感谢帮助，感谢luoyoumou解答
如果有具体的优化方法就更好了，谢谢