Oracle索引技术的应用与剖析

最新推荐文章于 2024-09-22 11:17:00 发布

iteye_2060

最新推荐文章于 2024-09-22 11:17:00 发布

阅读量95

点赞数

文章标签：数据库

<div>

Oracle

索引技术的应用与剖析

</div>
<div style="text-indent: 24.1pt;">
最
近这段时间，总是想写一些有关性能调优的文章。但是苦于没有一个实际的案例，本人又不愿空谈理论，因为这些理论随便在网上就能找到，而且基本上千篇一律，
因为理论上的那些东西就那么多，再怎么讲也不如一个实际案例生动。还好上天不负有心人，前些天让我碰到了一个实际的案例。这个实际案例是这样，本人所在的
城市的某个政府职能部门，要推出一项新的服务，这项服务就是要向社会发放一张功能完备的

CPU

卡
片，通过这张卡片可以经办该政府部门所负责相关业务，在某种程度上为广大市民提供了很大方便。在制作这张卡片过程中，需要将用户的身份证，姓名以及个人照
片打印在卡片上，并且要在卡片的芯片内写入一些信息。因此出现了两个问题，第一、该政府职能部门的自身的业务系统数据库中，没有公民个人照片信息以及卡片
内部所需的一些信息；第二、在该政府职能部门的数据库内，存在着一些错误信息，比如：身份证号码错误、姓名错误等等。为了解决这个问题，该政府部门用户提
出了一个方案，那就是拿本系统中的个人基本信息与当地公安系统的人口信息通过身份证号码进行比对，并且将能够比对上的人员所需的并且本系统没有的个人关键
信息，从人口数据库中取得，同时将没有比对上的个人信息进行标记，以便下发由当事人到相关部门去修正。由于该政府部门的所有业务系统，都是由本人所在的公
司确切的说是由本人设计并组织开发的，因此理所当然本人有幸承接这个“神圣而伟大”的任务。

</div>
<div style="text-indent: 24.1pt;">
刚拿到这个需求时，觉得这个需求还是比较简单的，就是一个对照比对，然后将能够匹配的记录所需要的数据拿到。因此我将本系统中的数据到一张表中，该表设计脚本如下：


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
);
</div>
<div style="text-indent: 24.1pt;">
在这张表中蓝色字段是从本系统数据中导出的，红色字段事要到人口库中比对并取得的

,flag

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

</div>
<div>
……
</div>
<div>
cursor info is 

</div>
<div>
select t1.xm,t1.xb,t1.xbmc,t1.csrq,t1.mz,t1.mzmc,t1.hyzk,

</div>
<div>
t1.hyzkmc,t1.photoid,t2.aac001

</div>
<div>
from T1 t1,T2 t2

</div>
<div>
where t1.sfzhm=t2.aac002;

</div>
<div>
/*

</div>
<div>
其中

T1

是人口库中人员基本信息表，

T2

是我带到哪里去比对的人员信息表，双方通过身份证号码进行关联

</div>
<div>
*/

</div>
<div>
begin

</div>
<div>
for c in info loop

</div>
<div style="text-indent: 24pt;">
update T2 

</div>
<div style="">
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,

</div>
<div style="">
 phototid=c.photoid,flag=’1’

</div>
<div style="">
where aac001=c.aac001;

</div>
<div>
end loop;

</div>
<div style="text-indent: 11.8pt;">
commit;

</div>
<div>
exception

</div>
<div style="text-indent: 24pt;">
when others then

</div>
<div style="text-indent: 24pt;">
rollback;

</div>
<div>
end;

</div>
<div style="text-indent: 24.1pt;">
这个过程在执行时运行了大约半个小时，然后安全的返回了，我本以为任务完成了，但是当我检查

T1

表时，却发现没任何信息被比对上，这怎么可能？难道真的就是没有匹配的记录吗？这不可能！我非常坚定，于是我查看了

Oracle

的在线日志，发现

Update

操作在执行了一段时间后戛然而止，我又查看了警告日文件，发现了那个著名的

Ora1555

错误。这是由于内存不足引起的，因此我判断这是由于关联后出现了大量的匹配记录，这些匹配记录在进行

update

操作时出现了回滚段充满而又没被释放的情况，因此出现了

Ora1555

。这怎么办？没什么作为公司研发中心的技术总监，我当然有办法。我马上重写了一个过程，这个过程类似代码如下：

</div>
<div>
……
</div>
<div>
v_csnum number(5);

</div>
<div>
cursor info is 

</div>
<div>
select t2.aac001

，

t2.aac002

</div>
<div>
from T2 t2

</div>
<div>
where t1.sfzhm=t2.aac002;

</div>
<div>
begin

</div>
<div>
for c in info loop

</div>
<div style="text-indent: 24pt;">
select count(1) from T1 where sfzhm=c.aac002;

</div>
<div style="text-indent: 24pt;">
if (v_csnum=1) then

</div>
<div style="text-indent: 24pt;">
……

</div>
<div style="text-indent: 24pt;">
else

</div>
<div style="text-indent: 24pt;">
update T2 set flag=’2’ where aac001=c.aac001

</div>
<div style="text-indent: 24pt;">
end if;

</div>
<div>
end loop;

</div>
<div style="text-indent: 17.7pt;">
commit;

</div>
<div>
exception

</div>
<div>
 when others then

</div>
<div>
 rollback;

</div>
<div>
end;

</div>
<div style="text-indent: 24.1pt;">
这个过程的思想就是查询出

T2

表中的主键和身份证号码字段，然后循环这个结果集，通过身份证去逐条比对，这样看上去好像效率不高，但是在

T1

表中的身份证字段

sfzhm

在
人口数据库中是一个索引字段，而且游标只查询一张表的两个字段，并且在更新时是通过主键更新的，效率也很高，并且不会浪费太多空间。也就是说这个过程充分
利用了数据库的索引字段来完成操作。在运行这个过程半个多小时后，他安全的返回了，当我满心欢喜的去检查结果时，又一件让我捉摸不透的事情发生了，这个过
程只比对成功了

9700

条记录，还剩下几十万人根本没有参与运算。这又是怎么回事？于是我将已经比对上的人员导出，又执行了一下，结果还是一样只比对上了

9

千
人左右，因此我怀疑这个过程每次只运行前一万条记录，后面的根本就不参与运算。这样也行啊，不就二十几万人吗，执行几次就可以了！可是我的如意算盘又落空
了，但执行到第五次时，过程以闪电般的速度返回了，而且没有任何错误信息。但是却一条记录也没有比对上。在运行以下小果还是一样，我赶忙查询了日志信息，
发现没有任何错误，只是打开了很多游标。对于

for

循环方式打开游标，是由

Oracle

自动完成的，而且它的关闭也是由

Oracle

控制的。因此我怀疑每次循环

Oracle

都会打开一次游标，而且关闭的不太及时，另外在这个数据库中对游标的打开最大数量还有限制。这时时间已经很晚了，当天的工作只好先结束，我带着郁闷的心情不得不离开。

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

</div>
<div style="text-indent: 16.05pt;">
procedure

 fetchmatchperson(prm_appcode out
number
,
</div>
<div style="text-indent: 16pt;">
 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
;
</div>
<div style="text-indent: 16pt;"></div>
<div style="text-indent: 24.1pt;">
首先我在原信息表中也就是上面所说的

T2

表，这里的

teda_ryxx

表中增加了两个字段，

source

和

object_type

并通过

update

语句赋予相应的值分别是

’le’

和

’ma’

，然后在

aac001,source,object_type

上建立了复合索引ＩＮＤＸ——ＭＡ，如下：

</div>
<div>
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
);

然后我写出了上面过程蓝色
部分的那个带参数的游标，而且这次采用ｏｐｅｎ／ｃｌｏｓｅ方式来控制游标的打开与关闭。这个游标为什么这样来写，在讲解这个之前我需要讲两个专题。第一
就是在Ｏｒａｃｌｅ中实现分页的技术，在Ｏｒａｃｌｅ中实现分页查询，是通过ｒｏｗｎｕｍ关键字来实现的，ｒｏｗｎｕｍ代表一个表中的记录数，比如我要查
询一个表中的前１００条记录，可以这样写：

</div>
<div style="text-indent: 24.75pt;">
select * from (select row_.*,rownum rownum_

</div>
<div style="text-indent: 24.75pt;">
 from (select * from tablename) row_

</div>
<div style="text-indent: 24.75pt;">
 where rownum<=100)

</div>
<div style="text-indent: 24.75pt;">
 where rownum_>0;

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

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

</div>
<div style="">
１、 


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

</div>
<div style="">
２、 



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


</div>
<div style="">
３、 



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


</div>
<div style="">
４、 



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


</div>
<div style="">
５、 



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


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

</div>
<div>
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
;

在对索引排序时要注意符合
索引的所有列必须都要用到，而且顺序要与索引定义顺序一样，否则ｏｒｄｅｒ　ｂｙ　将会弃用索引。另外由于我们在索引的ｒｏｗｉｄ上先进行了排序，因此不
会出现分页查询中的逻辑读积增的问题。所谓逻辑读积增，就是第一次分页读１００条记录加入逻辑读是１００，那么第１０次读逻辑读就是１０００，但是却只返
回了９００到１０００的１００条记录，因此对性能造成损害，合理的使用索引以及合理利用索引特性，利用了索引是有序的，所以先对索引排序分页，在回表查
询，这样就避免了逻辑读积增。这个游标的每一次的执行计划类似下面这样：

</div>
<div>
select statement

</div>
<div>
view

</div>
<div>
count stopkey

</div>
<div>
 view

</div>
<div>
 table access by index row

ｉ

d

</div>
<div>
 index range scan descending

</div>
<div>
可以看出这里成功的使用了ｓｔｏｐｋｅｙ内部关键字，限制了每次的返回记录数，另外每次对表的检索都是通过索引完成的，大大提高了效率。另外在这个游标中我还使用了ｈｉｎｔ线索

/*+ordered use_nl(t,teda_ryxx)*/

，

通过这个线索，可以实现将每次的执行计划稳定下来，避免不必要的硬解析。这在高可用的ＯＬＴＰ环境中是必要的。

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

</div>