Lob字段数据删除，对应空间变化测试

由于遇到某个系统需要新添加LOB字段，并且会有大量的插入删除操作，所以需要确认下lob字段在

大量数据操作之后总的体积大小变化是怎样的。猜想lob字段的大小并不会自动收缩，是持续增长的，需要手动干预收缩空间

--测试1 测试disable storage in row下的lob字段
--create table创建测试表
create table T_LOB_TEST
(
  id         number(10) not null,
  xml_content BLOB,
  comm       VARCHAR2(5)
)
lob(xml_content) store as
(disable storage in row
nocache logging)
;

--插入数据
declare 
i number(10);
begin
 for i in 1..1000
 loop
  insert into T_LOB_TEST values(i,to_blob('11111000011111000100001110000101010101000001000000011111100000000000000000111111111111100000000111111'),'comm');
 end loop;
 commit;
end;
/

--查询占用空间
select segment_name,bytes/1024 from user_segments where segment_name='T_LOB_TEST'
or segment_name in (select segment_name from user_lobs where table_name='T_LOB_TEST')
or segment_name in (select index_name from user_lobs where table_name='T_LOB_TEST');


SEGMENT_NAME              BYTES/1024
T_LOB_TEST                64
SYS_IL0000346640C00002$$  128
SYS_LOB0000346640C00002$$  9216



--删除部分数据
SQL> delete from T_LOB_TEST where id>200;
 
800 rows deleted
 
SQL> commit;
 
Commit complete
 
SQL> 


--再次查询占用空间，并未变化
select segment_name,bytes/1024 from user_segments where segment_name='T_LOB_TEST'
or segment_name in (select segment_name from user_lobs where table_name='T_LOB_TEST')
or segment_name in (select index_name from user_lobs where table_name='T_LOB_TEST');

SEGMENT_NAME              BYTES/1024
T_LOB_TEST                64
SYS_IL0000346640C00002$$  128
SYS_LOB0000346640C00002$$  9216



--再次插入数据
declare 
i number(10);
begin
 for i in 201..1000
 loop
  insert into T_LOB_TEST values(i,to_blob('11111000011111000100001110000101010101000001000000011111100000000000000000111111111111100000000111111'),'comm');
 end loop;
 commit;
end;
/

--再查询占用空间，发现占用空间变大了
select segment_name,bytes/1024 from user_segments where segment_name='T_LOB_TEST'
or segment_name in (select segment_name from user_lobs where table_name='T_LOB_TEST')
or segment_name in (select index_name from user_lobs where table_name='T_LOB_TEST');


SEGMENT_NAME BYTES/1024
T_LOB_TEST 64
SYS_IL0000346640C00002$$ 192
SYS_LOB0000346640C00002$$ 15360


--结论1：
--lob字段空间不会重复使用。重复删除插入，lob字段持续增长


--处理方法：需要压缩空间
ALTER TABLE r_zhangry.T_LOB_TEST  MODIFY LOB (xml_content) (SHRINK SPACE);

--查询占用空间，变成初始状态了
select segment_name,bytes/1024 from user_segments where segment_name='T_LOB_TEST'
or segment_name in (select segment_name from user_lobs where table_name='T_LOB_TEST')
or segment_name in (select index_name from user_lobs where table_name='T_LOB_TEST');

SEGMENT_NAME BYTES/1024
T_LOB_TEST 64
SYS_IL0000346640C00002$$ 320
SYS_LOB0000346640C00002$$ 960





--测试2 测试非disable storage in row模式下
--该模式为默认模式，既小于4k的数据不会存在lob中，只有大于4k的数据才会存在lob字段中

--创建测试表
create table T_LOB_TEST
(
  id         number(10) not null,
  xml_content BLOB,
  comm       VARCHAR2(5)
)
;


--插入数据
declare 
i number(10);
begin
 for i in 1..1000
 loop
  if  mod(i,3) != 0 then
  insert into T_LOB_TEST values(i,to_blob('11111000011111000100001110000'),'comm');
  else
  insert into T_LOB_TEST (select i,b.payload,'comm' from mid_opr.JBM_MSG b where rownum<2);
  end if;
  commit;
 end loop;
 commit;
end;
/


--查询占用空间
select segment_name,bytes/1024 from user_segments where segment_name='T_LOB_TEST'
or segment_name in (select segment_name from user_lobs where table_name='T_LOB_TEST')
or segment_name in (select index_name from user_lobs where table_name='T_LOB_TEST');


SEGMENT_NAME BYTES/1024
T_LOB_TEST 128
SYS_IL0000346643C00002$$ 64
SYS_LOB0000346643C00002$$ 16384



--删除部分数据
SQL> delete from T_LOB_TEST where id>200;
 
800 rows deleted
 
SQL> commit;
 
Commit complete
 
SQL> 



--再次查询占用空间，并无变化
select segment_name,bytes/1024 from user_segments where segment_name='T_LOB_TEST'
or segment_name in (select segment_name from user_lobs where table_name='T_LOB_TEST')
or segment_name in (select index_name from user_lobs where table_name='T_LOB_TEST');


SEGMENT_NAME BYTES/1024
T_LOB_TEST 128
SYS_IL0000346643C00002$$ 64
SYS_LOB0000346643C00002$$ 16384



--再次插入数据
declare 
i number(10);
begin
 for i in 201..1000
 loop
  if  mod(i,3) != 0 then
  insert into T_LOB_TEST values(i,to_blob('11111000011111000100001110000'),'comm');
  else
  insert into T_LOB_TEST (select i,b.payload,'comm' from mid_opr.JBM_MSG b where rownum<2);
  end if;
  commit;
 end loop;
 commit;
end;
/

--再查询占用空间，占用空间变大了
select segment_name,bytes/1024 from user_segments where segment_name='T_LOB_TEST'
or segment_name in (select segment_name from user_lobs where table_name='T_LOB_TEST')
or segment_name in (select index_name from user_lobs where table_name='T_LOB_TEST');


SEGMENT_NAME BYTES/1024
T_LOB_TEST 128
SYS_IL0000346643C00002$$ 64
SYS_LOB0000346643C00002$$ 29696



结论2：
--lob字段空间不会重复使用。重复删除插入，lob字段持续增长



处理方法：
--需要压缩空间
ALTER TABLE r_zhangry.T_LOB_TEST  MODIFY LOB (xml_content) (SHRINK SPACE);

--再次查询空间，结果变为初始状态
select segment_name,bytes/1024 from user_segments where segment_name='T_LOB_TEST'
or segment_name in (select segment_name from user_lobs where table_name='T_LOB_TEST')
or segment_name in (select index_name from user_lobs where table_name='T_LOB_TEST');


SEGMENT_NAME BYTES/1024
T_LOB_TEST 128
SYS_IL0000346643C00002$$ 64
SYS_LOB0000346643C00002$$ 16384