Oracle索引技术的应用与剖析

Oracle 索引技术的应用与剖析

最 近这段时间，总是想写一些有关性能调优的文章。但是苦于没有一个实际的案例，本人又不愿空谈理论，因为这些理论随便在网上就能找到，而且基本上千篇一律， 因为理论上的那些东西就那么多，再怎么讲也不如一个实际案例生动。还好上天不负有心人，前些天让我碰到了一个实际的案例。这个实际案例是这样，本人所在的 城市的某个政府职能部门，要推出一项新的服务，这项服务就是要向社会发放一张功能完备的 CPU 卡 片，通过这张卡片可以经办该政府部门所负责相关业务，在某种程度上为广大市民提供了很大方便。在制作这张卡片过程中，需要将用户的身份证，姓名以及个人照 片打印在卡片上，并且要在卡片的芯片内写入一些信息。因此出现了两个问题，第一、该政府职能部门的自身的业务系统数据库中，没有公民个人照片信息以及卡片 内部所需的一些信息；第二、在该政府职能部门的数据库内，存在着一些错误信息，比如：身份证号码错误、姓名错误等等。为了解决这个问题，该政府部门用户提 出了一个方案，那就是拿本系统中的个人基本信息与当地公安系统的人口信息通过身份证号码进行比对，并且将能够比对上的人员所需的并且本系统没有的个人关键 信息，从人口数据库中取得，同时将没有比对上的个人信息进行标记，以便下发由当事人到相关部门去修正。由于该政府部门的所有业务系统，都是由本人所在的公 司确切的说是由本人设计并组织开发的，因此理所当然本人有幸承接这个“神圣而伟大”的任务。

刚拿到这个需求时，觉得这个需求还是比较简单的，就是一个对照比对，然后将能够匹配的记录所需要的数据拿到。因此我将本系统中的数据到一张表中，该表设计脚本如下： create table TEDA_RYXX ( AAC002 VARCHAR2 (18), AAC001 VARCHAR2 (14) not null , AAC003 VARCHAR2 (20), AAC004 VARCHAR2 (3), AAC005 VARCHAR2 (3), AAC006 DATE , AAC009 VARCHAR2 (3), AAB003 VARCHAR2 (15), AAB004 VARCHAR2 (50), FLAG VARCHAR2( 3 ), NAME VARCHAR2 (20), SEX VARCHAR2 (3), SEXMC VARCHAR2 (6), CSRQ VARCHAR2 (10), HYZK VARCHAR2 (3), HYZKMC VARCHAR2 (10), MZ VARCHAR2 (3), MZMC VARCHAR2 (6), PHOTOID VARCHAR2 (20), ) tablespace TJLMDATA pctfree 10 initrans 1 maxtrans 255 storage ( initial 64 K minextents 1 maxextents unlimited ); alter table TEDA_RYXX add constraint PK_TEDA_AAC001 primary key (AAC001) using index tablespace TJLMDATA pctfree 10 initrans 2 maxtrans 255 storage ( initial 64 K minextents 1 maxextents unlimited );

在这张表中蓝色字段是从本系统数据中导出的，红色字段事要到人口库中比对并取得的 ,flag 字段是个标志字段，对比对上的人员进行标记。因此我带着这这张表兴高采烈的到了公安系统的人口数据库中，去完成这个看似简单到极点的任务。为了进行比对我写了类似如下的存储过程：

……

cursor info is

select t1.xm,t1.xb,t1.xbmc,t1.csrq,t1.mz,t1.mzmc,t1.hyzk,

t1.hyzkmc,t1.photoid,t2.aac001

from T1 t1,T2 t2

where t1.sfzhm=t2.aac002;

/*

其中 T1 是人口库中人员基本信息表， T2 是我带到哪里去比对的人员信息表，双方通过身份证号码进行关联

*/

begin

for c in info loop

update T2

set name=c.xm,sex=c.xb,sexmc=c.xbmc,csrq=c.csrq,mz=c.mz,mzmc=c.mzmc,hyzk=c.hyzk,hyzkmc=c.hyzkmc,

phototid=c.photoid,flag=’1’

where aac001=c.aac001;

end loop;

commit;

exception

when others then

rollback;

end;

这个过程在执行时运行了大约半个小时，然后安全的返回了，我本以为任务完成了，但是当我检查 T1 表时，却发现没任何信息被比对上，这怎么可能？难道真的就是没有匹配的记录吗？这不可能！我非常坚定，于是我查看了 Oracle 的在线日志，发现 Update 操作在执行了一段时间后戛然而止，我又查看了警告日文件，发现了那个著名的 Ora1555 错误。这是由于内存不足引起的，因此我判断这是由于关联后出现了大量的匹配记录，这些匹配记录在进行 update 操作时出现了回滚段充满而又没被释放的情况，因此出现了 Ora1555 。这怎么办？没什么作为公司研发中心的技术总监，我当然有办法。我马上重写了一个过程，这个过程类似代码如下：

……

v_csnum number(5);

cursor info is

select t2.aac001 ， t2.aac002

from T2 t2

where t1.sfzhm=t2.aac002;

begin

for c in info loop

select count(1) from T1 where sfzhm=c.aac002;

if (v_csnum=1) then

……

else

update T2 set flag=’2’ where aac001=c.aac001

end if;

end loop;

commit;

exception

when others then

rollback;

end;

这个过程的思想就是查询出 T2 表中的主键和身份证号码字段，然后循环这个结果集，通过身份证去逐条比对，这样看上去好像效率不高，但是在 T1 表中的身份证字段 sfzhm 在 人口数据库中是一个索引字段，而且游标只查询一张表的两个字段，并且在更新时是通过主键更新的，效率也很高，并且不会浪费太多空间。也就是说这个过程充分 利用了数据库的索引字段来完成操作。在运行这个过程半个多小时后，他安全的返回了，当我满心欢喜的去检查结果时，又一件让我捉摸不透的事情发生了，这个过 程只比对成功了 9700 条记录，还剩下几十万人根本没有参与运算。这又是怎么回事？于是我将已经比对上的人员导出，又执行了一下，结果还是一样只比对上了 9 千 人左右，因此我怀疑这个过程每次只运行前一万条记录，后面的根本就不参与运算。这样也行啊，不就二十几万人吗，执行几次就可以了！可是我的如意算盘又落空 了，但执行到第五次时，过程以闪电般的速度返回了，而且没有任何错误信息。但是却一条记录也没有比对上。在运行以下小果还是一样，我赶忙查询了日志信息， 发现没有任何错误，只是打开了很多游标。对于 for 循环方式打开游标，是由 Oracle 自动完成的，而且它的关闭也是由 Oracle 控制的。因此我怀疑每次循环 Oracle 都会打开一次游标，而且关闭的不太及时，另外在这个数据库中对游标的打开最大数量还有限制。这时时间已经很晚了，当天的工作只好先结束，我带着郁闷的心情不得不离开。

第 二天早上我到公司继续思考，昨天的问题，突然有一个想法涌上心头。可不可以利用分页查询的办法来解决这个问题呢？在应用系统开发中，分页查询是经常采用的 一项技术，就是当记录数很多时，不是一次性全部查出，而是分批分次查询，每次查询控制一定的查询数量。我想这个方法也能解决我遇到的问题，因此我针对这个 想法，又采用了一些调优手段，写出了类似如下的过程：

procedure fetchmatchperson(prm_appcode out number ,

prm_errmsgout varchar2 ) is cursor grepinfo(prm_start number , prm_end number ) is select /*+ordered use_nl(t,teda_ryxx)*/ aac001, aac002 from (select rid from (select rownum rn, rid from (select rowid rid from teda_ryxx where source = 'le' and object_type = 'ma' order by aac001 desc ) where rownum <= prm_end) where rn >= prm_start) t, teda_ryxx where t.rid = teda_ryxx.rowid ; v_xm ac01.aac003%type ; v_xb ac01.aac004%type ; v_csrq ac01.aac006%type ; v_mz ac01.aac005%type ; csnum number ( 2 ); endnum number ( 10 ); innerstart number ( 5 ); innerend number ( 5 ); v_aac001 teda_ryxx.aac001%type ; v_aac002 teda_ryxx.aac002%type ; begin prm_appcode := def_ok; endnum := 0 ; innerstart:= 0 ; innerend := 0 ; while (endnum <= 20000 ) loop innerstart := innerend; innerend := innerend + 1000 ; open grepinfo(innerstart, innerend); loop fetch grepinfo into v_aac001, v_aac002; exit when grepinfo %notfound ; select count ( 1 ) into csnum from ac01 where aac002 = v_aac002; if (csnum = 1 ) then select aac003, aac004, aac006, aac005 into v_xm, v_xb, v_csrq, v_mz from ac01 where aac002 = v_aac002; update teda_ryxx set name = v_xm, sex= v_xb, csrq = v_csrq, mz = v_mz, flag = '1' where aac001 = v_aac001; else update teda_ryxx set flag = '2' where aac001 = v_aac001; end if ; end loop ; close grepinfo; commit ; endnum := endnum + innerend; end loop ; commit ; exception when others then prm_appcode := def_error; prm_errmsg:= sqlerrm ; end ;

首先我在原信息表中也就是上面所说的 T2 表，这里的 teda_ryxx 表中增加了两个字段， source 和 object_type 并通过 update 语句赋予相应的值分别是 ’le’ 和 ’ma’ ，然后在 aac001,source,object_type 上建立了复合索引ＩＮＤＸ——ＭＡ，如下：

create index INDX_MA on TEDA_RYXX (SOURCE ,OBJECT_TYPE,AAC001) tablespace INDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 64 K minextents 1 maxextents unlimited ); 然后我写出了上面过程蓝色 部分的那个带参数的游标，而且这次采用ｏｐｅｎ／ｃｌｏｓｅ方式来控制游标的打开与关闭。这个游标为什么这样来写，在讲解这个之前我需要讲两个专题。第一 就是在Ｏｒａｃｌｅ中实现分页的技术，在Ｏｒａｃｌｅ中实现分页查询，是通过ｒｏｗｎｕｍ关键字来实现的，ｒｏｗｎｕｍ代表一个表中的记录数，比如我要查 询一个表中的前１００条记录，可以这样写：

select * from (select row_.*,rownum rownum_

from (select * from tablename) row_

where rownum<=100)

where rownum_>0;

通过这句查询我们就可以实现分页查询，并且我们可以将分页两个端点作为参数传入语句中，这样就可实现逐批地查询了。

第二我要讲讲Ｏｒａｃｌｅ索引技术，这项技术在我们这个方案中是一个决定成败的关键技术，索引的使用对于各种Ｏｒａｃｌｅ调优也是非常关键的。Ｏｒａｃｌｅ采用树形结构的索引而且索引是有序的，我们在日常的查询中，对索引的使用应该有以下５种情况：

１、 索引唯一扫描：通过主键或者唯一索引来查询记录，这种方式也是效率最高的。

２、 索引范围扫描：这种方式发生在返回多个值时，如通过ｗｈｅｒｅ　ｉｄ＞　ａｎｄ　ｉｄ　＜，或者非唯一索引ｉｄ＝＇＇等语句，范围扫描要求返回的结果集不能太大，否则将不会使索引发挥作用。

３、 索引全扫描：Ｏｒａｃｌｅ会按照索引的顺序全部扫描该索引，类似全表扫描，效率不高。

４、 索引快速全扫描：它不按照索引顺序来访问，而是直接读取索引块来访问索引数据。在这种存取方法中可以利用多块读功能，该种情况一般出现在ｓｅｌｅｃｔ　ｃｏｕｎｔ（＊）这种情况中。

５、 索引跳跃式扫描：这是９ｉ之后的新功能，一般出现在复合索引中，如在字段（Ａ１，Ａ２）上建立符合索引，当执行ｗｈｅｒｅ　Ａ２＝ｖａｌｕｅ查询时，Ｏｒａｃｌｅ会跳过Ａ１，直接使Ａ２使用这个符合索引。（这个功能在实践中尚待考量）

Ｏｒａｃｌｅ中的索引，其实是 ｒｏｗｉｄ的一个逻辑名，在通过索引进行检索时，都是先检索到索引代表的ｒｏｗｉｄ值，然后通过ｒｏｗｉｄ回表检索目标数据。如果我们要检索的数据就在索 引字段中，那么我们通过索引就能获得数据，也就不用回表检索了，那么效率将会更加提高，因此我们要在上面的 ｓｏｕｒｃｅ，ｏｂｊｅｃｔ＿ｔｙｐｅ，ａａｃ００１字段上建立符合索引，而且将ａａｃ００１作为排序字段，我们的思路是利用索引，但是在索引上先排序， 再分页，选择完ｒｏｗｉｄ，再回表查询（我们这里其实不用回表再查询了）。因此我们的游标构造如下：

cursor grepinfo(prm_start number , prm_end number ) is select /*+ordered use_nl(t,teda_ryxx)*/ aac001, aac002 from (select rid from (select rownum rn, rid from (select rowid rid from teda_ryxx where source = 'le' and object_type = 'ma' order by aac001 desc ) where rownum <= prm_end) where rn >= prm_start) t, teda_ryxx where t.rid = teda_ryxx.rowid ; 在对索引排序时要注意符合 索引的所有列必须都要用到，而且顺序要与索引定义顺序一样，否则ｏｒｄｅｒ　ｂｙ　将会弃用索引。另外由于我们在索引的ｒｏｗｉｄ上先进行了排序，因此不 会出现分页查询中的逻辑读积增的问题。所谓逻辑读积增，就是第一次分页读１００条记录加入逻辑读是１００，那么第１０次读逻辑读就是１０００，但是却只返 回了９００到１０００的１００条记录，因此对性能造成损害，合理的使用索引以及合理利用索引特性，利用了索引是有序的，所以先对索引排序分页，在回表查 询，这样就避免了逻辑读积增。这个游标的每一次的执行计划类似下面这样：

select statement

view

count stopkey

view

table access by index row ｉ d

index range scan descending

可以看出这里成功的使用了ｓｔｏｐｋｅｙ内部关键字，限制了每次的返回记录数，另外每次对表的检索都是通过索引完成的，大大提高了效率。另外在这个游标中我还使用了ｈｉｎｔ线索 /*+ordered use_nl(t,teda_ryxx)*/ ， 通过这个线索，可以实现将每次的执行计划稳定下来，避免不必要的硬解析。这在高可用的ＯＬＴＰ环境中是必要的。

　　通过以上这些技术的采用，成功地完成了这个比对工作，而且比对程序在数据库中高效并且平滑的运行，没有对系统造成性能损害。特此将这次的心得与体会贡献出来，供大家参考。