Hybrid Columnar Compression混合列压缩是Exadata数据库一体机的核心功能之一，与普通的高级压缩特性(advanced compression)不同，Hybrid columnar compression (HCC) 仅仅在Exadata平台上可用。使用HCC的情况下数据压缩存放在CU(compression unit压缩单位中)，一个CU单位包括多个数据库块，这是出于单数据块不利于以列值压缩算法的考量所决定的，当一个CU包含多个block时可以实现较优的列值压缩算法。 同时对于普通的INSERT/UPDATE操作，需要造成对行级数据的压缩降级，即在经历UPDATE/INSERT后原本HCC压缩的行可能变成普通高级压缩的水平。 hybrid columnar compression与数据仓库行为的批量初始化导入(bulk initial load)配合，直接路径导入(direct load)例如ALTER TABLE MOVE, IMPDP或直接路径插入(append INSERT)，使用HCC的前提是这些数据将不会被频繁修改或从不被修改。 当你更新混合列压缩启动的表中的数据行时，相关整个的压缩单位CU中的数据将被锁住。 被更新的这些数据将不得不从原HCC压缩级别降级到例如无压缩或for OLTP压缩的水准。 我们来看以下例子：
SQL*Plus: Release 11.2.0.2.0 Production on Wed Sep 12 06:14:53 2012Copyright (c) 1982, 2010, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand Real Application Testing optionsSQL> grant dba to scott;Grant succeeded.SQL> conn scott/oracleConnected.SQL>SQL> create table hcc_maclean tablespace users compress for query high as select * from dba_objects;Table created. 1* select rowid,owner,object_name,dbms_rowid.rowid_block_number(rowid) from hcc_maclean where owner='MACLEAN'SQL> /ROWID OWNER OBJECT_NAME DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)------------------------------ ------------------------------ -------------------- ------------------------------------AAAThuAAEAAAHTJAOI MACLEAN SALES 29897AAAThuAAEAAAHTJAOJ MACLEAN MYCUSTOMERS 29897AAAThuAAEAAAHTJAOK MACLEAN MYCUST_ARCHIVE 29897AAAThuAAEAAAHTJAOL MACLEAN MYCUST_QUERY 29897AAAThuAAEAAAHTJAOh MACLEAN COMPRESS_QUERY 29897AAAThuAAEAAAHTJAOi MACLEAN UNCOMPRESS 29897AAAThuAAEAAAHTJAOj MACLEAN CHAINED_ROWS 29897AAAThuAAEAAAHTJAOk MACLEAN COMPRESS_QUERY1 298978 rows selected.select dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from hcc_maclean where owner='MACLEAN';session A:update hcc_maclean set OBJECT_NAME=OBJECT_NAME||'DBM' where rowid='AAAThuAAEAAAHTJAOI';session B:update hcc_maclean set OBJECT_NAME=OBJECT_NAME||'DBM' where rowid='AAAThuAAEAAAHTJAOJ';SQL> select sid,wait_event_text,BLOCKER_SID from v$wait_chains; SID WAIT_EVENT_TEXT BLOCKER_SID---------- ---------------------------------------------------------------- ----------- 13 enq: TX - row lock contention 136 136 SQL*Net message from client可以看到session A block B，这验证了HCC压缩后update row所在CU会造成整个CU被锁住的说法SQL> alter system checkpoint;System altered.SQL> /System altered.SQL> alter system dump datafile 4 block 29897 2 ; Block header dump: 0x010074c9 Object id on Block? Y seg/obj: 0x1386e csc: 0x00.1cad7e itc: 3 flg: E typ: 1 - DATA brn: 0 bdba: 0x10074c8 ver: 0x01 opc: 0 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/Fsc0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.001cabfa0x02 0x000a.00a.00000430 0x00c051a7.0169.17 ---- 1 fsc 0x0000.000000000x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000avsp=0x14tosp=0x14 r0_9ir2=0x0 mec_kdbh9ir2=0x0 76543210 shcf_kdbh9ir2=---------- 76543210 flag_9ir2=--R----- Archive compression: Y fcls_9ir2[0]={ }0x16:pti[0] nrow=1 offs=00x1a:pri[0] offs=0x30block_row_dump:tab 0, row 0, @0x30tl: 8016 fb: --H-F--N lb: 0x2 cc: 1 ==>整个CU指向ITL 0x02nrid: 0x010074ca.0col 0: [8004]Compression level: 02 (Query High) Length of CU row: 8004kdzhrh: ------PC CBLK: 1 Start Slot: 00 NUMP: 01 PNUM: 00 POFF: 7984 PRID: 0x010074ca.0CU header:CU version: 0 CU magic number: 0x4b445a30CU checksum: 0xf8faf86eCU total length: 8694CU flags: NC-U-CRD-OPncols: 15nrows: 995algo: 0CU decomp length: 8487 len/value length: 100111row pieces per row: 1num deleted rows: 1deleted rows: 904,START_CU: 我们可以使用如下方式衡量row的压缩情况： SQL> select DBMS_COMPRESSION.GET_COMPRESSION_TYPE('SCOTT','HCC_MACLEAN','AAAThuAAEAAAHTJAOk') from dual;DBMS_COMPRESSION.GET_COMPRESSION_TYPE('SCOTT','HCC_MACLEAN','AAATHUAAEAAAHTJAOK'-------------------------------------------------------------------------------- 4 COMP_NOCOMPRESS CONSTANT NUMBER := 1; COMP_FOR_OLTP CONSTANT NUMBER := 2; COMP_FOR_QUERY_HIGH CONSTANT NUMBER := 4; COMP_FOR_QUERY_LOW CONSTANT NUMBER := 8; COMP_FOR_ARCHIVE_HIGH CONSTANT NUMBER := 16; COMP_FOR_ARCHIVE_LOW CONSTANT NUMBER := 32; COMP_RATIO_MINROWS CONSTANT NUMBER := 1000000; COMP_RATIO_ALLROWS CONSTANT NUMBER := -1; 上表列出了压缩类型的常数值，例如COMP_FOR_QUERY_HIGH是4，COMP_FOR_QUERY_LOW 是8 这里我们从上述查询GET_COMPRESSION_TYPE指定rowid的情况下得到的是4说明该列以COMP_FOR_QUERY_HIGH形式压缩： SQL> update hcc_maclean set OBJECT_NAME=OBJECT_NAME||'DBM' where owner='MACLEAN';8 rows updated.SQL> commit;Commit complete.SQL> select DBMS_COMPRESSION.GET_COMPRESSION_TYPE('SCOTT','HCC_MACLEAN',rowid) from HCC_MACLEAN where owner='MACLEAN';DBMS_COMPRESSION.GET_COMPRESSION_TYPE('SCOTT','HCC_MACLEAN',ROWID)------------------------------------------------------------------ 1 1 1 1 1 1 1 18 rows selected.以上更新一定量数据后可以看到COMPRESSION_TYPE由COMP_FOR_QUERY_HIGH降级为COMP_NOCOMPRESS，这说明这张表虽然compress for query high但部分数据在更新后实际不再被压缩。 在11g中这些非压缩态复萌的数据行不会自动升级成HCC状态。必要的时候手动作 ALTER TABLE MOVE或在线重定义以便将非压缩态的数据转换回HCC状态。 SQL> ALTER TABLE hcc_MACLEAN move COMPRESS FOR ARCHIVE HIGH;Table altered.SQL> select DBMS_COMPRESSION.GET_COMPRESSION_TYPE('SCOTT','HCC_MACLEAN',rowid) from HCC_MACLEAN where owner='MACLEAN';DBMS_COMPRESSION.GET_COMPRESSION_TYPE('SCOTT','HCC_MACLEAN',ROWID)------------------------------------------------------------------ 16 16 16 16 16 16 16 168 rows selected.