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;
