关于索引压缩的研究

当单列索引和复合索引中的数据列重复项比较多的时候，可以考虑进行索引压缩。索引压缩可以在某种程度上减小索引所占空间，减小扫描索引时候的I/O，提高查询的性能。
语法：create index index_name on table_name(col1,col2 ….coln) compress n; （n>0）
不输入n的话，默认压缩所有的索引列
索引中的前n项被压缩，称做前缀。

运行环境

SQL> conn / as sysdba已连接。SQL> select * from v$version;BANNER----------------------------------------------------------------Oracle8i Enterprise Edition Release 8.1.6.0.0 - ProductionPL/SQL Release 8.1.6.0.0 - ProductionCORE    8.1.6.0.0       ProductionTNS for 32-bit Windows: Version 8.1.6.0.0 - ProductionNLSRTL Version 3.4.1.0.0 – Production

实验一：索引压缩后占用空间的变化

SQL> drop table test10;Table droppedSQL> create table test10  2  (id1 varchar2(20),  3  id2 varchar2(20),  4  id3 varchar2(20))  5  tablespace test;Table createdSQL> insert into test10 select 'aaaaaaaaaaaaaaaaaaaa','bbbbbbbbbbbbbbbbbbbb',rownum from dba_objects where rownum<10000;9999 rows insertedSQL> insert into test10 select 'bbbbbbbbbbbbbbbbbbbb','aaaaaaaaaaaaaaaaaaaa',rownum from dba_objects where rownum<10000;9999 rows insertedSQL> commit;Commit completeSQL> insert into test10 select 'aaaaaaaaaaaaaaaaaaaa','bbbbbbbbbbbbbbbbbbbb',rownum from dba_objects where rownum<10000;9999 rows insertedSQL> insert into test10 select 'bbbbbbbbbbbbbbbbbbbb','aaaaaaaaaaaaaaaaaaaa',rownum from dba_objects where rownum<10000;9999 rows insertedSQL> commit;Commit completeSQL> create table test11 as select * from test10;Table createdSQL> create index ind_test10 on test10(id1,id2,id3) tablespace test;Index createdSQL> create index ind_test11 on test11(id1,id2,id3) compress 2 tablespace test;Index createdSQL> set serveroutput on SQL> exec show_space('IND_TEST10','I','MYTEST');Free Blocks.............................0Total Blocks............................195Total Bytes.............................1597440Unused Blocks...........................29Unused Bytes............................237568Last Used Ext FileId....................8Last Used Ext BlockId...................2127Last Used Block.........................36PL/SQL procedure successfully completedSQL> exec show_space('IND_TEST11','I','MYTEST');Free Blocks.............................0Total Blocks............................55Total Bytes.............................450560Unused Blocks...........................8Unused Bytes............................65536Last Used Ext FileId....................8Last Used Ext BlockId...................2227Last Used Block.........................12PL/SQL procedure successfully completed

普通索引IND_TEST10所占用的空间：195 – 29 = 166
压缩索引IND_TEST11所占用的空间：55 – 8 = 47
节约的空间百分比：（166 – 47）/ 166 = 71.7%
事实上，压缩索引所能节约的空间百分比大小与压缩索引的前缀字段大小占总字段大小的百分比有关系。前缀字段所占百分比越大，则节约空间越大，压缩效果越明显；反之，则节约空间越小，压缩效果越不明显。
以上实验是在前缀字段的重复项比较多的情况下，压缩索引可以发挥自己的优势。下面看一下如果前缀字段没有重复项这种极限的情况下，压缩索引的情况。

SQL> create table test12   2  (id number)  3  tablespace test;Table createdSQL> insert into test12 select rownum from dba_objects;23725 rows insertedSQL> commit;Commit completeSQL> create table test13 as select * from test12;Table createdSQL> create index ind_test12 on test12(id) tablespace test;Index createdSQL> create index ind_test13 on test13(id) compress tablespace test;Index createdSQL> exec show_space('IND_TEST12','I','MYTEST');Free Blocks.............................0Total Blocks............................55Total Bytes.............................450560Unused Blocks...........................1Unused Bytes............................8192Last Used Ext FileId....................8Last Used Ext BlockId...................2392Last Used Block.........................19PL/SQL procedure successfully completedSQL> exec show_space('IND_TEST13','I','MYTEST');Free Blocks.............................0Total Blocks............................85Total Bytes.............................696320Unused Blocks...........................11Unused Bytes............................90112Last Used Ext FileId....................8Last Used Ext BlockId...................2467Last Used Block.........................19PL/SQL procedure successfully completed

普通索引IND_TEST12所占用的空间：55 – 1 = 54
压缩索引IND_TEST13所占用的空间：85 – 11 = 74
浪费的空间百分比：（74 – 54）/ 54 = 37%
由此可见，使用压缩索引的前提必须是前缀列的重复项比较多，否则会对性能产生更坏的影响。

实验二：压缩索引BLOCK的内部结构
下面用一个比较小的表为例子，看一下压缩索引BLOCK的内部结构

SQL> create table test14 as select * from test10 where 1=0;Table createdSQL> insert into test14 values ('aa','bb','11');1 row insertedSQL> insert into test14 values ('aa','bb','22');1 row insertedSQL> insert into test14 values ('bb','aa','11');1 row insertedSQL> insert into test14 values ('bb','aa','2');1 row insertedSQL> commit;Commit completeSQL> select * from test14;ID1                  ID2                  ID3-------------------- -------------------- --------------------aa                   bb                   11aa                   bb                   22bb                   aa                   11bb                   aa                   2SQL> create index ind_test14 on test14(id1,id2,id3) compress 2;Index createdSQL> exec show_space('IND_TEST14','I','MYTEST');Free Blocks.............................0Total Blocks............................5Total Bytes.............................40960Unused Blocks...........................3Unused Bytes............................24576Last Used Ext FileId....................8Last Used Ext BlockId...................2502Last Used Block.........................2PL/SQL procedure successfully completedSQL> alter system dump datafile 8 block 2503;System alteredStart dump data blocks tsn: 7 file#: 8 minblk 2503 maxblk 2503buffer tsn: 7 rdba: 0x020009c7 (8/2503)scn: 0x0000.24a47653 seq: 0x01 flg: 0x00 tail: 0x76530601frmt: 0x02 chkval: 0x0000 type: 0x06=trans data Block header dump:  0x020009c7 Object id on Block? Y seg/obj: 0x5fba  csc: 0x00.24a47651  itc: 2  flg: -  typ: 2 - INDEX     fsl: 0  fnx: 0x0 ver: 0x01  Itl           Xid                  Uba         Flag  Lck        Scn/Fsc0x01   xid:  0x0000.000.00000000    uba: 0x00000000.0000.00  ----    0  fsc 0x0000.000000000x02   xid:  0x0006.005.000000b9    uba: 0x00000000.0000.00  ----    0  fsc 0x0000.00000000 Leaf block dump===============header address 365784156=0x15cd6c5ckdxcolev 0kdxcolok 0kdxcoopc 0xa0: opcode=0: iot flags=-C- is converted=Ykdxconco 4kdxcosdc 0kdxconro 4kdxcofbo 56=0x38kdxcofeo 7973=0x1f25kdxcoavs 7917kdxlespl 0kdxlende 0kdxlenxt 0=0x0kdxleprv 0=0x0kdxledsz 0kdxlebksz 8036kdxlepnro 2kdxlepnco 2prefix row#0[8028] flag: -P---, lock: 0    //即‘aa’，‘bb’col 0; len 2; (2):  61 61col 1; len 2; (2):  62 62prc 2   //在这个BLOCK中前缀为‘aa’，‘bb’的记录数2prefix row#1[7996] flag: -P---, lock: 0//即‘bb’，‘aa’col 0; len 2; (2):  62 62col 1; len 2; (2):  61 61prc 2   //在这个BLOCK中前缀为‘bb，‘aa’的记录数2row#0[8016] flag: -----, lock: 0col 0; len 2; (2):  31 31col 1; len 6; (6):  02 00 09 c2 00 00psno 0  //此条记录的前缀序号为‘0’，即‘aa’，‘bb’row#1[8004] flag: -----, lock: 0col 0; len 2; (2):  32 32col 1; len 6; (6):  02 00 09 c2 00 01psno 0row#2[7984] flag: -----, lock: 0col 0; len 2; (2):  31 31col 1; len 6; (6):  02 00 09 c2 00 02psno 1//此条记录的前缀序号为‘1’，即‘bb’，‘aa’row#3[7973] flag: -----, lock: 0col 0; len 1; (1):  32col 1; len 6; (6):  02 00 09 c2 00 03psno 1----- end of leaf block dump -----End dump data blocks tsn: 7 file#: 8 minblk 2503 maxblk 2503

通过dump的结果就可以看到压缩索引节约空间的原因了。经过其他的一些实验还发现以下几点：
1， 仅仅该BLOCK中存在该前缀对应的记录，该前缀的说明才会在BLOCK中出现。
2， 当索引中记录增多到引起叶的分裂的时候，相同前缀的记录会尽量存储在相同的BLOCK中，即BLOCK中的记录会发生重组。

实验三：压缩索引对查询性能的影响
一般说来，因为索引压缩后所占用的空间比较小，所以在发生索引扫描的时候需要访问的索引块比较小，会提高查询的性能。

SQL> run  1* select * from test10 where id1='aaaaaaaaaaaaaaaaaaaa'已选择9999行。Execution Plan----------------------------------------------------------   0      SELECT STATEMENT Optimizer=CHOOSE   1    0   INDEX (RANGE SCAN) OF 'IND_TEST10' (NON-UNIQUE)Statistics----------------------------------------------------------          0  recursive calls          0  db block gets        744  consistent gets          0  physical reads          0  redo size     484574  bytes sent via SQL*Net to client      74350  bytes received via SQL*Net from client        668  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)9999rows processedSQL> run  1* select * from test11 where id1='aaaaaaaaaaaaaaaaaaaa'已选择9999行。Execution Plan----------------------------------------------------------   0      SELECT STATEMENT Optimizer=CHOOSE   1    0   INDEX (RANGE SCAN) OF 'IND_TEST11' (NON-UNIQUE)Statistics----------------------------------------------------------          0  recursive calls          0  db block gets        691  consistent gets          0  physical reads          0  redo size     484574  bytes sent via SQL*Net to client      74350  bytes received via SQL*Net from client        668  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)       9999  rows processed