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 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> grant dba to scott;
Grant succeeded.
SQL> conn scott/oracle
Connected.
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 29897
AAAThuAAEAAAHTJAOJ MACLEAN MYCUSTOMERS 29897
AAAThuAAEAAAHTJAOK MACLEAN MYCUST_ARCHIVE 29897
AAAThuAAEAAAHTJAOL MACLEAN MYCUST_QUERY 29897
AAAThuAAEAAAHTJAOh MACLEAN COMPRESS_QUERY 29897
AAAThuAAEAAAHTJAOi MACLEAN UNCOMPRESS 29897
AAAThuAAEAAAHTJAOj MACLEAN CHAINED_ROWS 29897
AAAThuAAEAAAHTJAOk MACLEAN COMPRESS_QUERY1 29897
8 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/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.001cabfa
0x02 0x000a.00a.00000430 0x00c051a7.0169.17 ---- 1 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
avsp=0x14
tosp=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=0
0x1a:pri[0] offs=0x30
block_row_dump:
tab 0, row 0, @0x30
tl: 8016 fb: --H-F--N lb: 0x2 cc: 1 ==>整个CU指向ITL 0x02
nrid: 0x010074ca.0
col 0: [8004]
Compression level: 02 (Query High)
Length of CU row: 8004
kdzhrh: ------PC CBLK: 1 Start Slot: 00
NUMP: 01
PNUM: 00 POFF: 7984 PRID: 0x010074ca.0
CU header:
CU version: 0 CU magic number: 0x4b445a30
CU checksum: 0xf8faf86e
CU total length: 8694
CU flags: NC-U-CRD-OP
ncols: 15
nrows: 995
algo: 0
CU decomp length: 8487 len/value length: 100111
row pieces per row: 1
num deleted rows: 1
deleted 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
1
8 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
16
8 rows selected.
Leave a Reply