如何重建LOB类型的索引和LOB段
发布网友
发布时间：2022-05-02 17:46
共2个回答
懂视网
时间：2022-05-05 13:38
本文简要介绍MAX-Length数据及添加数据到LOB列的存储，另外主要介绍filestream 数据的基
本文简要介绍MAX-Length数据及添加数据到LOB列的存储，另外主要介绍filestream 数据的基础。
SQL2005/2008给我们MAX定义符选项来定义可变长度字段。通过MAX定义，你可以让SQL Server来决定存储值作为常规的varchar,nvarchar或varbinary或LOB数据(通常在大于8000字节时会使用text,ntext,image)。
在存储引擎中，每一个LOB列被分解成不大于8040字节的碎片。当附加数据到LOB列时，SQL Server查找附加点，并试图寻找新数据将被增加的位置，新老数据如果合起来大于8040字节，那么分配一个大对象页(Large Object Page)直到余下的碎片小于8040字节，再寻找一个页存放其余的字节。当SQL Server为LOB数据分配页时，它有两个分配策略：(1)如果数据小于64Kb,随机分配一个页。这个页来自一个大对象IAM的部分的一个范围(extent)，但页不一定是连续的。(2)如果大于64KB,它使用一个附加页分配器，一次性的分配一个范围，在这个范围中连续地写数据。因而，推荐的做法是：如果需要插入64KB数据，那么一次插入8*8040时，数据将是继续存放的，而且是在一个范围(extent)内。
如果你发现一个Large Object 数据变得越来越碎片，可以使用ALter Index reorganize选项来整理它。前提是默认打开为ON的WithLOB_compaction选项你没有关闭它。
文件流数据(FileStream Data)
尽管SQL Server提供了很大的便利，使得你可以在中灵活存储Large object Data。好处：
1、大对象数据的事务一致性可以保证。
2、包含大对象数据的备份与恢复，允许你完整地在某个时刻点恢复大对象数据。
3、所有的数据使用一个单一的存储和查询环境。
但是也有一些不利因素：
1、大对象数据在缓存中占用大量的缓冲器。
2、更新一个大对象会引起大范围的数据整理。
3、数据库文件会变得非常大。
SQL Server 2008 和 SQL Server Native Client 10.0 支持增强的 FILESTREAM 功能。FILESTREAM 功能允许通过 SQL Server 或通过直接访问 Windows 文件系统来存储和访问大型二进制值。大型二进制值是大于 2 GB 的值。有关增强的 FILESTREAM 支持的详细信息，请参阅FILESTREAM 概述。文件流来作为一种大数据存储方案，其优势：
1、大对象数据存储在文件系统中却在数据库中有一个48字节的文件指针值存储在包含文件流的列中。
2、大对象数据可以通过T-SQL和NTFS 流APIs来访问。
3、大对象数据的大小仅受限于NTFS值大小，而不是老的2GB大小限制(LOB数据)
劣势：
1、数据库镜像不能被用于包含文件流数据的数据库。
2、数据库快照不能包含文件流文件组，因而，文件流数据不可用。在快照中使用slect请求一个文件流列会出错。
3、文件流数据不能被SQL Server本地加密。
热心网友
时间：2022-05-05 10:46
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 procere 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没有办法。
