索引压缩仅用于复合索引,即多个字段建立一个索引的情况,通过compress参数指定压缩哪些字段.
虽然压缩后的索引,相对来说需要花费更多的CPU时间来处理,
但是,这样做后,可以在高速缓冲区中缓存更多的索引块,当大范围的扫描时,能够减少物理IO的数量.
压缩索引特别那些需要节省存储空间的情况,以下那些单次查询的范围比较大的情况.
不适合的情况,举例:
1.保险支付项目_IX_项目编码 on 保险支付项目(项目编码,险类)
如果设置compress为1表示压缩第一个字段,第一个字段项目编码,相同的很少,可压缩的很少.
估计是这个索引创建得不合理,应该把险类和项目编码的顺序互换一下,因为查询时,一般险类和项目编码会同时出现在Where子句中
这样,相同险类的项目编码就很多,比较适合压缩,
但是应用程序中,大多数情况下,只需取一条记录,少有大范围的取数情况,并且本身不是无限增长的表,节省空间也很少,就没有必要压缩了.
2.病人未结费用_IX_病人id on 病人未结费用(病人id,主页ID)
这个也没有压缩的必要,因为病人未结费用表中的病人ID相同的记录很少(因为记录的数据是按病人科室id,执行部门id,收入项目id等汇总的)
相反,病人费用记录(病人id,主页ID)
这个就非常适合了,因为同一病人,特别是住院病人的记录比较多,而且结帐或费用查询时,一般是大范围的扫描取数,压缩后就可以减少物理IO的数量.
下面以索引:票据使用明细_IX_使用人为例,通过实验观察压缩对空间的占用效果,以及性能影响对比.
说明:show_space过程是用于统计数据对象的空间使用情况的过程,
可以在ask tom网下载适合于Oracle各种版本的,ITPUB上有也一些版本可用.
SQL> set serverout on;
--1.原始信息
SQL> exec show_space('票据使用明细_IX_使用人','ZLHIS','INDEX');
Free Blocks.............................1
Total Blocks............................1685
Total Bytes.............................13803520
Unused Blocks...........................10
Unused Bytes............................81920
Last Used Ext FileId....................12
Last Used Ext BlockId...................160644
Last Used Block.........................10
PL/SQL procedure successfully completed
--2.重建索引
SQL> drop index 票据使用明细_IX_使用人;
Index dropped
SQL> CREATE INDEX 票据使用明细_IX_使用人 on 票据使用明细(使用人,票种,性质) pctfree 10 TABLESPACE zl9Expense;
Index created
SQL> exec show_space('票据使用明细_IX_使用人','ZLHIS','INDEX');
Free Blocks.............................0
Total Blocks............................976
Total Bytes.............................7995392
Unused Blocks...........................12
Unused Bytes............................98304
Last Used Ext FileId....................12
Last Used Ext BlockId...................158793
Last Used Block.........................4
PL/SQL procedure successfully completed
--pctfree 0
SQL> CREATE INDEX 票据使用明细_IX_使用人 on 票据使用明细(使用人,票种,性质) pctfree 0 TABLESPACE zl9Expense;
Index created
SQL> exec show_space('票据使用明细_IX_使用人','ZLHIS','INDEX');
Free Blocks.............................0
Total Blocks............................880
Total Bytes.............................7208960
Unused Blocks...........................14
Unused Bytes............................114688
Last Used Ext FileId....................12
Last Used Ext BlockId...................158296
Last Used Block.........................2
PL/SQL procedure successfully completed
--3.重建压缩索引
SQL> drop index 票据使用明细_IX_使用人;
Index dropped
SQL> CREATE INDEX 票据使用明细_IX_使用人 on 票据使用明细(使用人,票种,性质) compress 3 PCTFREE 10 TABLESPACE zl9Expense;
Index created
SQL> exec show_space('票据使用明细_IX_使用人','ZLHIS','INDEX');
Free Blocks.............................0
Total Blocks............................464
Total Bytes.............................3801088
Unused Blocks...........................8
Unused Bytes............................65536
Last Used Ext FileId....................12
Last Used Ext BlockId...................156157
Last Used Block.........................8
PL/SQL procedure successfully completed
--*********************************************************************************************************************
以上数据表明,重建索引后,索引占用的块数由13.8M降到了7.9M,压缩后降到了3.8M,压缩率达50%以上
--*********************************************************************************************************************
--4.性能对比
--操作步骤
SQL> alter session set timed_statistics=true;
Session altered
SQL> alter session set sql_trace=true;
Session altered
SQL> Begin
2 For x In (Select * From 票据使用明细 Where 使用人 = '吴敏' And 票种 = 1 And 性质 = 1) Loop
3 For y In (Select * From 票据使用明细 Where 使用人 = '吴敏' And 票种 = 1 And 性质 = 1) Loop
4 Null;
5 End Loop;
6 End Loop;
7 End;
8 /
PL/SQL procedure successfully completed
SQL> alter session set sql_trace=false;
--查询效率分析结果
--a.压缩前
Begin
For x In (Select * From 票据使用明细 Where 使用人 = '吴敏' And 票种 = 1 And 性质 = 1) Loop
For y In (Select * From 票据使用明细 Where 使用人 = '吴敏' And 票种 = 1 And 性质 = 1) Loop
Null;
End Loop;
End Loop;
End;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.02 0 0 0 0
Execute 1 1.69 1.72 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 1.71 1.74 0 0 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 45
********************************************************************************
SELECT *
FROM
票据使用明细 WHERE 使用人 = '吴敏' AND 票种 = 1 AND 性质 = 1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.01 0.01 0 0 0 0
Execute 38 0.00 0.00 0 0 0 0
Fetch 1444 0.02 0.02 0 2926 0 1406
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1484 0.03 0.03 0 2926 0 1406
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 45 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
37 TABLE ACCESS BY INDEX ROWID 票据使用明细
38 INDEX RANGE SCAN (object id 41899)
********************************************************************************
--b.压缩后
Begin
For x In (Select * From 票据使用明细 Where 使用人 = '吴敏' And 票种 = 1 And 性质 = 1) Loop
For y In (Select * From 票据使用明细 Where 使用人 = '吴敏' And 票种 = 1 And 性质 = 1) Loop
Null;
End Loop;
End Loop;
End;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.02 0 0 0 0
Execute 1 1.72 1.77 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 1.74 1.79 0 0 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 45
********************************************************************************
SELECT *
FROM
票据使用明细 WHERE 使用人 = '吴敏' AND 票种 = 1 AND 性质 = 1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.02 0.02 0 0 0 0
Execute 38 0.00 0.00 0 0 0 0
Fetch 1444 0.01 0.01 0 2926 0 1406
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1484 0.03 0.03 0 2926 0 1406
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 45 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
37 TABLE ACCESS BY INDEX ROWID 票据使用明细
38 INDEX RANGE SCAN (object id 41900)
--结果表明,压缩索引对CPU的负面影响几乎可以忽略,逻辑IO相同,CPU仅有细微差别
