我们为了降低表的大小,一般有以下两个手段
1) 降低表的pctfree值。极端的情况可以是0
2) 采用表的压缩技术,即在表创建的时候显式的指定compress关键词。
让我们看看压缩的效果:
1) 普通方式的创建表142 M
vodka@CRMG>create table wxh_tbd1 as select * from bpm_actinst;
Table created.
vodka@CRMG>@size
142M
2) pctfree为0方式创建128M
vodka@CRMG>create table wxh_tbd2 pctfree 0 as select * from bpm_actinst;
Table created.
vodka@CRMG>@size
128M
3) 增加compress关键字 33M
vodka@CRMG>create table wxh_tbd3 compress as select * from bpm_actinst;
Table created.
vodka@CRMG>@size
33M
4) 增加compress关键字,并且按照重复度较高的字段排序。21M.
vodka@CRMG>create table wxh_tbd4 compress as select * from bpm_actinst order by col2,actor,node_type;
Table created.
vodka@CRMG>@size
21M
默认的启用了compress,表的pctfree属性就是0
vodka@CRMG>vodka@CRMG>vodka@CRMG>@getddl
Enter value for object_type: table
Enter value for object_name: wxh_tbd4
Enter value for owner: vodka
old 1: select dbms_metadata.get_ddl(upper('&object_type'),upper('&object_name'),upper('&owner')) FROM DUAL
new 1: select dbms_metadata.get_ddl(upper('table'),upper('wxh_tbd4'),upper('vodka')) FROM DUAL
DBMS_METADATA.GET_DDL(UPPER('TABLE'),UPPER('WXH_TBD4'),UPPER('VODKA'))
--------------------------------------------------------------------------------
CREATE TABLE "VODKA"."WXH_TBD4"
( "ID" NUMBER(19,0) NOT NULL ENABLE,
"GMT_MODIFIED" DATE,
"MODIFIER" VARCHAR2(32),
"IS_DELETED" CHAR(1) NOT NULL ENABLE,
"VERSION" NUMBER NOT NULL ENABLE,
"PROCINST" NUMBER(19,0),
"SIGN_FLAG" VARCHAR2(32),
"STATE" VARCHAR2(255),
"FLAG" VARCHAR2(32),
"START_TIME" DATE,
"END_TIME" DATE,
"NODE_NAME" VARCHAR2(255),
"NODE_TYPE" VARCHAR2(32),
"OLD_ACTID" NUMBER(19,0),
"SUPER_ACTID" NUMBER(19,0),
"PATH" VARCHAR2(512),
"ACTOR" VARCHAR2(64),
"ORG_ROLE" VARCHAR2(64),
"BAK_ACTOR" VARCHAR2(64),
"TASK_ORGPATH" VARCHAR2(255),
"TASK_TITLE" VARCHAR2(512),
"TASK_LEVEL" VARCHAR2(32),
"TASK_SRC" VARCHAR2(64),
"TASK_TYPE" VARCHAR2(32),
"SERVICE_METHOD" VARCHAR2(32),
"ASSIGN_TIME" DATE,
"RESERVED_TIME" DATE,
"DEADLINE_TIME" DATE,
"TAKEOVER_TIME" DATE,
"APPROVAL_ID" VARCHAR2(64),
"APPROVAL_ORG" VARCHAR2(255),
"APPROVAL_ROLE" VARCHAR2(255),
"APPROVAL_TIME" DATE,
"APPROVAL_NAME" VARCHAR2(255),
"APPROVAL_VOTE" VARCHAR2(64),
"APPROVAL_MEMO" VARCHAR2(4000),
"COL1" DATE,
"COL2" VARCHAR2(255),
"COL3" VARCHAR2(255),
"ARRANGE_TYPE" VARCHAR2(255),
"TASK_NOTE" VARCHAR2(4000),
"NODE_CATEGORY" VARCHAR2(255),
"COL4" VARCHAR2(255),
"COL5" VARCHAR2(255)
) PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS LOGGING
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "VODKA"
压缩表的适用场景及特点
1) 大大减少表存储空间,我们的例子中压缩了近六倍的空间。
2) 比较适合静态表,多见于数据仓库,,比如备份表,或者表上只有delete与insert操作,没有update操作,原因很简单由于pctfree为0,update导致的行链接会大大增加。
3) 压缩表比一般的表在读取数据块的时候耗取更多的CUP,不过11G的文档上宣称,这种限制在11G已经不存在了。在实际应用中需要权衡。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22034023/viewspace-688255/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22034023/viewspace-688255/