oracle 海量数据之利器“数据压缩”实际应用及优缺点汇总
引言：“数据压缩”以前对我来说还是比较新鲜的词，并不是没有听说过，而是没有实际使用过，之前一直做项目经理工作上也设计到数据库的运维，但由于存储设计的比较充裕，在加上性能运转的还能让客户接受，所以压缩技术基本上没怎么用，当时也怕对DML操作有负面影响！之所以现在要实验这方面的技术，也是因为二期我们的数据量暴增，对机器对性能对运维都用一定的压力和冲击，这也说明了这门技术是在特定环境、特定场合下来使用的，下面我们来详细讲一讲如何去使用这门技术---海量数据之利器“压缩compress”
一、应用场合
答：正如上面所说，压缩技术一般应用在“海量数据”的范畴，在大数据量下并且重复率高的情境下应该的效果才好，适合于OLAP（报表系统）不适合OLTP（在线交易系统），当数据量小时作用还不是很大，只要数据量达到一定程度的时候使用这门技术才有意义。
二、压缩技术的好处
1.节约空间：我认为压缩技术的最大好处就是节约空间，在存储设备空间很紧张的情况下更需要使用压缩技术来缓解
2.减少memory/IO开销：当使用了压缩技术后，同等记录对应的数据块相对较少，自然而然IO开销就小了
3.提高数据处理效率：原本10个数据块的，经过压缩后变成5个数据块，但还是那些数据，可效率提高了一倍。
三、压缩原理
答：我们都用过rar压缩zip压缩，其实这些压缩的原理都很相似，当然在细微处理上有不同，数据块压缩原理上是使用长度较短符号来代替列中的每个值，由于符号占用空间小于实际值，从而达到节约空间的效果，当列中重复值越多压缩效果越好。
四、数据压缩不适合OLTP系统
1.OLTP是一种多事务短时间片系统，有大量的DML操作，而DML操作我们都知道是有锁lock的（insert update delete）当数据块中记录数多起来时，导致访问这个数据块的用户就多，就是产生争用和等待，从而降低了效率。
2.数据插入、更新和索引键值更新时，都要先解压缩再更新，等待的时间会更长。
五、分区表的压缩
1.分区表的每个分区都可以独立压缩
2.有些时候，分区表在创建时不设置分区压缩属性，当某个分区需要压缩时，再独立进行压缩
3.如果在表级设置压缩属性的话，那么所有分区都会启动压缩功能，反正如果是某几个分区独立压缩，表级属性上什么也不写的。
实验
create table leo_partition1 (object_id,object_name) 创建一个六个分区的分区表，没有指定压缩
partition by range (object_id)
(
partition leo_p1 values less than (1000),
partition leo_p2 values less than (2000),
partition leo_p3 values less than (3000),
partition leo_p4 values less than (4000),
partition leo_p5 values less than (5000),
partition leo_max values less than (maxvalue)
)
as select object_id,object_name from dba_objects;
LS@LEO> select count(*) from leo_partition1;
答：正如上面所说，压缩技术一般应用在“海量数据”的范畴，在大数据量下并且重复率高的情境下应该的效果才好，适合于OLAP（报表系统）不适合OLTP（在线交易系统），当数据量小时作用还不是很大，只要数据量达到一定程度的时候使用这门技术才有意义。
二、压缩技术的好处
1.节约空间：我认为压缩技术的最大好处就是节约空间，在存储设备空间很紧张的情况下更需要使用压缩技术来缓解
2.减少memory/IO开销：当使用了压缩技术后，同等记录对应的数据块相对较少，自然而然IO开销就小了
3.提高数据处理效率：原本10个数据块的，经过压缩后变成5个数据块，但还是那些数据，可效率提高了一倍。
三、压缩原理
答：我们都用过rar压缩zip压缩，其实这些压缩的原理都很相似，当然在细微处理上有不同，数据块压缩原理上是使用长度较短符号来代替列中的每个值，由于符号占用空间小于实际值，从而达到节约空间的效果，当列中重复值越多压缩效果越好。
四、数据压缩不适合OLTP系统
1.OLTP是一种多事务短时间片系统，有大量的DML操作，而DML操作我们都知道是有锁lock的（insert update delete）当数据块中记录数多起来时，导致访问这个数据块的用户就多，就是产生争用和等待，从而降低了效率。
2.数据插入、更新和索引键值更新时，都要先解压缩再更新，等待的时间会更长。
五、分区表的压缩
1.分区表的每个分区都可以独立压缩
2.有些时候，分区表在创建时不设置分区压缩属性，当某个分区需要压缩时，再独立进行压缩
3.如果在表级设置压缩属性的话，那么所有分区都会启动压缩功能，反正如果是某几个分区独立压缩，表级属性上什么也不写的。
实验
create table leo_partition1 (object_id,object_name) 创建一个六个分区的分区表，没有指定压缩
partition by range (object_id)
(
partition leo_p1 values less than (1000),
partition leo_p2 values less than (2000),
partition leo_p3 values less than (3000),
partition leo_p4 values less than (4000),
partition leo_p5 values less than (5000),
partition leo_max values less than (maxvalue)
)
as select object_id,object_name from dba_objects;
LS@LEO> select count(*) from leo_partition1;
COUNT(*)
----------
10351
----------
10351
LS@LEO> select partition_name,compression from user_tab_partitions where table_name='LEO_PARTITION1';
PARTITION_NAME COMPRESS
------------------------------ --------
LEO_MAX DISABLED
LEO_P1 DISABLED
LEO_P2 DISABLED
LEO_P3 DISABLED
LEO_P4 DISABLED
LEO_P5 DISABLED
------------------------------ --------
LEO_MAX DISABLED
LEO_P1 DISABLED
LEO_P2 DISABLED
LEO_P3 DISABLED
LEO_P4 DISABLED
LEO_P5 DISABLED
LS@LEO> alter table leo_partition1 compress; 例如 定义表级压缩属性，此时所有分区都压缩
LS@LEO> alter table leo_partition1 modify partition leo_p1 compress; 我们测试准对leo_p1 和 leo_p5分区进行压缩，来说明分区可以独立压缩
Table altered.
LS@LEO> alter table leo_partition1 modify partition leo_p5 compress;
Table altered.
LS@LEO> select partition_name,compression from user_tab_partitions where table_name='LEO_PARTITION1';
PARTITION_NAME COMPRESS
------------------------------ -----------------------
LEO_MAX DISABLED
LEO_P1 ENABLED
LEO_P2 DISABLED
LEO_P3 DISABLED
LEO_P4 DISABLED
LEO_P5 ENABLED
------------------------------ -----------------------
LEO_MAX DISABLED
LEO_P1 ENABLED
LEO_P2 DISABLED
LEO_P3 DISABLED
LEO_P4 DISABLED
LEO_P5 ENABLED
6 rows selected.
LS@LEO> alter table leo_partition1 modify partition leo_p5 nocompress; 我们还可以取消压缩
Table altered.
LS@LEO> select partition_name,compression from user_tab_partitions where table_name='LEO_PARTITION1'; leo_p5分区已经取消压缩
PARTITION_NAME COMPRESS
------------------------------ -----------------------
LEO_MAX DISABLED
LEO_P1 ENABLED
LEO_P2 DISABLED
LEO_P3 DISABLED
LEO_P4 DISABLED
LEO_P5 DISABLED
------------------------------ -----------------------
LEO_MAX DISABLED
LEO_P1 ENABLED
LEO_P2 DISABLED
LEO_P3 DISABLED
LEO_P4 DISABLED
LEO_P5 DISABLED
六、分区索引的压缩
1.分区表索引中不能对单个独立分区索引设置压缩，要么整体压缩要么不压缩。
2.如果创建分区表索引时未设置压缩，以后都不能在设置了
LS@LEO> create index leo_partition1_index on leo_partition1(object_id) local; 创建时没有设置压缩
1.分区表索引中不能对单个独立分区索引设置压缩，要么整体压缩要么不压缩。
2.如果创建分区表索引时未设置压缩，以后都不能在设置了
LS@LEO> create index leo_partition1_index on leo_partition1(object_id) local; 创建时没有设置压缩
Index created.
LS@LEO> select index_name,compression from user_indexes where table_name='LEO_PARTITION1'; 分区表索引没有压缩
INDEX_NAME COMPRESS
------------------------------ --------
LEO_PARTITION1_INDEX DISABLED
------------------------------ --------
LEO_PARTITION1_INDEX DISABLED
LS@LEO> select partition_name,compression from user_ind_partitions where index_name='LEO_PARTITION1_INDEX'; 独立分区索引也没有压缩
PARTITION_NAME COMPRESS
------------------------------ --------
LEO_MAX DISABLED
LEO_P1 DISABLED
LEO_P2 DISABLED
LEO_P3 DISABLED
LEO_P4 DISABLED
LEO_P5 DISABLED
------------------------------ --------
LEO_MAX DISABLED
LEO_P1 DISABLED
LEO_P2 DISABLED
LEO_P3 DISABLED
LEO_P4 DISABLED
LEO_P5 DISABLED
LS@LEO> alter index leo_partition1_index rebuild partition leo_p5 compress;
alter index leo_partition1_index rebuild partition leo_p5 compress
*
ERROR at line 1:
ORA-28659: COMPRESS must be specified at object level first 报错，压缩必须首先在对象级别指定
alter index leo_partition1_index rebuild partition leo_p5 compress
*
ERROR at line 1:
ORA-28659: COMPRESS must be specified at object level first 报错，压缩必须首先在对象级别指定
3.必须创建分区表索引时一次性指定好压缩属性，以后才能对独立分区索引压缩进行修改
LS@LEO> create index leo_partition1_index on leo_partition1(object_id) local compress; 创建时指定
Index created.
LS@LEO> select index_name,compression from user_indexes where table_name='LEO_PARTITION1'; 分区表索引已压缩
LS@LEO> create index leo_partition1_index on leo_partition1(object_id) local compress; 创建时指定
Index created.
LS@LEO> select index_name,compression from user_indexes where table_name='LEO_PARTITION1'; 分区表索引已压缩
INDEX_NAME COMPRESS
------------------------------ --------
LEO_PARTITION1_INDEX ENABLED
------------------------------ --------
LEO_PARTITION1_INDEX ENABLED
LS@LEO> select partition_name,index_name,compression from user_ind_partitions where index_name='LEO_PARTITION1_INDEX';独立分区索引也压
缩
PARTITION_NAME INDEX_NAME COMPRESS
------------------------------ ------------------------------ --------
LEO_MAX LEO_PARTITION1_INDEX ENABLED
LEO_P1 LEO_PARTITION1_INDEX ENABLED
LEO_P2 LEO_PARTITION1_INDEX ENABLED
LEO_P3 LEO_PARTITION1_INDEX ENABLED
LEO_P4 LEO_PARTITION1_INDEX ENABLED
LEO_P5 LEO_PARTITION1_INDEX ENABLED
6 rows selected.
------------------------------ ------------------------------ --------
LEO_MAX LEO_PARTITION1_INDEX ENABLED
LEO_P1 LEO_PARTITION1_INDEX ENABLED
LEO_P2 LEO_PARTITION1_INDEX ENABLED
LEO_P3 LEO_PARTITION1_INDEX ENABLED
LEO_P4 LEO_PARTITION1_INDEX ENABLED
LEO_P5 LEO_PARTITION1_INDEX ENABLED
6 rows selected.
LS@LEO> alter index leo_partition1_index rebuild partition leo_p3 nocompress; 独立分区索引取消压缩
Index altered.
LS@LEO> alter index leo_partition1_index rebuild partition leo_p4 nocompress;
Index altered.
LS@LEO> alter index leo_partition1_index rebuild partition leo_p5 nocompress;
Index altered.
LS@LEO> select partition_name,index_name,compression from user_ind_partitions where index_name='LEO_PARTITION1_INDEX';
Index altered.
LS@LEO> alter index leo_partition1_index rebuild partition leo_p4 nocompress;
Index altered.
LS@LEO> alter index leo_partition1_index rebuild partition leo_p5 nocompress;
Index altered.
LS@LEO> select partition_name,index_name,compression from user_ind_partitions where index_name='LEO_PARTITION1_INDEX';
PARTITION_NAME INDEX_NAME COMPRESS
------------------------------ ------------------------------ --------
LEO_MAX LEO_PARTITION1_INDEX ENABLED
LEO_P1 LEO_PARTITION1_INDEX ENABLED
LEO_P2 LEO_PARTITION1_INDEX ENABLED
LEO_P3 LEO_PARTITION1_INDEX DISABLED
LEO_P4 LEO_PARTITION1_INDEX DISABLED
LEO_P5 LEO_PARTITION1_INDEX DISABLED
------------------------------ ------------------------------ --------
LEO_MAX LEO_PARTITION1_INDEX ENABLED
LEO_P1 LEO_PARTITION1_INDEX ENABLED
LEO_P2 LEO_PARTITION1_INDEX ENABLED
LEO_P3 LEO_PARTITION1_INDEX DISABLED
LEO_P4 LEO_PARTITION1_INDEX DISABLED
LEO_P5 LEO_PARTITION1_INDEX DISABLED
七、数据压缩对空间影响
1.节约空间：数据压缩会节约大量存储空间，我们创建一个压缩表和一个普通表，来对比节约空间的效果
LS@LEO> create table leo_compress_table compress as select * from dba_objects; 压缩表
1.节约空间：数据压缩会节约大量存储空间，我们创建一个压缩表和一个普通表，来对比节约空间的效果
LS@LEO> create table leo_compress_table compress as select * from dba_objects; 压缩表
Table created.
LS@LEO> create table leo_nocompress_table as select * from dba_objects; 普通表
Table created.
LS@LEO> select segment_name,extent_id,bytes from user_extents where segment_name in ('LEO_COMPRESS_TABLE','LEO_NOCOMPRESS_TABLE');
SEGMENT_NAME EXTENT_ID BYTES
---------------------- ---------- ----------
LEO_COMPRESS_TABLE 0 65536
LEO_COMPRESS_TABLE 1 65536
LEO_COMPRESS_TABLE 2 65536
LEO_COMPRESS_TABLE 3 65536
LEO_COMPRESS_TABLE 4 65536
LEO_COMPRESS_TABLE 5 65536
LEO_COMPRESS_TABLE 6 65536 同样数据量，压缩表占用7个区，使用0.43M空间
---------------------- ---------- ----------
LEO_COMPRESS_TABLE 0 65536
LEO_COMPRESS_TABLE 1 65536
LEO_COMPRESS_TABLE 2 65536
LEO_COMPRESS_TABLE 3 65536
LEO_COMPRESS_TABLE 4 65536
LEO_COMPRESS_TABLE 5 65536
LEO_COMPRESS_TABLE 6 65536 同样数据量，压缩表占用7个区，使用0.43M空间
LEO_NOCOMPRESS_TABLE 0 65536
LEO_NOCOMPRESS_TABLE 1 65536
LEO_NOCOMPRESS_TABLE 2 65536
LEO_NOCOMPRESS_TABLE 3 65536
LEO_NOCOMPRESS_TABLE 4 65536
LEO_NOCOMPRESS_TABLE 5 65536
LEO_NOCOMPRESS_TABLE 6 65536
LEO_NOCOMPRESS_TABLE 7 65536
LEO_NOCOMPRESS_TABLE 8 65536
LEO_NOCOMPRESS_TABLE 9 65536
LEO_NOCOMPRESS_TABLE 10 65536
LEO_NOCOMPRESS_TABLE 11 65536
LEO_NOCOMPRESS_TABLE 12 65536
LEO_NOCOMPRESS_TABLE 13 65536
LEO_NOCOMPRESS_TABLE 14 65536
LEO_NOCOMPRESS_TABLE 15 65536
LEO_NOCOMPRESS_TABLE 16 1048576 同样数据量，普通表占用17个区，使用2M空间
LEO_NOCOMPRESS_TABLE 1 65536
LEO_NOCOMPRESS_TABLE 2 65536
LEO_NOCOMPRESS_TABLE 3 65536
LEO_NOCOMPRESS_TABLE 4 65536
LEO_NOCOMPRESS_TABLE 5 65536
LEO_NOCOMPRESS_TABLE 6 65536
LEO_NOCOMPRESS_TABLE 7 65536
LEO_NOCOMPRESS_TABLE 8 65536
LEO_NOCOMPRESS_TABLE 9 65536
LEO_NOCOMPRESS_TABLE 10 65536
LEO_NOCOMPRESS_TABLE 11 65536
LEO_NOCOMPRESS_TABLE 12 65536
LEO_NOCOMPRESS_TABLE 13 65536
LEO_NOCOMPRESS_TABLE 14 65536
LEO_NOCOMPRESS_TABLE 15 65536
LEO_NOCOMPRESS_TABLE 16 1048576 同样数据量，普通表占用17个区，使用2M空间
小结：我们可以看出，普通表空间是压缩表空间的4倍多，显然压缩可以节约大量的存储空间，这在磁盘空间不足的情况下很有作用
2.索引压缩比：索引键值重复率越高，压缩比越大，索引键值重复率越低，压缩比越小
我们来比较一下，列中数据重复率的高低对压缩比的影响
我们来比较一下，列中数据重复率的高低对压缩比的影响
LS@LEO> create table leo_compress_table1 as select object_id,object_name from dba_objects; object_id列没有重复值
Table created.
LS@LEO> create table leo_compress_table2 as select 2 object_id,object_name from dba_objects; object_id列全是2，完全重复
Table created.
LS@LEO> create index leo_compress_table1_index on leo_compress_table1 (object_id) compress; 为表1创建压缩索引
Index created.
LS@LEO> create index leo_compress_table2_index on leo_compress_table2 (object_id) compress; 为表2创建压缩索引
Index created.
LS@LEO> select segment_name,extent_id,bytes from user_extents where segment_name in
('LEO_COMPRESS_TABLE1_INDEX','LEO_COMPRESS_TABLE2_INDEX');
SEGMENT_NAME EXTENT_ID BYTES
-------------------------- ---------- ----------
LEO_COMPRESS_TABLE2_INDEX 0 65536
LEO_COMPRESS_TABLE2_INDEX 1 65536
LEO_COMPRESS_TABLE2_INDEX 2 65536 重复率高表2的压缩索引，占用3个区，使用0.18M空间
-------------------------- ---------- ----------
LEO_COMPRESS_TABLE2_INDEX 0 65536
LEO_COMPRESS_TABLE2_INDEX 1 65536
LEO_COMPRESS_TABLE2_INDEX 2 65536 重复率高表2的压缩索引，占用3个区，使用0.18M空间
LEO_COMPRESS_TABLE1_INDEX 0 65536
LEO_COMPRESS_TABLE1_INDEX 1 65536
LEO_COMPRESS_TABLE1_INDEX 2 65536
LEO_COMPRESS_TABLE1_INDEX 3 65536
LEO_COMPRESS_TABLE1_INDEX 4 65536 重复率低表1的压缩索引，占用5个区，使用0.31M空间
LEO_COMPRESS_TABLE1_INDEX 1 65536
LEO_COMPRESS_TABLE1_INDEX 2 65536
LEO_COMPRESS_TABLE1_INDEX 3 65536
LEO_COMPRESS_TABLE1_INDEX 4 65536 重复率低表1的压缩索引，占用5个区，使用0.31M空间
小结：看来索引键值重复率的高低会直接影响压缩索引的存储空间，也从侧面反映了在高重复率的环境下压缩意义才大。
3.那么在索引键值重复率低的情况下，压缩索引与不压缩索引的区别有多大呢，使用show_space()存储过程分析空间使用情况，我列出了show_space()存储过程定义脚本完美版同时支持ASSM and MSSM 段对象存储属性，先要创建show_space()存储过程才能使用，默认是没有的，作者 TOM大叔，有好几个版本，用我这个就可以了，然后重点set serveroutput on;显示输出结果，否则只显示执行完成但没有结果输出
LS@LEO> create table leo_table1 as select * from dba_objects; 表1和表3结构数据完全一致，都是普通表
Table created.
LS@LEO> create table leo_table3 as select * from dba_objects;
LS@LEO> create table leo_table3 as select * from dba_objects;
Table created.
LS@LEO> create index leo_compress_table1_index on leo_table1 (object_id) compress; 创建压缩索引，object_id列没有重复值，重复率极低
Index created.
LS@LEO> create index leo_table3_index on leo_table3 (object_id) ; 创建普通索引，object_id列没有重复值，重复率极低
Index created.
create or replace procedure show_space 定义show_space()存储过程，支持ASSM and MSSM 段对象存储属性
( p_segname in varchar2,
p_owner in varchar2 default user,
p_type in varchar2 default 'TABLE',
p_partition in varchar2 default NULL )
-- this procedure uses authid current user so it can query DBA_*
-- views using privileges from a ROLE and so it can be installed
-- once per database, instead of once per user that wanted to use it
authid current_user
as
l_free_blks number;
l_total_blocks number;
l_total_bytes number;
l_unused_blocks number;
l_unused_bytes number;
l_LastUsedExtFileId number;
l_LastUsedExtBlockId number;
l_LAST_USED_BLOCK number;
l_segment_space_mgmt varchar2(255);
l_unformatted_blocks number;
l_unformatted_bytes number;
l_fs1_blocks number; l_fs1_bytes number;
l_fs2_blocks number; l_fs2_bytes number;
l_fs3_blocks number; l_fs3_bytes number;
l_fs4_blocks number; l_fs4_bytes number;
l_full_blocks number; l_full_bytes number;
( p_segname in varchar2,
p_owner in varchar2 default user,
p_type in varchar2 default 'TABLE',
p_partition in varchar2 default NULL )
-- this procedure uses authid current user so it can query DBA_*
-- views using privileges from a ROLE and so it can be installed
-- once per database, instead of once per user that wanted to use it
authid current_user
as
l_free_blks number;
l_total_blocks number;
l_total_bytes number;
l_unused_blocks number;
l_unused_bytes number;
l_LastUsedExtFileId number;
l_LastUsedExtBlockId number;
l_LAST_USED_BLOCK number;
l_segment_space_mgmt varchar2(255);
l_unformatted_blocks number;
l_unformatted_bytes number;
l_fs1_blocks number; l_fs1_bytes number;
l_fs2_blocks number; l_fs2_bytes number;
l_fs3_blocks number; l_fs3_bytes number;
l_fs4_blocks number; l_fs4_bytes number;
l_full_blocks number; l_full_bytes number;
-- inline procedure to print out numbers nicely formatted
-- with a simple label
procedure p( p_label in varchar2, p_num in number )
is
begin
dbms_output.put_line( rpad(p_label,40,'.') ||
to_char(p_num,'999,999,999,999') );
end;
begin
-- this query is executed dynamically in order to allow this procedure
-- to be created by a user who has access to DBA_SEGMENTS/TABLESPACES
-- via a role as is customary.
-- NOTE: at runtime, the invoker MUST have access to these two
-- views!
-- this query determines if the object is a ASSM object or not
begin
execute immediate
'select ts.segment_space_management
from dba_segments seg, dba_tablespaces ts
where seg.segment_name = :p_segname
and (:p_partition is null or
seg.partition_name = :p_partition)
and seg.owner = :p_owner
and seg.tablespace_name = ts.tablespace_name'
into l_segment_space_mgmt
using p_segname, p_partition, p_partition, p_owner;
exception
when too_many_rows then
dbms_output.put_line
( 'This must be a partitioned table, use p_partition => ');
return;
end;
-- with a simple label
procedure p( p_label in varchar2, p_num in number )
is
begin
dbms_output.put_line( rpad(p_label,40,'.') ||
to_char(p_num,'999,999,999,999') );
end;
begin
-- this query is executed dynamically in order to allow this procedure
-- to be created by a user who has access to DBA_SEGMENTS/TABLESPACES
-- via a role as is customary.
-- NOTE: at runtime, the invoker MUST have access to these two
-- views!
-- this query determines if the object is a ASSM object or not
begin
execute immediate
'select ts.segment_space_management
from dba_segments seg, dba_tablespaces ts
where seg.segment_name = :p_segname
and (:p_partition is null or
seg.partition_name = :p_partition)
and seg.owner = :p_owner
and seg.tablespace_name = ts.tablespace_name'
into l_segment_space_mgmt
using p_segname, p_partition, p_partition, p_owner;
exception
when too_many_rows then
dbms_output.put_line
( 'This must be a partitioned table, use p_partition => ');
return;
end;
-- if the object is in an ASSM tablespace, we must use this API
-- call to get space information, else we use the FREE_BLOCKS
-- API for the user managed segments
if l_segment_space_mgmt = 'AUTO'
then
dbms_space.space_usage
( p_owner, p_segname, p_type, l_unformatted_blocks,
l_unformatted_bytes, l_fs1_blocks, l_fs1_bytes,
l_fs2_blocks, l_fs2_bytes, l_fs3_blocks, l_fs3_bytes,
l_fs4_blocks, l_fs4_bytes, l_full_blocks, l_full_bytes, p_partition);
p( 'Unformatted Blocks ', l_unformatted_blocks );
p( 'FS1 Blocks (0-25) ', l_fs1_blocks );
p( 'FS2 Blocks (25-50) ', l_fs2_blocks );
p( 'FS3 Blocks (50-75) ', l_fs3_blocks );
p( 'FS4 Blocks (75-100)', l_fs4_blocks );
p( 'Full Blocks ', l_full_blocks );
else
dbms_space.free_blocks(
segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
freelist_group_id => 0,
free_blks => l_free_blks);
p( 'Free Blocks', l_free_blks );
end if;
-- and then the unused space API call to get the rest of the
-- information
dbms_space.unused_space
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
partition_name => p_partition,
total_blocks => l_total_blocks,
total_bytes => l_total_bytes,
unused_blocks => l_unused_blocks,
unused_bytes => l_unused_bytes,
LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
LAST_USED_BLOCK => l_LAST_USED_BLOCK );
-- information
dbms_space.unused_space
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
partition_name => p_partition,
total_blocks => l_total_blocks,
total_bytes => l_total_bytes,
unused_blocks => l_unused_blocks,
unused_bytes => l_unused_bytes,
LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
LAST_USED_BLOCK => l_LAST_USED_BLOCK );
p( 'Total Blocks', l_total_blocks );
p( 'Total Bytes', l_total_bytes );
p( 'Total MBytes', trunc(l_total_bytes/1024/1024) );
p( 'Unused Blocks', l_unused_blocks );
p( 'Unused Bytes', l_unused_bytes );
p( 'Last Used Ext FileId', l_LastUsedExtFileId );
p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
p( 'Last Used Block', l_LAST_USED_BLOCK );
end;
/
Procedure created.
p( 'Total Bytes', l_total_bytes );
p( 'Total MBytes', trunc(l_total_bytes/1024/1024) );
p( 'Unused Blocks', l_unused_blocks );
p( 'Unused Bytes', l_unused_bytes );
p( 'Last Used Ext FileId', l_LastUsedExtFileId );
p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
p( 'Last Used Block', l_LAST_USED_BLOCK );
end;
/
Procedure created.
LS@LEO> set serveroutput on; 必须设置输出show_space()存储过程结果，否则只显示执行完成但没有结果输出
LS@LEO> execute show_space('LEO_COMPRESS_TABLE1_INDEX',USER,'INDEX'); 压缩索引leo_compress_table1_index空间使用情况
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 1
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 31 其中用了31个
Total Blocks............................ 40 占用了40个数据块
Total Bytes............................. 327,680 0.31M
Total MBytes............................ 0
Unused Blocks........................... 3
Unused Bytes............................ 24,576
Last Used Ext FileId.................... 7
Last Used Ext BlockId................... 4,569
Last Used Block......................... 5
LS@LEO> execute show_space('LEO_COMPRESS_TABLE1_INDEX',USER,'INDEX'); 压缩索引leo_compress_table1_index空间使用情况
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 1
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 31 其中用了31个
Total Blocks............................ 40 占用了40个数据块
Total Bytes............................. 327,680 0.31M
Total MBytes............................ 0
Unused Blocks........................... 3
Unused Bytes............................ 24,576
Last Used Ext FileId.................... 7
Last Used Ext BlockId................... 4,569
Last Used Block......................... 5
PL/SQL procedure successfully completed.
LS@LEO> execute show_space('LEO_TABLE3_INDEX',USER,'INDEX'); 普通索引leo_table3_index空间使用情况
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 1
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 22 其中用了22个
Total Blocks............................ 32 占用了32个数据块
Total Bytes............................. 262,144 0.25M
Total MBytes............................ 0
Unused Blocks........................... 5
Unused Bytes............................ 40,960
Last Used Ext FileId.................... 7
Last Used Ext BlockId................... 4,601
Last Used Block......................... 3
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 1
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 22 其中用了22个
Total Blocks............................ 32 占用了32个数据块
Total Bytes............................. 262,144 0.25M
Total MBytes............................ 0
Unused Blocks........................... 5
Unused Bytes............................ 40,960
Last Used Ext FileId.................... 7
Last Used Ext BlockId................... 4,601
Last Used Block......................... 3
PL/SQL procedure successfully completed.
小结：那么在索引键值重复率低的情况下，压缩索引比普通索引占用的空间还多，这是为什么呢，压缩原理就是把键值用符号代替，重复键值越多冗余空间越多，但我们测试键值几乎是没有重复的，还要腾出额外空间给映射列表（键值:符号对应表），所以压缩索引使用的空间就会更多。
八、数据压缩对性能影响
原因：数据压缩后，扫描数据块变少了，导致逻辑I/O（LIO）减少（一致性读减少），同时有更多的数据块缓存到内存中，导致物理I/O（PIO）减少，当然CPU消耗自然也少了。
1.海量数据普通表和海量数据压缩表空间比较
LS@LEO> create table leo_big_table as select * from dba_objects; 海量数据普通表，16万条记录
Table created.
LS@LEO> insert into leo_big_table select * from leo_big_table;
10374 rows created.
LS@LEO> /
20748 rows created.
LS@LEO> /
41496 rows created.
LS@LEO> /
82992 rows created.
LS@LEO> select count(*) from leo_big_table;
原因：数据压缩后，扫描数据块变少了，导致逻辑I/O（LIO）减少（一致性读减少），同时有更多的数据块缓存到内存中，导致物理I/O（PIO）减少，当然CPU消耗自然也少了。
1.海量数据普通表和海量数据压缩表空间比较
LS@LEO> create table leo_big_table as select * from dba_objects; 海量数据普通表，16万条记录
Table created.
LS@LEO> insert into leo_big_table select * from leo_big_table;
10374 rows created.
LS@LEO> /
20748 rows created.
LS@LEO> /
41496 rows created.
LS@LEO> /
82992 rows created.
LS@LEO> select count(*) from leo_big_table;
COUNT(*)
----------
165984
----------
165984
LS@LEO> commit;
Commit complete.
LS@LEO> create table leo_big_table_compress compress as select * from dba_objects; 海量数据压缩表，16万条记录
Table created.
LS@LEO> insert into leo_big_table_compress select * from leo_big_table_compress;
10375 rows created.
LS@LEO> /
20750 rows created.
LS@LEO> /
41500 rows created.
LS@LEO> /
83000 rows created.
LS@LEO> select count(*) from leo_big_table_compress;
Commit complete.
LS@LEO> create table leo_big_table_compress compress as select * from dba_objects; 海量数据压缩表，16万条记录
Table created.
LS@LEO> insert into leo_big_table_compress select * from leo_big_table_compress;
10375 rows created.
LS@LEO> /
20750 rows created.
LS@LEO> /
41500 rows created.
LS@LEO> /
83000 rows created.
LS@LEO> select count(*) from leo_big_table_compress;
COUNT(*)
----------
166000
----------
166000
LS@LEO>
LS@LEO> commit;
Commit complete.
LS@LEO> execute show_space('LEO_BIG_TABLE'); 海量数据普通表，16万条记录
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 1
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 5
Full Blocks ..................... 2,064 其中2064个装满了数据
Total Blocks............................ 2,176 占用了2176个数据块
Total Bytes............................. 17,825,792
Total MBytes............................ 17 17M空间
Unused Blocks........................... 64
Unused Bytes............................ 524,288
Last Used Ext FileId.................... 7
Last Used Ext BlockId................... 6,793
Last Used Block......................... 64
LS@LEO> commit;
Commit complete.
LS@LEO> execute show_space('LEO_BIG_TABLE'); 海量数据普通表，16万条记录
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 1
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 5
Full Blocks ..................... 2,064 其中2064个装满了数据
Total Blocks............................ 2,176 占用了2176个数据块
Total Bytes............................. 17,825,792
Total MBytes............................ 17 17M空间
Unused Blocks........................... 64
Unused Bytes............................ 524,288
Last Used Ext FileId.................... 7
Last Used Ext BlockId................... 6,793
Last Used Block......................... 64
PL/SQL procedure successfully completed.
LS@LEO> execute show_space('LEO_BIG_TABLE_COMPRESS'); 海量数据压缩表，16万条记录
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 1
FS4 Blocks (75-100)..................... 31
Full Blocks ..................... 1,786 其中1786个装满了数据
Total Blocks............................ 1,920 占用了1920个数据块
Total Bytes............................. 15,728,640
Total MBytes............................ 15 15M空间
Unused Blocks........................... 64
Unused Bytes............................ 524,288
Last Used Ext FileId.................... 7
Last Used Ext BlockId................... 8,713
Last Used Block......................... 64
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 1
FS4 Blocks (75-100)..................... 31
Full Blocks ..................... 1,786 其中1786个装满了数据
Total Blocks............................ 1,920 占用了1920个数据块
Total Bytes............................. 15,728,640
Total MBytes............................ 15 15M空间
Unused Blocks........................... 64
Unused Bytes............................ 524,288
Last Used Ext FileId.................... 7
Last Used Ext BlockId................... 8,713
Last Used Block......................... 64
PL/SQL procedure successfully completed.
小结：显而易见，海量数据的环境下，压缩表比普通表占用的空间更少
小结：显而易见，海量数据的环境下，压缩表比普通表占用的空间更少
2.10046事件追踪查询select普通表和压缩表的sql执行计划（全表扫描）
LS@LEO> alter session set events '10046 trace name context forever,level 12';
LS@LEO> select * from leo_big_table;
165984 rows selected.
LS@LEO> select * from leo_big_table_compress;
166000 rows selected.
tkprof leo_ora_1983.trc leo_20120812.txt sys=no
more leo_20120812.txt
LS@LEO> select * from leo_big_table;
165984 rows selected.
LS@LEO> select * from leo_big_table_compress;
166000 rows selected.
tkprof leo_ora_1983.trc leo_20120812.txt sys=no
more leo_20120812.txt
select * from leo_big_table 普通表sql性能指标
call count cpu elapsed 磁盘读disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0 硬解析
Execute 1 0.00 0.00 0 0 0 0 执行
Fetch 11067 1.55 1.44 0 13000 0 165984 取操作
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11069 1.55 1.44 0 13000 0 165984
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0 硬解析
Execute 1 0.00 0.00 0 0 0 0 执行
Fetch 11067 1.55 1.44 0 13000 0 165984 取操作
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11069 1.55 1.44 0 13000 0 165984
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 27
Optimizer mode: ALL_ROWS
Parsing user id: 27
Rows Row Source Operation
------- -----------------------------------------------------------------------
165984 TABLE ACCESS FULL LEO_BIG_TABLE (cr=13000 pr=0 pw=0 time=1992805 us) 输出165984行，一致性读13000
------- -----------------------------------------------------------------------
165984 TABLE ACCESS FULL LEO_BIG_TABLE (cr=13000 pr=0 pw=0 time=1992805 us) 输出165984行，一致性读13000
********************************************************************************
select * from leo_big_table_compress 压缩表sql性能指标
call count cpu elapsed 磁盘读disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0 硬解析
Execute 1 0.00 0.00 0 0 0 0 执行
Fetch 11068 1.14 1.13 0 12775 0 166000 取操作
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11070 1.14 1.13 0 12775 0 166000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0 硬解析
Execute 1 0.00 0.00 0 0 0 0 执行
Fetch 11068 1.14 1.13 0 12775 0 166000 取操作
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11070 1.14 1.13 0 12775 0 166000
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 27
Optimizer mode: ALL_ROWS
Parsing user id: 27
Rows Row Source Operation
------- ---------------------------------------------------
166000 TABLE ACCESS FULL LEO_BIG_TABLE_COMPRESS (cr=12775 pr=0 pw=0 time=1495219 us) 输出166000行，一致性读12775
------- ---------------------------------------------------
166000 TABLE ACCESS FULL LEO_BIG_TABLE_COMPRESS (cr=12775 pr=0 pw=0 time=1495219 us) 输出166000行，一致性读12775
********************************************************************************
小结：这里磁盘读disk都是0是因为所有记录都缓存在内存中，从一致性读、CPU、elapsed等指标上看，压缩表还是优于普通表
小结：这里磁盘读disk都是0是因为所有记录都缓存在内存中，从一致性读、CPU、elapsed等指标上看，压缩表还是优于普通表
3.10046事件追踪查询select普通表和压缩表的sql执行计划（走索引）
LS@LEO> create index leo_big_table_index on leo_big_table(object_id); 普通表创建索引
Index created.
LS@LEO> create index leo_big_table__compress_index on leo_big_table_compress(object_id); 压缩表创建索引
Index created.
LS@LEO> select * from leo_big_table where object_id=5276;
LS@LEO> select * from leo_big_table_compress where object_id=5276;
select * from leo_big_table where object_id=5276 普通表走索引检索sql性能指标
call count cpu elapsed 磁盘读disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0 硬解析
Execute 1 0.00 0.00 0 0 0 0 执行
Fetch 3 0.00 0.00 0 20 0 16 取操作
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.00 0.00 0 20 0 16
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0 硬解析
Execute 1 0.00 0.00 0 0 0 0 执行
Fetch 3 0.00 0.00 0 20 0 16 取操作
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.00 0.00 0 20 0 16
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 27
Optimizer mode: ALL_ROWS
Parsing user id: 27
Rows Row Source Operation
------- ---------------------------------------------------
16 TABLE ACCESS BY INDEX ROWID LEO_BIG_TABLE (cr=20 pr=0 pw=0 time=316 us) 输出16行，走索引，表一致性读20
16 INDEX RANGE SCAN LEO_BIG_TABLE_INDEX (cr=4 pr=0 pw=0 time=169 us)(object id 11066) 因为索引没压缩，索引一致性读4
------- ---------------------------------------------------
16 TABLE ACCESS BY INDEX ROWID LEO_BIG_TABLE (cr=20 pr=0 pw=0 time=316 us) 输出16行，走索引，表一致性读20
16 INDEX RANGE SCAN LEO_BIG_TABLE_INDEX (cr=4 pr=0 pw=0 time=169 us)(object id 11066) 因为索引没压缩，索引一致性读4
********************************************************************************
select * from leo_big_table_compress where object_id=5276 压缩表走索引检索sql性能指标
call count cpu elapsed 磁盘读disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 2 0 0 硬解析
Execute 1 0.00 0.00 0 0 0 0 执行
Fetch 3 0.00 0.00 0 20 0 16 取操作
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.00 0.00 0 22 0 16
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 2 0 0 硬解析
Execute 1 0.00 0.00 0 0 0 0 执行
Fetch 3 0.00 0.00 0 20 0 16 取操作
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.00 0.00 0 22 0 16
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 27
Optimizer mode: ALL_ROWS
Parsing user id: 27
Rows Row Source Operation
------- ---------------------------------------------------
16 TABLE ACCESS BY INDEX ROWID LEO_BIG_TABLE_COMPRESS (cr=20 pr=0 pw=0 time=140 us) 输出16行，走索引，一致性读22
16 INDEX RANGE SCAN LEO_BIG_TABLE__COMPRESS_INDEX (cr=4 pr=0 pw=0 time=31 us)(object id 11067) 因为索引没压缩，索引一致性读4
------- ---------------------------------------------------
16 TABLE ACCESS BY INDEX ROWID LEO_BIG_TABLE_COMPRESS (cr=20 pr=0 pw=0 time=140 us) 输出16行，走索引，一致性读22
16 INDEX RANGE SCAN LEO_BIG_TABLE__COMPRESS_INDEX (cr=4 pr=0 pw=0 time=31 us)(object id 11067) 因为索引没压缩，索引一致性读4
********************************************************************************
小结：我们看到压缩表比普通表的一致性读要多2个，原因有二，第一 查询的数据太少，不容易看出来。第二 先解压再显示，解压需要额外的开销。在海量数据环境下，压缩表走索引的效果应该比普通表要好。
小结：我们看到压缩表比普通表的一致性读要多2个，原因有二，第一 查询的数据太少，不容易看出来。第二 先解压再显示，解压需要额外的开销。在海量数据环境下，压缩表走索引的效果应该比普通表要好。
4.10046事件追踪order by group by普通表和压缩表的sql执行计划
select * from leo_big_table order by owner 普通表order by 性能指标
call count cpu elapsed 磁盘读disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0 硬解析
Execute 1 0.00 0.00 0 0 0 0 执行
Fetch 11067 2.91 13.21 2116 2087 28 165984 取操作
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11069 2.91 13.21 2116 2088 28 165984
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0 硬解析
Execute 1 0.00 0.00 0 0 0 0 执行
Fetch 11067 2.91 13.21 2116 2087 28 165984 取操作
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11069 2.91 13.21 2116 2088 28 165984
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 27
Optimizer mode: ALL_ROWS
Parsing user id: 27
Rows Row Source Operation
------- ---------------------------------------------------
165984 SORT ORDER BY (cr=2087 pr=2116 pw=2116 time=13536800 us) 全表扫描排序后输出165984行，一致性读2087，磁盘读2116
165984 TABLE ACCESS FULL LEO_BIG_TABLE (cr=2076 pr=0 pw=0 time=7635325 us)
------- ---------------------------------------------------
165984 SORT ORDER BY (cr=2087 pr=2116 pw=2116 time=13536800 us) 全表扫描排序后输出165984行，一致性读2087，磁盘读2116
165984 TABLE ACCESS FULL LEO_BIG_TABLE (cr=2076 pr=0 pw=0 time=7635325 us)
********************************************************************************
select * from leo_big_table_compress order by owner 压缩表order by 性能指标
call count cpu elapsed 磁盘读disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0 硬解析
Execute 1 0.00 0.00 0 0 0 0 执行
Fetch 11068 1.65 2.16 2117 1834 4 166000 取操作
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11070 1.65 2.17 2117 1834 4 166000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0 硬解析
Execute 1 0.00 0.00 0 0 0 0 执行
Fetch 11068 1.65 2.16 2117 1834 4 166000 取操作
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11070 1.65 2.17 2117 1834 4 166000
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 27
Optimizer mode: ALL_ROWS
Parsing user id: 27
Rows Row Source Operation
------- ---------------------------------------------------
166000 SORT ORDER BY (cr=1834 pr=2117 pw=2117 time=2356632 us) 全表扫描排序后输出166000行，一致性读1834，磁盘读2117
166000 TABLE ACCESS FULL LEO_BIG_TABLE_COMPRESS (cr=1823 pr=0 pw=0 time=1494518 us)
------- ---------------------------------------------------
166000 SORT ORDER BY (cr=1834 pr=2117 pw=2117 time=2356632 us) 全表扫描排序后输出166000行，一致性读1834，磁盘读2117
166000 TABLE ACCESS FULL LEO_BIG_TABLE_COMPRESS (cr=1823 pr=0 pw=0 time=1494518 us)
********************************************************************************
select owner,count(*) from leo_big_table group by owner 普通表group by 性能指标
call count cpu elapsed 磁盘读disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0 硬解析
Execute 1 0.00 0.00 0 15 0 0 执行
Fetch 2 0.10 0.10 0 2076 0 9 取操作
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.10 0.10 0 2091 0 9
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0 硬解析
Execute 1 0.00 0.00 0 15 0 0 执行
Fetch 2 0.10 0.10 0 2076 0 9 取操作
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.10 0.10 0 2091 0 9
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 27
Optimizer mode: ALL_ROWS
Parsing user id: 27
Rows Row Source Operation
------- ---------------------------------------------------
9 HASH GROUP BY (cr=2091 pr=0 pw=0 time=116605 us) 分组计算后输出9行，一致性读2091
165984 TABLE ACCESS FULL LEO_BIG_TABLE (cr=2076 pr=0 pw=0 time=1825987 us) 全表扫描165984行
------- ---------------------------------------------------
9 HASH GROUP BY (cr=2091 pr=0 pw=0 time=116605 us) 分组计算后输出9行，一致性读2091
165984 TABLE ACCESS FULL LEO_BIG_TABLE (cr=2076 pr=0 pw=0 time=1825987 us) 全表扫描165984行
********************************************************************************
select owner,count(*) from leo_big_table_compress group by owner 压缩表group by 性能指标
call count cpu elapsed 磁盘读disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0 硬解析
Execute 1 0.00 0.00 0 0 0 0 执行
Fetch 2 0.08 0.08 0 1823 0 9 取操作
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.08 0.08 0 1823 0 9
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0 硬解析
Execute 1 0.00 0.00 0 0 0 0 执行
Fetch 2 0.08 0.08 0 1823 0 9 取操作
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.08 0.08 0 1823 0 9
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 27
Optimizer mode: ALL_ROWS
Parsing user id: 27
Rows Row Source Operation
------- ---------------------------------------------------
9 HASH GROUP BY (cr=1838 pr=0 pw=0 time=94680 us) 分组计算后输出9行，一致性读1838
166000 TABLE ACCESS FULL LEO_BIG_TABLE_COMPRESS (cr=1823 pr=0 pw=0 time=1162061 us) 全表扫描166000行
------- ---------------------------------------------------
9 HASH GROUP BY (cr=1838 pr=0 pw=0 time=94680 us) 分组计算后输出9行，一致性读1838
166000 TABLE ACCESS FULL LEO_BIG_TABLE_COMPRESS (cr=1823 pr=0 pw=0 time=1162061 us) 全表扫描166000行
小结：从上面测试结果来看，压缩表的性能更好一些，尤其在逻辑读I/O（LIO）、一致性读、物理读I/O（PIO）、CPU等指标上。
九、数据压缩和DML操作
答：不管从实际应用还是官方说明，“压缩”技术都是属于OLAP数据仓库领域
问：那么在OLTP领域就不能使用“压缩”技术了嘛？ 其实不然，特定场合DML操作可以压缩处理的，但大部分是不作压缩的
特定场合
（1）alter table 表名 move compress 给普通表进行压缩，通过MOVE方式进行数据重组
（2）alter table 表名 move partition 分区名 compress 给分区表进行压缩，每个分区都可以独立压缩，结尾必须添加compress关键字否则不做压缩
（3）create table 表名 compress as select CTAS 对象属性
（4）insert /*+ append */ 直接加载插入数据，必压缩。 常规 insert into 不压缩数据
（5）sqlldr 参数 direct path sqlload 直接路径加载，必压缩
答：不管从实际应用还是官方说明，“压缩”技术都是属于OLAP数据仓库领域
问：那么在OLTP领域就不能使用“压缩”技术了嘛？ 其实不然，特定场合DML操作可以压缩处理的，但大部分是不作压缩的
特定场合
（1）alter table 表名 move compress 给普通表进行压缩，通过MOVE方式进行数据重组
（2）alter table 表名 move partition 分区名 compress 给分区表进行压缩，每个分区都可以独立压缩，结尾必须添加compress关键字否则不做压缩
（3）create table 表名 compress as select CTAS 对象属性
（4）insert /*+ append */ 直接加载插入数据，必压缩。 常规 insert into 不压缩数据
（5）sqlldr 参数 direct path sqlload 直接路径加载，必压缩
实验 1 CTAS
LS@LEO> create table leo_test1 compress as select * from dba_objects; CTAS 创建初就压缩
Table created.
LS@LEO> create table leo_test2 compress as select * from dba_objects where 1=2; 只要结构不要数据
Table created.
LS@LEO> select count(*) from leo_test1; 有10378行
COUNT(*)
----------
10378
LS@LEO> select count(*) from leo_test2; 只要结构不要数据 successful
Table created.
LS@LEO> create table leo_test2 compress as select * from dba_objects where 1=2; 只要结构不要数据
Table created.
LS@LEO> select count(*) from leo_test1; 有10378行
COUNT(*)
----------
10378
LS@LEO> select count(*) from leo_test2; 只要结构不要数据 successful
COUNT(*)
----------
0
LS@LEO> insert into leo_test2 select * from dba_objects; 常规insert into 不压缩数据
10379 rows created.
LS@LEO> commit; 提交
----------
0
LS@LEO> insert into leo_test2 select * from dba_objects; 常规insert into 不压缩数据
10379 rows created.
LS@LEO> commit; 提交
LS@LEO> set serveroutput on; 打开屏幕输出
LS@LEO> execute show_space('LEO_TEST1'); 使用CTAS直接压缩
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 45 其中用了45个装满数据
Total Blocks............................ 56 占用了56个数据块
Total Bytes............................. 458,752 0.43M空间，已压缩
Total MBytes............................ 0
Unused Blocks........................... 5
Unused Bytes............................ 40,960
Last Used Ext FileId.................... 7
Last Used Ext BlockId................... 9,649
Last Used Block......................... 3
LS@LEO> execute show_space('LEO_TEST1'); 使用CTAS直接压缩
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 45 其中用了45个装满数据
Total Blocks............................ 56 占用了56个数据块
Total Bytes............................. 458,752 0.43M空间，已压缩
Total MBytes............................ 0
Unused Blocks........................... 5
Unused Bytes............................ 40,960
Last Used Ext FileId.................... 7
Last Used Ext BlockId................... 9,649
Last Used Block......................... 3
PL/SQL procedure successfully completed.
LS@LEO> execute show_space('LEO_TEST2'); 常规insert into 不压缩数据
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 1
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 2
Full Blocks ..................... 115 其中用了115个装满数据
Total Blocks............................ 128 占用了128个数据块
Total Bytes............................. 1,048,576
Total MBytes............................ 1 1M空间
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 7
Last Used Ext BlockId................... 9,777
Last Used Block......................... 8
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 1
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 2
Full Blocks ..................... 115 其中用了115个装满数据
Total Blocks............................ 128 占用了128个数据块
Total Bytes............................. 1,048,576
Total MBytes............................ 1 1M空间
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 7
Last Used Ext BlockId................... 9,777
Last Used Block......................... 8
PL/SQL procedure successfully completed.
小结：我们看到leo_test2表比leo_test1表占用的空间几乎多了一倍，为什么都用CTAS创建的表空间会不一样呢，leo_test2表在创建时只有结构没有数据，压缩只针对数据压缩因此没有数据就没有压缩。后来我们使用了常规insert into 插入数据但数据是不压缩的，这是导致表2比表1占用空间多的原因。
实验 2 insert /*+ append */ 直接加载插入数据，必压缩
LS@LEO> truncate table leo_test2; 清空leo_test2表
Table truncated.
LS@LEO> insert /*+ append */ into leo_test2 select * from dba_objects; 直接加载插入数据必压缩
10379 rows created.
LS@LEO> commit; 提交
LS@LEO> execute show_space('LEO_TEST2'); 分析空间使用情况
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 45 其中用了45个装满数据
Total Blocks............................ 56 占用了56个数据块
Total Bytes............................. 458,752 0.43M空间，已压缩
Total MBytes............................ 0
Unused Blocks........................... 5
Unused Bytes............................ 40,960
Last Used Ext FileId.................... 7
Last Used Ext BlockId................... 9,705
Last Used Block......................... 3
Table truncated.
LS@LEO> insert /*+ append */ into leo_test2 select * from dba_objects; 直接加载插入数据必压缩
10379 rows created.
LS@LEO> commit; 提交
LS@LEO> execute show_space('LEO_TEST2'); 分析空间使用情况
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 45 其中用了45个装满数据
Total Blocks............................ 56 占用了56个数据块
Total Bytes............................. 458,752 0.43M空间，已压缩
Total MBytes............................ 0
Unused Blocks........................... 5
Unused Bytes............................ 40,960
Last Used Ext FileId.................... 7
Last Used Ext BlockId................... 9,705
Last Used Block......................... 3
小结：使用insert /*+ append */ 直接加载插入数据方式，数据必须压缩状态
实验 3 update操作对压缩数据作解压缩处理，空间立刻恢复为未压缩时的容量
LS@LEO> update leo_test1 set object_name=object_name||'leo_nocompress'; leo_test1已经是压缩状态，现在对其进行update操作
10378 rows updated.
LS@LEO> commit;
Commit complete.
LS@LEO> execute show_space('LEO_TEST1'); update操作对压缩数据进行解压缩处理
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 1
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 36
Full Blocks ..................... 143 其中用了143个装满数据
Total Blocks............................ 256 占用了256个数据块
Total Bytes............................. 2,097,152
Total MBytes............................ 2 2M空间，已经解压缩了，空间恢复为未压缩是的容量
Unused Blocks........................... 64
Unused Bytes............................ 524,288
Last Used Ext FileId.................... 7
Last Used Ext BlockId................... 9,865
Last Used Block......................... 64
10378 rows updated.
LS@LEO> commit;
Commit complete.
LS@LEO> execute show_space('LEO_TEST1'); update操作对压缩数据进行解压缩处理
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 1
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 36
Full Blocks ..................... 143 其中用了143个装满数据
Total Blocks............................ 256 占用了256个数据块
Total Bytes............................. 2,097,152
Total MBytes............................ 2 2M空间，已经解压缩了，空间恢复为未压缩是的容量
Unused Blocks........................... 64
Unused Bytes............................ 524,288
Last Used Ext FileId.................... 7
Last Used Ext BlockId................... 9,865
Last Used Block......................... 64
PL/SQL procedure successfully completed.
小结：此时leo_test1表的空间立刻恢复为未压缩时的容量，但比原来的空间还要大呢，是因为update操作将记录的长度变大了，空间自然也变大了！
实验 4 给普通表进行压缩，通过MOVE方式进行数据重组
LS@LEO> alter table leo_test1 move compress; 现在对普通表进行压缩重组数据
Table altered.
LS@LEO> execute show_space('LEO_TEST1');
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 65 其中用了65个装满数据
Total Blocks............................ 72 占用了72个数据块
Total Bytes............................. 589,824 0.56M空间，已经压缩
Total MBytes............................ 0
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 7
Last Used Ext BlockId................... 9,849
Last Used Block......................... 8
Table altered.
LS@LEO> execute show_space('LEO_TEST1');
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 65 其中用了65个装满数据
Total Blocks............................ 72 占用了72个数据块
Total Bytes............................. 589,824 0.56M空间，已经压缩
Total MBytes............................ 0
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 7
Last Used Ext BlockId................... 9,849
Last Used Block......................... 8
PL/SQL procedure successfully completed.
小结：同样的数据经过重组数据进行压缩，可以比未压缩时节约了4倍空间，在一些特定场合使用压缩技术还是非常有效果的。
实验 5 给分区表进行压缩，每个分区都可以独立压缩
LS@LEO> create table leo_test_partition1 (object_id,object_name) 我们先创建一个分区表，共6个分区
partition by range (object_id)
(
partition leo_p1 values less than (1000),
partition leo_p2 values less than (2000),
partition leo_p3 values less than (3000),
partition leo_p4 values less than (4000),
partition leo_p5 values less than (5000),
partition leo_max values less than (maxvalue)
)
as select object_id,object_name from dba_objects;
Table created.
partition by range (object_id)
(
partition leo_p1 values less than (1000),
partition leo_p2 values less than (2000),
partition leo_p3 values less than (3000),
partition leo_p4 values less than (4000),
partition leo_p5 values less than (5000),
partition leo_max values less than (maxvalue)
)
as select object_id,object_name from dba_objects;
Table created.
LS@LEO> insert into leo_test_partition1 select * from leo_test_partition1; 多次加载数据
LS@LEO> select count(*) from leo_test_partition1; 33万行
COUNT(*)
----------
332576
----------
332576
LS@LEO> select count(*) from leo_test_partition1 partition (leo_p2); leo_p2分区有32000行
COUNT(*)
----------
32000
----------
32000
LS@LEO> commit; 提交
LS@LEO> select partition_name,compression from user_tab_partitions where table_name='LEO_TEST_PARTITION1'; 6个分区都是未压缩状态
PARTITION_NAME COMPRESS
------------------------------ --------
LEO_MAX DISABLED
LEO_P1 DISABLED
LEO_P2 DISABLED
LEO_P3 DISABLED
LEO_P4 DISABLED
LEO_P5 DISABLED
------------------------------ --------
LEO_MAX DISABLED
LEO_P1 DISABLED
LEO_P2 DISABLED
LEO_P3 DISABLED
LEO_P4 DISABLED
LEO_P5 DISABLED
LS@LEO> execute show_space('LEO_TEST_PARTITION1',USER,'TABLE PARTITION','LEO_P2'); leo_p2分区未压缩时空间情况
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 5
Full Blocks ..................... 105 其中用了105个装满数据
Total Blocks............................ 120 占用了120个数据块
Total Bytes............................. 983,040 0.9375M
Total MBytes............................ 0
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 7
Last Used Ext BlockId................... 10,601
Last Used Block......................... 8
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 5
Full Blocks ..................... 105 其中用了105个装满数据
Total Blocks............................ 120 占用了120个数据块
Total Bytes............................. 983,040 0.9375M
Total MBytes............................ 0
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 7
Last Used Ext BlockId................... 10,601
Last Used Block......................... 8
PL/SQL procedure successfully completed.
LS@LEO> alter table leo_test_partition1 move partition leo_p2 compress; 单独对leo_p2分区进行压缩，结尾必须添加compress关键字否则不做压缩
Table altered.
LS@LEO> select partition_name,compression from user_tab_partitions where table_name='LEO_TEST_PARTITION1'; leo_p2分区已经压缩
Table altered.
LS@LEO> select partition_name,compression from user_tab_partitions where table_name='LEO_TEST_PARTITION1'; leo_p2分区已经压缩
PARTITION_NAME COMPRESS
------------------------------ --------
LEO_MAX DISABLED
LEO_P1 DISABLED
LEO_P2 ENABLED
LEO_P3 DISABLED
LEO_P4 DISABLED
LEO_P5 DISABLED
------------------------------ --------
LEO_MAX DISABLED
LEO_P1 DISABLED
LEO_P2 ENABLED
LEO_P3 DISABLED
LEO_P4 DISABLED
LEO_P5 DISABLED
LS@LEO> execute show_space('LEO_TEST_PARTITION1',USER,'TABLE PARTITION','LEO_P2'); leo_p2分区是压缩时空间情况
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 104 其中用了104个装满数据，只比原来少了1个块
Total Blocks............................ 120 占用了120个数据块
Total Bytes............................. 983,040
Total MBytes............................ 0
Unused Blocks........................... 6
Unused Bytes............................ 49,152
Last Used Ext FileId.................... 7
Last Used Ext BlockId................... 11,297
Last Used Block......................... 2
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 104 其中用了104个装满数据，只比原来少了1个块
Total Blocks............................ 120 占用了120个数据块
Total Bytes............................. 983,040
Total MBytes............................ 0
Unused Blocks........................... 6
Unused Bytes............................ 49,152
Last Used Ext FileId.................... 7
Last Used Ext BlockId................... 11,297
Last Used Block......................... 2
PL/SQL procedure successfully completed.
小结：我们看到leo_p2分区压缩时比未压缩时只在使用的数据块上少了一个，感觉基本上没怎么压，后来分析数据发现，记录重复率非常小，样本数据条数也不够大，导致压缩比也小，效果稍差。
原理出处：表压缩比：列中数据重复率越高，压缩比越大，列中数据重复率越低，压缩比越小
大家在做测试的时候可以和我的结果对比一下，看看是不是一样，如果分析的不足，请提出宝贵意见，共同进步：）
原理出处：表压缩比：列中数据重复率越高，压缩比越大，列中数据重复率越低，压缩比越小
大家在做测试的时候可以和我的结果对比一下，看看是不是一样，如果分析的不足，请提出宝贵意见，共同进步：）
十、数据压缩应用场景
1.静态数据：这种数据处在不在改变的状态，只用于查询和分析，可以压缩
2.历史数据：这种数据当前已经不在使用，只用于以后归档查询，可以压缩
3.海量数据：提高数据处理效率，减少内存和磁盘I/O开销，可以压缩
4.备份数据：压缩备份可以节约大量空间给其他对象使用，可以压缩
1.静态数据：这种数据处在不在改变的状态，只用于查询和分析，可以压缩
2.历史数据：这种数据当前已经不在使用，只用于以后归档查询，可以压缩
3.海量数据：提高数据处理效率，减少内存和磁盘I/O开销，可以压缩
4.备份数据：压缩备份可以节约大量空间给其他对象使用，可以压缩
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/26686207/viewspace-740928/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
