-- 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 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博客 ” ,链接:http://blog.itpub.net/9363164/viewspace-1356651/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9363164/viewspace-1356651/