Lob字段数据删除，对应空间变化测试
发布时间：2020-12-12 14:50:06 所属栏目：百科 来源：网络整理
导读：由于遇到某个系统需要新添加LOB字段，并且会有大量的插入删除操作，所以需要确认下lob字段在 大量数据操作之后总的体积大小变化是怎样的。猜想lob字段的大小并不会自动收缩，是持续增长的，需要手动干预收缩空间 --测试1 测试disable storage in row下的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('22222000022222000100001110000101010101000001000000022222100000000000000000222222222211100000000222221'),'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('22222000022222000100001110000'),'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（编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |