当单列索引和复合索引中的数据列重复项比较多的时候，可以考虑进行索引压缩。索引压缩可以在某种程度上减小索引所占空间，减小扫描索引时候的I/O，提高查询的性能。
语法：create index index_name on table_name(col1,col2 ….coln) compress n; （n>0）
不输入n的话，默认压缩所有的索引列
索引中的前n项被压缩，称做前缀。
运行环境
[@more@]SQL> conn / as sysdba
已连接。
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
PL/SQL Release 8.1.6.0.0 - Production
CORE 8.1.6.0.0 Production
TNS for 32-bit Windows: Version 8.1.6.0.0 - Production
NLSRTL Version 3.4.1.0.0 – Production
实验一：索引压缩后占用空间的变化
SQL> drop table test10;
Table dropped
SQL> create table test10
2 (id1 varchar2(20),
3 id2 varchar2(20),
4 id3 varchar2(20))
5 tablespace test;
Table created
SQL> insert into test10 select 'aaaaaaaaaaaaaaaaaaaa','bbbbbbbbbbbbbbbbbbbb',rownum from dba_objects where rownum<10000;
9999 rows inserted
SQL> insert into test10 select 'bbbbbbbbbbbbbbbbbbbb','aaaaaaaaaaaaaaaaaaaa',rownum from dba_objects where rownum<10000;
9999 rows inserted
SQL> commit;
Commit complete
SQL> insert into test10 select 'aaaaaaaaaaaaaaaaaaaa','bbbbbbbbbbbbbbbbbbbb',rownum from dba_objects where rownum<10000;
9999 rows inserted
SQL> insert into test10 select 'bbbbbbbbbbbbbbbbbbbb','aaaaaaaaaaaaaaaaaaaa',rownum from dba_objects where rownum<10000;
9999 rows inserted
SQL> commit;
Commit complete
SQL> create table test11 as select * from test10;
Table created
SQL> create index ind_test10 on test10(id1,id2,id3) tablespace test;
Index created
SQL> create index ind_test11 on test11(id1,id2,id3) compress 2 tablespace test;
Index created
SQL> set serveroutput on
SQL> exec show_space('IND_TEST10','I','MYTEST');
Free Blocks.............................0
Total Blocks............................195
Total Bytes.............................1597440
Unused Blocks...........................29
Unused Bytes............................237568
Last Used Ext FileId....................8
Last Used Ext BlockId...................2127
Last Used Block.........................36
PL/SQL procedure successfully completed
SQL> exec show_space('IND_TEST11','I','MYTEST');
Free Blocks.............................0
Total Blocks............................55
Total Bytes.............................450560
Unused Blocks...........................8
Unused Bytes............................65536
Last Used Ext FileId....................8
Last Used Ext BlockId...................2227
Last Used Block.........................12
PL/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 created
SQL> insert into test12 select rownum from dba_objects;
23725 rows inserted
SQL> commit;
Commit complete
SQL> create table test13 as select * from test12;
Table created
SQL> create index ind_test12 on test12(id) tablespace test;
Index created
SQL> create index ind_test13 on test13(id) compress tablespace test;
Index created
SQL> exec show_space('IND_TEST12','I','MYTEST');
Free Blocks.............................0
Total Blocks............................55
Total Bytes.............................450560
Unused Blocks...........................1
Unused Bytes............................8192
Last Used Ext FileId....................8
Last Used Ext BlockId...................2392
Last Used Block.........................19
PL/SQL procedure successfully completed
SQL> exec show_space('IND_TEST13','I','MYTEST');
Free Blocks.............................0
Total Blocks............................85
Total Bytes.............................696320
Unused Blocks...........................11
Unused Bytes............................90112
Last Used Ext FileId....................8
Last Used Ext BlockId...................2467
Last Used Block.........................19
PL/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 created
SQL> insert into test14 values ('aa','bb','11');
1 row inserted
SQL> insert into test14 values ('aa','bb','22');
1 row inserted
SQL> insert into test14 values ('bb','aa','11');
1 row inserted
SQL> insert into test14 values ('bb','aa','2');
1 row inserted
SQL> commit;
Commit complete
SQL> select * from test14;
ID1 ID2 ID3
-------------------- -------------------- --------------------
aa bb 11
aa bb 22
bb aa 11
bb aa 2
SQL> create index ind_test14 on test14(id1,id2,id3) compress 2;
Index created
SQL> exec show_space('IND_TEST14','I','MYTEST');
Free Blocks.............................0
Total Blocks............................5
Total Bytes.............................40960
Unused Blocks...........................3
Unused Bytes............................24576
Last Used Ext FileId....................8
Last Used Ext BlockId...................2502
Last Used Block.........................2
PL/SQL procedure successfully completed
SQL> alter system dump datafile 8 block 2503;
System altered
Start dump data blocks tsn: 7 file#: 8 minblk 2503 maxblk 2503
buffer tsn: 7 rdba: 0x020009c7 (8/2503)
scn: 0x0000.24a47653 seq: 0x01 flg: 0x00 tail: 0x76530601
frmt: 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/Fsc
0x01 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 xid: 0x0006.005.000000b9 uba: 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
Leaf block dump
===============
header address 365784156=0x15cd6c5c
kdxcolev 0
kdxcolok 0
kdxcoopc 0xa0: opcode=0: iot flags=-C- is converted=Y
kdxconco 4
kdxcosdc 0
kdxconro 4
kdxcofbo 56=0x38
kdxcofeo 7973=0x1f25
kdxcoavs 7917
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
kdxlepnro 2
kdxlepnco 2
prefix row#0[8028] flag: -P---, lock: 0 //即‘aa’，‘bb’
col 0; len 2; (2): 61 61
col 1; len 2; (2): 62 62
prc 2 //在这个BLOCK中前缀为‘aa’，‘bb’的记录数2
prefix row#1[7996] flag: -P---, lock: 0 //即‘bb’，‘aa’
col 0; len 2; (2): 62 62
col 1; len 2; (2): 61 61
prc 2 //在这个BLOCK中前缀为‘bb，‘aa’的记录数2
row#0[8016] flag: -----, lock: 0
col 0; len 2; (2): 31 31
col 1; len 6; (6): 02 00 09 c2 00 00
psno 0 //此条记录的前缀序号为‘0’，即‘aa’，‘bb’
row#1[8004] flag: -----, lock: 0
col 0; len 2; (2): 32 32
col 1; len 6; (6): 02 00 09 c2 00 01
psno 0
row#2[7984] flag: -----, lock: 0
col 0; len 2; (2): 31 31
col 1; len 6; (6): 02 00 09 c2 00 02
psno 1 //此条记录的前缀序号为‘1’，即‘bb’，‘aa’
row#3[7973] flag: -----, lock: 0
col 0; len 1; (1): 32
col 1; len 6; (6): 02 00 09 c2 00 03
psno 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)
9999 rows processed
SQL> 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
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8394333/viewspace-987139/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/8394333/viewspace-987139/
扫一扫
1266
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
