oracle compress压缩小记

1,压缩的概念
    1,仅用于heap-organized表
    2,尤其适用于数据仓库,此类情形的insert和update操作很小量
    3,在11G测试,压缩表可以EXP和IMP
   
2,压缩的语法
    1,整个表级,可以在对象表或关系表物理属性之中指定
    2,对于范围分区表,可在每个分区描述中指定
    3,对于列表分区表,可以在每个列表分区描述中指定
    4,对于NESTED 表的存储表类型,同样可以在NESTED 表的列存储子句中指定
3,压缩表的一些限制:
    1,如果表的列超过255个列,不支持
    2,lob段不支付,经测,是否启用压缩,LOB占用的大小未变
    3,IOT表不支持,OVERFLOW SEGMENT或OVERFLOW SEGMENT的分区也不支持;或IOT表的任何一个映射表段
    4,外部表不支持或集表不支持
    5,HASH分区或HASH和列表子分区不支持.这些分区的压缩模式属性要继承于表空间,表,表分区的属性
   
4,压缩测试
   1,压缩对于占用空间大小的测试
  
   --创建测试表
   SQL> create table t_compression(a int);

表已创建。

  SQL> select compression,compress_for from user_tables where table_name='T_COMPRE
SSION';

COMPRESSION      COMPRESS_FOR
---------------- ------------------------
DISABLED

--插入数据
SQL> insert into t_compression select level from dual connect  by level<5e5;

已创建499999行。

SQL> commit;

提交完成。

---查询非压缩模式的表占用的段大小为27MB
SQL> select segment_name,bytes/1024/1024 mb from user_segments where segment_nam
e='T_COMPRESSION';

SEGMENT_NAME
--------------------------------------------------------------------------------

        MB
----------
T_COMPRESSION
        27
       
       
SQL> truncate table t_compression;

表被截断。

---数据已经被清空截断,如下查询发现仍有空间分配给它,这个问题在另一文中再测试
SQL> select segment_name,bytes/1024/1024 mb from user_segments where segment_nam
e='T_COMPRESSION';

SEGMENT_NAME
--------------------------------------------------------------------------------

        MB
----------
T_COMPRESSION
     .0625
       
---开启表压缩模式
SQL> alter table t_compression compress;

表已更改。

---查询表是否压缩,压缩的级别
SQL> select compression,compress_for from user_tables where table_name='T_COMPRE
SSION';

COMPRESSION      COMPRESS_FOR
---------------- ------------------------
ENABLED          BASIC

SQL> insert into t_compression select level from dual connect  by level<5e5;

已创建499999行。

SQL> commit;

提交完成。

--启用压缩模式,仅占用原来1/3小的空间
SQL> select segment_name,bytes/1024/1024 mb from user_segments where segment_nam
e='T_COMPRESSION';

SEGMENT_NAME
--------------------------------------------------------------------------------

        MB
----------
T_COMPRESSION
         7

  2,压缩对于时间的影响
---压缩模式下
23:29:08 SQL> insert into t_compression select level from dual connect  by level
<5e5;

已创建499999行。

已用时间:  00: 00: 00.47
23:29:13 SQL> commit;

提交完成。

已用时间:  00: 00: 00.01
23:29:19 SQL> truncate table t_compression;

表被截断。

 

--------非压缩模式,自知可知压缩模式仍会更多耗用时间
已用时间:  00: 00: 00.23
23:29:29 SQL> alter table t_compression nocompress;

表已更改。

已用时间:  00: 00: 00.01
23:29:42 SQL> insert into t_compression select level from dual connect  by level
<5e5;

已创建499999行。

已用时间:  00: 00: 00.53
23:29:47 SQL> commit;
     
       
       
   4,压缩对于DML的影响
      1,redo size的区别
      2,cpu time及db time的区别
      3,执行计划方面,逻辑读(一致性读和当前读的区别)
     

4.2.1 Table Compression
 
Heap-organized tables can be stored in a compressed format that is transparent for any kind of application. Table compression was designed primarily for read-only environments and can cause processing overhead for DML operations in some cases. However, it increases performance for many read operations, especially when your system is I/O bound.
 
Compressed data in a database block is self-contained which means that all the information needed to re-create the uncompressed data in a block is available within that block. A block will also be kept compressed in the buffer cache. Table compression not only reduces the disk storage but also the memory usage, specifically the buffer cache requirements. Performance improvements are accomplished by reducing the amount of necessary I/O operations for accessing a table and by increasing the probability of buffer cache hits.
 

4.2.1.1 Estimating the Compression factor
 
Table compression works by eliminating column value repetitions within individual blocks. Duplicate values in all the rows and columns in a block are stored once at the beginning of the block, in what is called a symbol table for that block. All occurrences of such values are replaced with a short reference to the symbol table. The compression is higher in blocks that have more repeated values.
 
Before compressing large tables you should estimate the expected compression factor. The compression factor is defined as the number of blocks necessary to store the information in an uncompressed form. divided by the number of blocks necessary for a compressed storage. The compression factor can be estimated by sampling a small number of representative data blocks of the table to be compressed and comparing the average number of records for each block for the uncompressed and compressed case. Experience shows that approximately 1000 data blocks provides a very accurate estimation of the compression factor. Note that the more blocks you are sampling, the more accurate the result become.
 

4.2.1.2 Tuning to Achieve a Better Compression Ratio
 
Oracle achieves a good compression factor in many cases with no special tuning. As a database administrator or application developer, you can try to tune the compression factor by reorganizing the records when the compression actually takes place. Tuning can improve the compression factor slightly in some cases and very substantially in other cases.
 
To improve the compression factor you have to increase the likelihood of value repetitions within a database block. The compression factor that can be achieved depends on the cardinality of a specific column or column pairs (representing the likelihood of column value repetitions) and on the average row length of those columns. Oracle table compression not only compresses duplicate values of a single column but tries to use multi-column value pairs whenever possible. Without a very detailed understanding of the data distribution it is very difficult to predict the most optimal order.
     

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-752224/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-752224/