创建压缩表
create table t_compress as select * from dba_objects;
创建非压缩表
create table t_uncompress as select * from dba_objects;
占用空间对比
select segment_name,extent_id,bytes from user_extents where segment_name in ('T_COMPRESS')
select segment_name,extent_id,bytes from user_extents where segment_name in ('T_UNCOMPRESS')
可见未压缩的表extent数目为21 压缩过的表为17 占用空间前者是后者的3倍。
索引的压缩:先建表
创建索引
create table t1 as select * from dba_objects;
create table t2 as select * from dba_objects;
create index ind_t1_compress on t1(object_name) compress;
create index ind_t2_uncompress on t2(object_name);
查询占用空间情况
select segment_name,extent_id,bytes from user_extents where segment_name in ('IND_T1_COMPRESS');
select segment_name,extent_id,bytes from user_extents where segment_name in ('IND_T2_UNCOMPRESS');
可能是测试的数据不够多 这里面发现两个占得空间几乎一样,但是查询叶子节点的数目
发现压缩过后叶子节点减少了五分之一
select index_name,compression,leaf_blocks from user_indexes where index_name in('IND_T1_COMPRESS','IND_T2_UNCOMPRESS')
列重复率高的情况下:先建表
create table t1 as select object_id,object_name from dba_objects;
create table t2 as select 1 object_id,object_name from dba_objects;
create index ind_t2_compress on t2(object_id) compress;
创建压缩的索引,对比
select segment_name,extent_id,bytes from user_extents where segment_name in ('IND_T1_COMPRESS');
select segment_name,extent_id,bytes from user_extents where segment_name in ('IND_T2_COMPRESS');
可见重复率高的t2表的索引比t1占用的空间少了6个extents