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

前端之家收集整理的这篇文章主要介绍了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,'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,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,'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

猜你在找的Oracle相关文章