[20181127]12c Advanced Index Compression 2.txt

[20181127]12c Advanced Index Compression 2.txt --//重复链接测试: https://richardfoote.wordpress.com/2014/10/09/index-compression-part-vi-12c-index-advanced-compression-block-dumps-tumble-and-twirl/ 1.环境: SCOTT@test01p> @ ver1 PORT_STRING          VERSION    BANNER                                                                       CON_ID -------------------- ---------- ---------------------------------------------------------------------------- ------ IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production      0 SCOTT@test01p> create table bowie (id number, code number, name varchar2(30)); Table created. SCOTT@test01p> insert into bowie select rownum, rownum, 'ZIGGY STARDUST' from dual connect by level <= 1000000; 1000000 rows created. --//修改大部分数据导致出现重复值. SCOTT@test01p> update bowie set code = 42 where id between 250000 and 499999; 250000 rows updated. SCOTT@test01p> commit ; Commit complete. SCOTT@test01p> create index bowie_code_i on bowie(code); Index created. SCOTT@test01p> alter index bowie_code_i rebuild compress advanced low; Index altered. SCOTT@test01p> select index_name, leaf_blocks, compression from user_indexes where table_name='BOWIE'; INDEX_NAME           LEAF_BLOCKS COMPRESSION -------------------- ----------- ------------- BOWIE_CODE_I                2054 ADVANCED LOW --//前面已经测试过,采用compress advanced low;后,比原来的普通前缀压缩更小.简单探究Advanced Index Compression的索引结构. 2.测试: SCOTT@test01p> select object_id,data_object_id,object_name from user_objects where object_name='BOWIE_CODE_I';  OBJECT_ID DATA_OBJECT_ID OBJECT_NAME ---------- -------------- --------------------      73219          73223 BOWIE_CODE_I SCOTT@test01p> @ treedump BOWIE_CODE_I old   1: select object_id from user_objects where object_name = upper('&&1') and object_type = 'INDEX' new   1: select object_id from user_objects where object_name = upper('BOWIE_CODE_I') and object_type = 'INDEX'  OBJECT_ID ----------      73219 old   1: alter session set events 'immediate trace name treedump level &m_index_id' new   1: alter session set events 'immediate trace name treedump level      73219' Session altered.   --//检查转储文件. ----- begin tree dump branch: 0x2c0113b 46141755 (0: nrow: 4, level: 2)    branch: 0x2c013b4 46142388 (-1: nrow: 544, level: 1)       leaf: 0x2c0113c 46141756 (-1: row:618.618 avs:820 Acmp:42.1.2)       leaf: 0x2c0113d 46141757 (0: row:651.651 avs:826 Acmp:1.1.2)       leaf: 0x2c0113e 46141758 (1: row:651.651 avs:826 Acmp:1.1.2)       leaf: 0x2c0113f 46141759 (2: row:651.651 avs:826 Acmp:1.1.2)       leaf: 0x2c01140 46141760 (3: row:651.651 avs:826 Acmp:1.1.2)  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~       leaf: 0x2c01141 46141761 (4: row:651.651 avs:826 Acmp:1.1.2) ...       leaf: 0x2c01310 46142224 (382: row:651.651 avs:826 Acmp:1.1.2)       leaf: 0x2c01311 46142225 (383: row:488.488 avs:828 Acmp:0.0.2)  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~       leaf: 0x2c01312 46142226 (384: row:479.479 avs:816 Acmp:0.0.2) --//你可以发现下划线相关数据块,块中索引键值很多.选择其中1块转储看看(dba=0x2c0113e 46141758) --//46141758= alter system dump datafile 11 block 4414 --//46142225= alter system dump datafile 11 block 4881 SCOTT@test01p> alter system dump datafile 11 block 4414; System altered. SCOTT@test01p> alter system dump datafile 11 block 4881; System altered. 3.分析转储文件: Block header dump:  0x02c0113e  Object id on Block? Y  seg/obj: 0x11e07  csc:  0x0000000000181654  itc: 2  flg: E  typ: 2 - INDEX      brn: 0  bdba: 0x2c01138 ver: 0x01 opc: 0      inc: 0  exflg: 0    Itl           Xid                  Uba         Flag  Lck        Scn/Fsc 0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000 0x02   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn  0x0000000000181654 Leaf block dump =============== header address 615067748=0x24a93064 kdxcolev 0 KDXCOLEV Flags = - - - kdxcolok 0 kdxcoopc 0xa0: opcode=0: iot flags=-C- is converted=Y kdxconco 2 kdxcosdc 0 kdxconro 651 kdxcofbo 1346=0x542 kdxcofeo 2172=0x87c kdxcoavs 826 kdxlespl 0 kdxlende 0 kdxlenxt 46141759=0x2c0113f kdxleprv 46141757=0x2c0113d kdxledsz 0 kdxlebksz 8036 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ kdxlepnro 1 kdxlepnco 1 (Adaptive) prefix row#0[8031] flag: -P-----, lock: 0, len=5 col 0; len 2; (2):  c1 2b prc 651 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ row#0[8022] flag: -------, lock: 0, len=9 col 0; len 6; (6):  02 c0 04 87 00 11 psno 0 row#1[8013] flag: -------, lock: 0, len=9 col 0; len 6; (6):  02 c0 04 87 00 12 psno 0 ... row#648[2190] flag: -------, lock: 0, len=9 col 0; len 6; (6):  02 c0 04 89 00 b5 psno 0 row#649[2181] flag: -------, lock: 0, len=9 col 0; len 6; (6):  02 c0 04 89 00 b6 psno 0 row#650[2172] flag: -------, lock: 0, len=9 col 0; len 6; (6):  02 c0 04 89 00 b7 psno 0 ----- end of leaf block Logical dump ----- ----- end of leaf block dump ----- End dump data blocks tsn: 5 file#: 11 minblk 4414 maxblk 4414 kdxlepnro 1 kdxlepnco 1 (Adaptive) prefix row#0[8031] flag: -P-----, lock: 0, len=5 col 0; len 2; (2):  c1 2b prc 651 --//引用:https://richardfoote.wordpress.com/2014/10/09/index-compression-part-vi-12c-index-advanced-compression-block-dumps-tumble-and-twirl/ The red section is a portion of the index header that determines the number of rows in the prefix table of the index (kdxlepnro 1). The prefix table basically lists all the distinct column values in the leaf blocks that are to be compressed. The value 1 denotes there is actually only just the 1 distinct column value in this specific leaf block (i.e. all index entries have the same indexed value). This section also denotes how many of the indexed columns are to be compressed (kdxlepnco 1). As this index only has the one column, it also has a value of 1. Note this value can potentially be anything between 0 (no columns compressed) up to the number of columns in the index.  The (Adaptive) reference tells us that Index Advanced Compression has been used and that the values here can change from leaf block to leaf block depending on the data characteristics of the index entries within each leaf block (a dump of a basic compressed index will not have the "Adaptive" reference). The green section is the compression prefix table and details all the unique combinations of index entries to be compressed within the leaf block. As all indexed values are the same in this index (value 42, internally represented as c1 2b hex), the prefix table only has the one row. prc 651 denotes that all 651 index entries in this leaf block have this specific indexed value. Next follows all the actual index entries, which now only consist of the rowid (the 6 byte col 0 column) as they all reference psno 0, which is the unique row id of the only row within the prefix table (row#0). So rather than storing the indexed value 651 times, we can just store the index value (42) just the once within the prefix table and simply reference it from within the actual index entries. This is why index compression can save us storage, storing something once within a leaf block rather than multiple times. If we now look at a partial block dump of another index leaf block within the index, that consists of many differing (basically unique) index entries: Block header dump:  0x02c01311  Object id on Block? Y  seg/obj: 0x11e07  csc:  0x0000000000181654  itc: 2  flg: E  typ: 2 - INDEX      brn: 0  bdba: 0x2c01300 ver: 0x01 opc: 0      inc: 0  exflg: 0    Itl           Xid                  Uba         Flag  Lck        Scn/Fsc 0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000 0x02   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn  0x0000000000181654 Leaf block dump =============== header address 615067748=0x24a93064 kdxcolev 0 KDXCOLEV Flags = - - - kdxcolok 0 kdxcoopc 0xa0: opcode=0: iot flags=-C- is converted=Y kdxconco 2 kdxcosdc 0 kdxconro 488 kdxcofbo 1016=0x3f8 kdxcofeo 1844=0x734 kdxcoavs 828 kdxlespl 0 kdxlende 0 kdxlenxt 46142226=0x2c01312 kdxleprv 46142224=0x2c01310 kdxledsz 0 kdxlebksz 8036 kdxlepnro 0 kdxlepnco 0 (Adaptive) row#0[8024] flag: -------, lock: 0, len=12 col 0; len 2; (2):  c1 2b col 1; len 6; (6):  02 c0 08 ff 00 97 --//还有小部分数据是42的键值. row#1[8012] flag: -------, lock: 0, len=12 col 0; len 2; (2):  c1 2b col 1; len 6; (6):  02 c0 08 ff 00 98 row#2[8000] flag: -------, lock: 0, len=12 col 0; len 2; (2):  c1 2b col 1; len 6; (6):  02 c0 08 ff 00 99 row#3[7988] flag: -------, lock: 0, len=12 col 0; len 2; (2):  c1 2b col 1; len 6; (6):  02 c0 08 ff 00 9a ... row#90[6944] flag: -------, lock: 0, len=12 col 0; len 2; (2):  c1 2b col 1; len 6; (6):  02 c0 08 ff 00 f1 row#91[6932] flag: -------, lock: 0, len=12 col 0; len 2; (2):  c1 2c col 1; len 6; (6):  02 c0 00 ae 00 2a ... row#481[1922] flag: -------, lock: 0, len=13 col 0; len 3; (3):  c2 05 22 col 1; len 6; (6):  02 c0 00 af 00 a6 row#482[1909] flag: -------, lock: 0, len=13 col 0; len 3; (3):  c2 05 23 col 1; len 6; (6):  02 c0 00 af 00 a7 row#483[1896] flag: -------, lock: 0, len=13 col 0; len 3; (3):  c2 05 24 col 1; len 6; (6):  02 c0 00 af 00 a8 row#484[1883] flag: -------, lock: 0, len=13 col 0; len 3; (3):  c2 05 25 col 1; len 6; (6):  02 c0 00 af 00 a9 row#485[1870] flag: -------, lock: 0, len=13 col 0; len 3; (3):  c2 05 26 col 1; len 6; (6):  02 c0 00 af 00 aa row#486[1857] flag: -------, lock: 0, len=13 col 0; len 3; (3):  c2 05 27 col 1; len 6; (6):  02 c0 00 af 00 ab row#487[1844] flag: -------, lock: 0, len=13 col 0; len 3; (3):  c2 05 28 col 1; len 6; (6):  02 c0 00 af 00 ac ----- end of leaf block Logical dump ----- ----- end of leaf block dump ----- End dump data blocks tsn: 5 file#: 11 minblk 4881 maxblk 4881 We notice that in the red section, both kdxlepnro 0 and kdxlepnco 0 (Adaptive) have a value of 0, meaning we have no rows and no columns within the prefix table. As such, we have no prefix table at all here and that this leaf block has simply not been compressed. If we look at the actual index entries, they all have an additional column now in blue, that being the actual indexed value as all the index values in this leaf block are different from each other. Without some form of index entry duplication, there would be no benefit from compression and Index Advanced Compression has automatically determined this and not bothered to compress this leaf block. An attempt to compress this block would have actually increased the necessary overall storage for these index entries, due to the additional overheads associated with the prefix table (note it has an additional 2 byes of overhead per row within the prefix table). I'll next look at an example of a multi-column index and how Index Advanced Compression handles which columns in the index to compress. --//注:下面将探究多列采用Advanced index Compression 的情况.