[20181127]12c Advanced Index Compression 2.txt

[20181127]12c Advanced Index Compression 2.txt

--//重複連結測試:

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

--//引用:

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 的情況.