-- Create table
create table CRM.PROD_FEA compress
partition by list (REGION)
(
  partition P_FZ_591 values (2, 11, 12, 13, 14, 15, 16, 17, 18, 19) compress
    tablespace TBS_BILL
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 16K
      next 1M
      minextents 1
      maxextents unlimited
      pctincrease 0
    ),
  partition P_XM_592 values (3, 20, 21, 91, 93) compress
    tablespace TBS_BILL
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 16K
      next 1M
      minextents 1
      maxextents unlimited
      pctincrease 0
    ),
  partition P_ND_593 values (4, 22, 23, 24, 25, 26, 27, 28, 29, 30) compress
,
  partition P_PT_594 values (5, 31, 32, 81, 82) compress
,
  partition P_QZ_595 values (6, 33, 34, 35, 36, 37, 38, 39, 40, 80, 88) compress
,
  partition P_ZZ_596 values (7, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 90) compress
,
  partition P_LY_597 values (8, 51, 52, 53, 54, 55, 56, 57) compress
,
  partition P_SM_598 values (9, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68) compress
,
  partition P_NP_599 values (10, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78) compress
,
  partition P_590 values (default) compress
) as select * from crm.prod_fea@lk_crm1
;

create index CRM.INX_PROD_FEA_02 on CRM.PROD_FEA (PROD_ID) LOCAL COMPRESS PARALLEL 8;

create index CRM.INX_PROD_FEA_03 on CRM.PROD_FEA (FEA_SPEC_ID) LOCAL COMPRESS PARALLEL 8;

create index CRM.INX_PROD_FEA_05 on CRM.PROD_FEA (PARAM1) LOCAL COMPRESS PARALLEL 8;

create index CRM.INX_PROD_FEA_06 on CRM.PROD_FEA (REAL_MODIFY_DATE) LOCAL COMPRESS PARALLEL 8;

create index CRM.INX_PROD_FEA_CREATE_DATE on CRM.PROD_FEA (CREATE_DATE) LOCAL COMPRESS PARALLEL 8;

create index CRM.INX_PROD_FEA_MODIFY_DATE on CRM.PROD_FEA (MODIFY_DATE) LOCAL COMPRESS PARALLEL 8;

create index CRM.INX_PROD_FEA_REGION on CRM.PROD_FEA (REGION) LOCAL COMPRESS PARALLEL 8;

测试结果:

INX_PROD_FEA_02 索引:

压缩后: 8.6g

非压缩:12.9g

压缩后大小/压缩前=压缩率:67%

某表1:

压缩前:431.2g

压缩后:166.2g

表2:

压缩前:151gb

压缩后:98.2gb

表3

压缩前142.3gb

压缩后72.3gb

1.表压缩与lob字段的压缩没有关系

2.表压缩是支持的.

在建立表与子分区时候每个表与子分区都带上compress选项即可

如果是insert 操作,那么必须是append hint才支持压缩,否则即使有上面参数也没效果

压缩比根据内容差异大,大约(2:1)

3.index也有压缩选项:同表压缩

4.对查询性能没有什么影响

5.对DML操作可能偏慢与消耗cpu

6.表与index压缩适合静态的或只有批量的insert的表,如历史表,归档备份表.

以上结果将在1.0库迁移中继续使用与验证,对于CRM2.0历史数据,建议考虑采用.

据说mysql压缩比更高,这个留待mysql技术研究了.

ITPub博客

oracle 表压缩中分区表的测试

最新文章