你的压缩表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已经有了很大改变