oracle压缩机制_oracle中表数据怎么压缩

1、表压缩

表压缩级别有两种：COMPRESS FOR OLTP(HIGH)和COMPRESS BASIC

CREATE TABLE "RBC"."EVENT_USAGE_C_10570_TEST" *****;

CREATE TABLE "RBC"."EVENT_USAGE_C_10570_TEST_COMP" ***** COMPRESS BASIC;

CREATE TABLE "RBC"."EVENT_USAGE_C_10570_TEST_COMP2" ***** COMPRESS FOR OLTP;

（1）相同的表结构、记录数，COMPRESS FOR OLTP的压缩率最高，能达到20%左右

select table_name,compression,compress_for from dba_tables where table_name like 'EVENT_USAGE_C_10570_TEST%';

（2）对不同压缩级别的表进行全表扫描，COMPRESS FOR OLTP全表扫描最快，能达到普通表的20%的时间

（3）创建索引的速度也是compress for oltp更快；但是创建的索引默认都没压缩，大小相同

create index rbc.idx_EVENT_USAGE_C_10570_TEST on rbc.EVENT_USAGE_C_10570_TEST(EVENT_INST_ID) ;

create index rbc.idx_EVENT_USAGE_C_10570_TEST_COMP on rbc.EVENT_USAGE_C_10570_TEST_COMP(EVENT_INST_ID) ;

create index rbc.idx_EVENT_USAGE_C_10570_TEST_COMP2 on rbc.EVENT_USAGE_C_10570_TEST_COMP2(EVENT_INST_ID) ;

（4）分区表压缩

CREATE TABLE MODEL_RESULT(ID number NOT NULL,NAME VARCHAR(128) NOT NULL) compress for oltp PARTITION BY list(ID) (PARTITION P1 VALUES (1000),PARTITION P2 VALUES (2000),partition p3 values(default));

select table_name,partition_name,compression,compress_for from dba_tab_partitions where table_name = 'MODEL_RESULT';

select table_name,compression,compress_for from dba_tables where table_name='MODEL_RESULT'; --不显示

2、索引压缩

（1）创建compress advanced high、low的索引，与普通索引比较大小（low和不压缩差不多大，high约为不压缩的一半）

create index rbc.idx_EVENT_USAGE_C_10570_TEST on rbc.EVENT_USAGE_C_10570_TEST(EVENT_INST_ID) ;

create index rbc.idx_EVENT_USAGE_C_10570_TEST_COMP on rbc.EVENT_USAGE_C_10570_TEST_COMP(EVENT_INST_ID) compress advanced low;

create index rbc.idx_EVENT_USAGE_C_10570_TEST_COMP2 on rbc.EVENT_USAGE_C_10570_TEST_COMP2(EVENT_INST_ID) compress advanced high;

（2）若要索引继承表的压缩属性，可以将db_index_compression_inheritance参数设为table

create index rbc.idx_EVENT_USAGE_C_10570_TEST on rbc.EVENT_USAGE_C_10570_TEST(EVENT_INST_ID) ;

create index rbc.idx_EVENT_USAGE_C_10570_TEST_COMP on rbc.EVENT_USAGE_C_10570_TEST_COMP(EVENT_INST_ID) ;

create index rbc.idx_EVENT_USAGE_C_10570_TEST_COMP2 on rbc.EVENT_USAGE_C_10570_TEST_COMP2(EVENT_INST_ID) ;

经测试可以看出，在db_index_compression_inheritance为table的情况下，compress basic的表创建的索引不压缩；compress for oltp的表创建的索引为advanced low压缩级别

（3）分区索引压缩

create index idx_MODEL_RESULT on MODEL_RESULT(id) local compress ADVANCED HIGH ;

SELECT partition_name,compression from dba_ind_partitions where index_name='IDX_MODEL_RESULT';

SELECT INDEX_NAME,compression from dba_indexes where index_name='IDX_MODEL_RESULT';

3、表空间压缩

表空间也可以进行压缩，建在表空间里的表和索引也会根据表空间的压缩属性自动进行压缩

创建压缩表空间：

create tablespace test_com datafile '/oradata/test_comp.dbf' size 100m default compress for oltp;

SELECT DEF_TAB_COMPRESSION,COMPRESS_FOR,DEF_INDEX_COMPRESSION ,INDEX_COMPRESS_FOR from dba_tablespaces where tablespace_name='TEST_COM';

在压缩表空间中建表,则表默认为压缩

建索引（在db_index_compression_inheritance为默认none的情况下，即使建在压缩表空间，索引也不会压缩）：

若将db_index_compression_inheritance改为tablespace，则建到压缩表空间的索引默认为压缩:

4、其他压缩

rman、expdp、lob、dg的redo压缩这里简单说下

（1）securefile LOB压缩：

CREATE TABLE t1 (a CLOB)

LOB(a) STORE AS SECUREFILE (

COMPRESS HIGH

);

SELECT COMPRESSION FROM DBA_LOBS WHERE COLUMN_NAME='A';

（2）expdp压缩

expdp COMPRESSION={ALL | DATA_ONLY | [METADATA_ONLY] | NONE}

impdp时不需要解压缩

dumpfile压缩到75%左右

（3）rman压缩

RMAN> configure compression algorithm 'MEDIUM';

RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE

PLUS ARCHIVELOG;

（4）dg redo传输压缩

log_archive_dest_2='SERVICE=dgtests ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dgtests COMPRESSION=ENABLE'

减少生产和容灾间redo传输带宽