oracle高度压缩表,你的压缩表Oracle Table Compress真的有效吗?

有个数据仓库的环境,因数据量大使用的都是压缩表,但感觉仍然每张表很大,奉命查查呗,

select owner,segment_name,tablespace_name, bytes/1024/1024 size_m from dba_segments wher

e segment_name like 'FACT_PV_ALL_20080802';

OWNER    SEGMENT_NAME         TABLESPACE_NAME                    SIZE_M

-------- -------------------- ------------------------------ ----------

DW       FACT_PV_ALL_20080802 TS_DW_DATA1                         33456

一张表就有34G,的确不小

select owner,table_name,COMPRESSION from dba_tables where owner='DW' and table_name='FAC

T_PV_ALL_20080802';

OWNER    TABLE_NAME                     COMPRESS

-------- ------------------------------ --------

DW       FACT_PV_ALL_20080802           ENABLED

select dbms_metadata.get_ddl('TABLE','FACT_PV_ALL_20080802','DW') from dual;

DBMS_METADATA.GET_DDL('TABLE','FACT_PV_ALL_20080802','DW')

--------------------------------------------------------------------------------

CREATE TABLE "DW"."FACT_PV_ALL_20080802"

(    "TRANS_ID" NUMBER(12,0),

"ADZONE_ID" NUMBER(12,0),

"SITE_ID" NUMBER(12,0),

"CHANNEL_ID" VARCHAR2(12),

"ADZONE_CAT_ID" VARCHAR2(12),

"PRODUCT_TYPE_ID" VARCHAR2(4),

"ADZONE_LEVEL_ID" NUMBER(4,0),

"SITE_TYPE_ID" VARCHAR2(10),

"ADZONE_URL" VARCHAR2(1024),

"SITE_NAME" VARCHAR2(64),

"SOURCE_TYPE_ID" VARCHAR2(10),

"MEMBER_ID" NUMBER(12,0),

"ADBOARD_ID" NUMBER(12,0),

"AD_ID" NUMBER(12,0),

"ADZONE_PV" NUMBER(12,0)

) PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS LOGGING

STORAGE(INITIAL 8388608 NEXT 8388608 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

TABLESPACE "TS_DW_DATA1"

表面上看都是已经启用了compress属性的,但我再执行alter table dw.FACT_PV_ALL_20080802 move compress后,发现只有11G了,占用空间只有原来的1/3了,原来他们是先把空表创建好,创建是加了compress,然后用insert语句导入数据的,难怪呢.Oracle 11g版本以前需要通过批量导入数据才能实现table compress

知识点:

在ORACLE 11G以前的版本中,需要通过批量导入数据才能实现table compress

1.alter table move

2.create table as select

3.insert /*+ APPEND */

4.direct path sqlldr

在Oracle11g版本中table compress已经有了很大改变