Oracle 的LOB类型索引的转移，或是重建问题

Oracle 的LOB类型索引的转移，或是重建问题

原文地址：http://space.itpub.net/7728585/viewspace-683081

当我们想重建LOB类型的索引的时候，就会出现报错，重现如下：
create table test
 ( id int, txt clob );

SQL> select * from user_segments;
 
SEGMENT_NAME                                                                     PARTITION_NAME                 SEGMENT_TYPE       TABLESPACE_NAME          

         BYTES     BLOCKS    EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE  FREELISTS FREELIST_GROUPS BUFFER_POOL
-------------------------------------------------------------------------------- ------------------------------ ------------------ -------------------------

----- ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------------- -----------
TEST                                                                                                            TABLE              USERS                    

      41943040       5120         55          65536                       1  2147483645                                         DEFAULT
SYS_IL0000049476C00002$$                                                                                        LOBINDEX           USERS                    

         65536          8          1          65536                       1  2147483645                                         DEFAULT
SYS_LOB0000049476C00002$$                                                                                       LOBSEGMENT         USERS                    

         65536          8          1          65536                       1  2147483645                                         DEFAULT

alter index SYS_IL0000049476C00002$$ rebuild tablespace users ONLINE NOLOGGING
 
ORA-02327: 无法以数据类型 LOB 的表达式创建索引



我们先回忆一下相关知识，以下来自ORACLE 9I&10G编程艺术
  lobindex和lobsegment，它们做什么用？创建这些段是为了支持我们的LOB列。我们的实际LOB数据就存储在lobsegment中（确实，LOB数据也有可能存储在表T中，不过稍后讨论

ENABLE STORAGE IN ROW子句时还会更详细地说明这个内容）。lobindex用于执行LOB的导航，来找出其中的某些部分。创建一个LOB列时，一般来说，存储在行中的这是一个指针（

pointer），或LOB定位器（LOB locator）。我们的应用所获取的就是这个LOB定位器。当请求得到LOB的“12.000～2,000字节”时，将对lobindex使用LOB定位器来找出这些字节存

储在哪里，然后再访问lobsegment。可以用lobindex很容易地找到LOB的各个部分。由此说来，可以把LOB想成是一种主/明细关系。
  表中的LOB实际上只是指向lobindex，lobindex再指向LOB本身的各个部分。为了得到LOB中的N~M字节，要对表中的指针（LOB定位器）解除引用，遍历lobindex结构来找到所需的

数据库（chunk），然后按顺序访问。这使得随机访问LOB的任何部分都能同样迅速，你可以用同样快的速度得到LOB的最前面、中间或最后面的部分，因为无需再从头开始遍历LOB

。
  ORACLE也说：The LOB index is an internal structure that is strongly associated with the LOB storage. 也就是说不让直接重建
  但是我觉得可以明确INDEX是用来确定LOBSEGMENT的位置，如果经常对表中的行进行DML或者对大字段进行DML，我觉得重建还是用必要的。
既然是索引就要遵守索引的原则。大量的DML必然使索引的页节点越来越多，深度越来越大，但是其中包含了空闲空间。
  ORACLE METALINK 说 Use the ALTER TABLE ... MOVE command which will rebuild  the indexes ，也就是使用ALTER TABLE MOVE语句来进行REBUILD索引。
  下面就测试一下：
   首先理解几个概念，才能了解实验结果：
1、IN ROW 默认的这个子句是（ENABLE STORAGE IN ROW），也就是小于4000字节就存储在表段中，如果大于4000字节就存储在lob段中，同时使用LOBINDEX来指定位置，我这里使

用DISABLE STORAGE IN ROW，也就是不管多大都存在LOBSEGMENT中。
2、CHUNK 表示最小LOGSEGMENT最小的存储单元，而且一个CHUNK只限于一个SEGMENT行使用，如果一个CHUNK设置为32K，你的SEGMENT行只有2K那就要浪费30k。
3、CACHE 表示是否把读取写入LOGSEGMENT记录到缓存，默认是NOCACHE，可以是CACHE和CACHE READS，前者读写都保存，后者读保存，写是直接写。而NOCACHE，就是直接读写。

下面是我建立表的语句。
  CREATE TABLE "PPTEST"."TEST2"
   ( "ID" NUMBER(*,0),
 "TXT" CLOB
   ) 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 "USERS"
 LOB ("TXT") STORE AS (
  TABLESPACE "USERS" DISABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
  NOCACHE
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))
我这里DISABLE STORAGE IN ROW CHUNK 8192，CHUNK是8K。
现在我插入数据
SQL> insert into test2
  2  select * from test;
 
589824 rows inserted
 这里有58W多行，计算一下SEGMENTS占用空间。589824*8K=4.5G
查看一下：
select SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024/1024 gb,BLOCKS from user_segments;
SYS_IL0000049480C00002$$                                                         LOBINDEX           0.03027343       3968
SYS_LOB0000049480C00002$$                                                        LOBSEGMENT         4.54199218     595328
TEST2                                                                            TABLE              0.01855468       2432
确实我们的LOGSEGMENT是4.5G。
现在我们收集统计信息，并且对索引进行分析如下：
SQL> execute dbms_stats.gather_schema_stats(ownname => 'PPTEST',cascade => true);
 
PL/SQL procedure successfully completed
analyze index SYS_IL0000049480C00002$$ validate structure;
analyze index SYS_IL0000049480C00002$$ compute statistics;
过后查看都没有发现LOGINDEX的结构信息，不知道为何。
但是考虑进行了大量的DML够后进行REBULIDING索引的大小肯定会减少。
现在我们来进行REBULDING实验。
首先模拟大量删除插入，
delete  test2;
insert into test2
select id,txt from (select rownum rn, id, txt from test) where mod(rn, 8) = 0;
先全部删除，然后再插入1/8的数据。（过程巨慢）
SQL> delete  test2;
 
589824 rows deleted
查看
select SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024/1024 gb,BLOCKS from user_segments;
SYS_IL0000049480C00002$$                                                         LOBINDEX             0.078125      10240
SYS_LOB0000049480C00002$$                                                        LOBSEGMENT         4.60449218     603520
TEST2                                                                            TABLE              0.01855468       2432
索引的块大量增加，占用空间也大量增加，这里我也不太明白为何大量增加。这时其实没有任何数据了。
然后进行了插入。
SQL> insert into test2
  2  select id,txt from (select rownum rn, id, txt from test) where mod(rn, 8) = 0;
 
73728 rows inserted
 查看
select SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024/1024 gb,BLOCKS from user_segments;
SYS_IL0000049480C00002$$                                                         LOBINDEX            0.0859375      11264
SYS_LOB0000049480C00002$$                                                        LOBSEGMENT         5.10449218     669056
TEST2                                                                            TABLE              0.01855468       2432
 
其实这个时候数据只是以前的1/8 但是LOGSEGMENT和LOBINDEX 却更大。所以有大量的浪费空间。
我们直接重建TEST2表
SQL> alter table test2 move tablespace users;
查看
SYS_IL0000049480C00002$$                                                         LOBINDEX            0.0859375      11264
SYS_LOB0000049480C00002$$                                                        LOBSEGMENT         5.10449218     669056
TEST2                                                                            TABLE              0.00292968        384
只是重建了TEST2段

使用语句
  ALTER TABLE test2 MOVE
  TABLESPACE users
  LOB (TXT) STORE AS lobsegment
  (TABLESPACE users  );
进行重建
重建期间可以看到临时对象如下：
4.635                                                                            TEMPORARY          0.00061035         80
4.187                                                                            TEMPORARY           0.1328125      17408
4.611                                                                            TEMPORARY          0.00097656        128
显然表本身，LOBSEGMENT和LOBINDEX都再重建
然后查看
SQL> select SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024/1024 gb,BLOCKS from user_segments;
 
SEGMENT_NAME                                                                     SEGMENT_TYPE               GB     BLOCKS
-------------------------------------------------------------------------------- ------------------ ---------- ----------
TEST2                                                                            TABLE              0.00292968        384
LOBSEGMENT                                                                       LOBSEGMENT         0.56835937      74496
SYS_IL0000049480C00002$$                                                         LOBINDEX           0.00390625        512
因为我这里使用 lobsegment所以以前的SYS_LOB0000049480C00002$$ 变为了LOBSEGMENT，可以看到这个时候容量正常了。0.56G刚好是以前的1/8.

如果想单独重建会报错。METALINK上记录如下：
Explanation
-----------

The 'ALTER TABLE foo MODIFY LOB (lobcol) ...' syntax does not allow
for a change of tablespace

  ALTER TABLE  my_lob
   MODIFY LOB (a_lob)
   (TABLESPACE new_tbsp);

  (TABLESPACE new_tbsp)
   *
  ORA-22853: invalid LOB storage option specification

You have to use the MOVE keyword instead as shown in the examples.

 

结论：
1、LOGSEGMENT不会重用HWM以下的空间，所以大量DML会不断增加它的大小。
2、进行LOBsegment和LOBINDEX重建很有必要，使用语法如下：
  ALTER TABLE test2 MOVE
  TABLESPACE users
  LOB (TXT) STORE AS lobsegment
  (TABLESPACE users  );
必须和表一起重建，单独重建LOBSEGMENT或者LOGINDEX没有办法。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21907916/viewspace-713787/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21907916/viewspace-713787/