Exadata上的HCC测试（EHCC）—3—分区表的压缩

Exadata上的HCC测试（EHCC）—2—：DBMS_COMPRESSION.GET_COMPRESSION_RATIO
Exadata上的HCC测试（EHCC）——1

在分区表内部，不同分区可以不同的压缩类型：建立测试表
（注意，还可以按照列选择不同分区，具体参见文档的语法）

CREATE TABLE LUNAR_PAR (
id varchar2(15),
row_num varchar2(15),
name varchar2(50),
DEPT_ID varchar2(15),
STU_ID number,
amount number,
FIRST_DATE date,
END_DATE date)
PARTITION BY RANGE (FIRST_DATE)
(
 partition lunar1 VALUES LESS THAN(TO_DATE('1993-07-01', 'yyyy-mm-dd')) COMPRESS FOR archive high,
 partition lunar2 VALUES LESS THAN(TO_DATE('1998-01-01', 'yyyy-mm-dd')) COMPRESS FOR archive low,
 partition lunar3 VALUES LESS THAN(TO_DATE('2003-12-01', 'yyyy-mm-dd')) COMPRESS FOR query high,
 partition lunar4 VALUES LESS THAN(TO_DATE('2009-07-01', 'yyyy-mm-dd')) COMPRESS FOR query low,
 partition lunar5 VALUES LESS THAN(TO_DATE('2014-12-31', 'yyyy-mm-dd')) nocompress
);

对比插入普通的表（Basic Compress）的时间和插入分区表（不同分区类型）的时间：

insert /*+ append parallel(8)*/ into lunar_comp_basic nologging select * from LUNARTEST;
commit;                                                                   

LUNAR@mylove>
LUNAR@mylove>insert /*+ append parallel(8)*/ into lunar_comp_basic nologging select * from LUNARTEST;
commit;   

20000000 rows created.

Elapsed: 00:00:35.82
LUNAR@mylove>
Commit complete.

Elapsed: 00:00:00.03
LUNAR@mylove>

LUNAR@mylove>insert /*+ append parallel(8)*/ into LUNAR_PAR nologging select * from LUNARTEST;
commit;                                                                   

20000000 rows created.

Elapsed: 00:01:44.81
LUNAR@mylove>
Commit complete.

Elapsed: 00:00:00.02
LUNAR@mylove>

检查分区表各个分区的分区类型：

LUNAR@mylove>SELECT table_name,partition_name,compression,compress_for FROM USER_TAB_PARTITIONS order by 1,2;

TABLE_NAME                     PARTITION_NAME                 COMPRESS COMPRESS_FOR
------------------------------ ------------------------------ -------- ------------
LUNAR_PAR                      LUNAR1                         ENABLED  ARCHIVE HIGH
LUNAR_PAR                      LUNAR2                         ENABLED  ARCHIVE LOW
LUNAR_PAR                      LUNAR3                         ENABLED  QUERY HIGH
LUNAR_PAR                      LUNAR4                         ENABLED  QUERY LOW
LUNAR_PAR                      LUNAR5                         DISABLED

Elapsed: 00:00:00.05
LUNAR@mylove>

检查每种分区中的记录数据：

LUNAR@mylove>col Compression_type format a50
LUNAR@mylove>SELECT CASE comp_type
  2  WHEN 1 THEN 'No Compression'
  3  WHEN 2 THEN 'Advanced compression level'
  4  WHEN 4 THEN 'Hybrid Columnar Compression for Query High'
  5  WHEN 8 THEN 'Hybrid Columnar Compression for Query Low'
  6  WHEN 16 THEN 'Hybrid Columnar Compression for Archive High'
  7  WHEN 32 THEN 'Hybrid Columnar Compression for Archive Low'
  8  WHEN 64 THEN 'Compressed row'
  9  WHEN 128 THEN 'High compression level for LOB operations'
 10  WHEN 256 THEN 'Medium compression level for LOB operations'
 11  WHEN 512 THEN 'Low compression level for LOB operations'
 12  WHEN 1000 THEN 'Minimum required number of LOBs in the object for which LOB compression ratio is to be estimated'
 13  WHEN 4096 THEN 'Basic compression level'
 14  WHEN 5000 THEN 'Maximum number of LOBs used to compute the LOB compression ratio'
 15  WHEN 1000000 THEN 'Minimum required number of rows in the object for which HCC ratio is to be estimated'
 16  WHEN -1 THEN 'To indicate the use of all the rows in the object to estimate HCC ratio'
 17  WHEN 1 THEN 'Identifies the object whose compression ratio is estimated as of type table'
 18  ELSE 'Unknown Compression Type'
 19  END AS Compression_type,
 20  n as num_rows
 21  FROM (SELECT comp_type,
 22  Count(*) n
 23  FROM (SELECT
 24  dbms_compression.Get_compression_type(USER, 'LUNAR3_PAR', ROWID)
 25  AS comp_type
 26  FROM LUNAR3_PAR)
 27  GROUP BY comp_type);

COMPRESSION_TYPE                                     NUM_ROWS
-------------------------------------------------- ----------
No Compression                                          17240
Hybrid Columnar Compression for Archive Low             16450
Hybrid Columnar Compression for Query High              21600
Hybrid Columnar Compression for Query Low               20390
Hybrid Columnar Compression for Archive High            24320

Elapsed: 00:00:08.20
LUNAR@mylove>

还可以按照rowid来查看压缩情况，输出为一个数字，代表压缩类型，具体的含义上面都有了：

select DBMS_COMPRESSION.GET_COMPRESSION_TYPE('LUNAR', 'LUNARTEST', '&rowid') from dual;