oracle 表压缩详细介绍

前言：

       表压缩应该是从10g开始支持的吧，oracle 11g exadata将这个特性用到了极点，并大力推广压缩技术，在现代机器cpu性能井喷的状态下，对IO，内存占用的减少显得尤为重要，压缩技术肯定是未来的一种普遍技术，现在这个技术也越来越成熟，那就让我们在以后的维护过程中用起来，早日实现这个功能的普及，福利DBA。

        oracle 12c同样对压缩技术进行了支持，下面是各种压缩方法的优缺点，语法和介绍：

 

基本表压缩        压缩级别高       cpu消耗少                       适合DSS             语法ROW STORE COMPRESS[BASIC]         未使用direct-path 插入和更新数据将不压缩
高级压缩            压缩级别高       cpu消耗少                       适合OLTP DSS  语法ROW STORE COMPRESS ADVANCED 同上
数据仓库压缩   （混合列压缩） 压缩级别更高 cpu消耗高 适合dss              语法COLUMN STORE COMPRESS FOR QUERY [LOW|HIGH]   高cpu，不使用direct-path是行级别存储，而非列格式，压缩级别将降低
归档压缩          （混合列压缩）  压缩级别最高 cpu消耗最高 适合归档        语法COLUMN STORE COMPRESS FOR ARCHIVE[LOW|HIGH] 同上

 

各种压缩技术优缺点：

基本表压缩适合direct path 插入和受限数据类型sql
高级行压缩支持所有的数据类型和所有sql

混合列压缩适合不频繁的update

归档压缩仅适合direct path inserted，传统的插入和更新也是支持的，不过得使用ADO（automatic data optimization)策略移动行达到要求的混合列压缩级别

总结：大家可以发现高级行压缩是理想压缩方式，其他级别都是冷门压缩，根据情况使用

测试一下吧
我的环境：oracle 12c for linux enterprise 5 +file system   --single instance

 

SQL> create table tt (id number) row store compress basic; --成功
SQL> create table tt (id number) row store compress advanced; --成功
SQL> create table tt (id number) column store compress for query high； --报错，发现普通存储不支持，只适合exadata上使用

create table tt (id number) column store compress for query high
*
ERROR at line 1:
ORA-64307:  Exadata Hybrid Columnar Compression is not supported for
tablespaces on this storage type
SQL> create table tt (id number) column store compress for archive high; --报错同上

SQL> create table tt (id number);

Table created.

SQL> alter table tt row store compress advanced; --表后期可以来指定压缩级别

Table altered.

SQL>

 

总结：发现有点遗憾的是数据仓库级压缩和归档压缩在普通库上不支持，我们再一次受限制了

 

 

alter table 修改表为压缩，只影响后边的插入和更新操作，以前的数据不进行操作。
alter table move会将所有的数据进行压缩。
alter table ... nocompress;  --取消表压缩，但是已经压缩的数据是不会自己解压缩的，新数据将会使用Uncompressed

column store compress for query high  是默认仓库级压缩，提供好的性能和压缩级别，但是混合列只能在exadata storage上   
query low适合读性能特别差的系统，他比query high要快

column store compress for archive low 是默认的归档压缩模式，，，提供了高级别压缩和理想的不频繁的数据访问，archive high适合和少数据访问的情况

DBMS_COMPRESSION包帮助进行重要表的特殊压缩方式实现期望的压缩级别

CREATE TABLE sales_history ... ROW STORE COMPRESS;   --此语句默认使用的basic table 压缩

 

手工测试一下，没有数据库机，就把basic compress和row advanced compress测试一下，看一下效果
SQL> select  segment_name,bytes/1024/1024 from user_segments;

SEGMENT_NAME                                                                                                                     BYTES/1024/1024
-------------------------------------------------------------------------------------------------------------------------------- ---------------
TEST                                                                                                                                       .0625
AA                                                                                                                                           192

SQL>
SQL>
SQL> create table aa_bas row store compress basic as select * from aa;

Table created.

SQL> create table aa_adv row store compress advanced as select * from aa;

Table created.

SQL>  select  segment_name,bytes/1024/1024 from user_segments;

SEGMENT_NAME                                                                                                                     BYTES/1024/1024
-------------------------------------------------------------------------------------------------------------------------------- ---------------
TEST                                                                                                                                       .0625
AA                                                                                                                                           192
AA_BAS                                                                                                                                        47
AA_ADV                                                                                                                                        53

 

 

--添加几条在exadata中执行的命令，以供参考：

SQL> create table a_ql compress for query low as select * from a;


Table created.

SQL> create table a_qh compress for query high as select * from a;


Table created.

SQL> create table a_al compress for archive low as select * from a;


Table created.


SQL> create table a_ah compress for archive high as select * from a;


Table created.

SQL>  create table a_basic compress for oltp as select * from a;


Table created.

SQL> create table a_basic compress   as select * from a;


Table created.

SQL> select owner,table_name,compress_for from dba_tables
  2  where compression='ENABLED'
  3  ;


OWNER                          TABLE_NAME                     COMPRESS_FOR
------------------------------ ------------------------------ ------------
SYSMAN                         MGMT_METRICS                   OLTP
SYSMAN                         MGMT_POLICIES                  OLTP
TEST                           A_QH                           QUERY HIGH
TEST                           A_QL                           QUERY LOW
TEST                           A_AL                           ARCHIVE LOW
TEST                           A_AH                           ARCHIVE HIGH
TEST                           A_OLTP                         OLTP
TEST                           A_BASIC                        BASIC


8 rows selected.


SQL>