本文關(guān)鍵詞：Oracle索引技術(shù)，由筆耕文化傳播整理發(fā)布。

Oracle索引技術(shù)的應(yīng)用與剖析

最近這段時(shí)間，總是想寫一些有關(guān)性能調(diào)優(yōu)的文章。但是苦于沒有一個(gè)實(shí)際的案例，本人又不愿空談理論，因?yàn)檫@些理論隨便在網(wǎng)上就能找到，而且基本上千篇一律，因?yàn)槔碚撋系哪切〇|西就那么多，再怎么講也不如一個(gè)實(shí)際案例生動(dòng)。還好上天不負(fù)有心人，前些天讓我碰到了一個(gè)實(shí)際的案例。這個(gè)實(shí)際案例是這樣，本人所在的城市的某個(gè)政府職能部門，要推出一項(xiàng)新的服務(wù)，這項(xiàng)服務(wù)就是要向社會(huì)發(fā)放一張功能完備的CPU卡片，通過這張卡片可以經(jīng)辦該政府部門所負(fù)責(zé)相關(guān)業(yè)務(wù)，在某種程度上為廣大市民提供了很大方便。在制作這張卡片過程中，需要將用戶的身份證，姓名以及個(gè)人照片打印在卡片上，并且要在卡片的芯片內(nèi)寫入一些信息。因此出現(xiàn)了兩個(gè)問題，第一、該政府職能部門的自身的業(yè)務(wù)系統(tǒng)數(shù)據(jù)庫中，沒有公民個(gè)人照片信息以及卡片內(nèi)部所需的一些信息；第二、在該政府職能部門的數(shù)據(jù)庫內(nèi)，存在著一些錯(cuò)誤信息，比如：身份證號(hào)碼錯(cuò)誤、姓名錯(cuò)誤等等。為了解決這個(gè)問題，該政府部門用戶提出了一個(gè)方案，那就是拿本系統(tǒng)中的個(gè)人基本信息與當(dāng)?shù)毓蚕到y(tǒng)的人口信息通過身份證號(hào)碼進(jìn)行比對(duì)，并且將能夠比對(duì)上的人員所需的并且本系統(tǒng)沒有的個(gè)人關(guān)鍵信息，從人口數(shù)據(jù)庫中取得，同時(shí)將沒有比對(duì)上的個(gè)人信息進(jìn)行標(biāo)記，以便下發(fā)由當(dāng)事人到相關(guān)部門去修正。由于該政府部門的所有業(yè)務(wù)系統(tǒng)，都是由本人所在的公司確切的說是由本人設(shè)計(jì)并組織開發(fā)的，因此理所當(dāng)然本人有幸承接這個(gè)“神圣而偉大”的任務(wù)。

剛拿到這個(gè)需求時(shí)，覺得這個(gè)需求還是比較簡(jiǎn)單的，就是一個(gè)對(duì)照比對(duì)，然后將能夠匹配的記錄所需要的數(shù)據(jù)拿到。因此我將本系統(tǒng)中的數(shù)據(jù)到一張表中，該表設(shè)計(jì)腳本如下：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 64K    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 64K    minextents 1    maxextents unlimited );

在這張表中藍(lán)色字段是從本系統(tǒng)數(shù)據(jù)中導(dǎo)出的，紅色字段事要到人口庫中比對(duì)并取得的,flag字段是個(gè)標(biāo)志字段，對(duì)比對(duì)上的人員進(jìn)行標(biāo)記。因此我?guī)е@這張表興高采烈的到了公安系統(tǒng)的人口數(shù)據(jù)庫中，去完成這個(gè)看似簡(jiǎn)單到極點(diǎn)的任務(wù)。為了進(jìn)行比對(duì)我寫了類似如下的存儲(chǔ)過程：

……

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是我?guī)У侥睦锶ケ葘?duì)的人員信息表，雙方通過身份證號(hào)碼進(jìn)行關(guān)聯(lián)

*/

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;

這個(gè)過程在執(zhí)行時(shí)運(yùn)行了大約半個(gè)小時(shí)，然后安全的返回了，我本以為任務(wù)完成了，但是當(dāng)我檢查T1表時(shí)，卻發(fā)現(xiàn)沒任何信息被比對(duì)上，這怎么可能？難道真的就是沒有匹配的記錄嗎？這不可能！我非常堅(jiān)定，于是我查看了Oracle的在線日志，發(fā)現(xiàn)Update操作在執(zhí)行了一段時(shí)間后戛然而止，我又查看了警告日文件，發(fā)現(xiàn)了那個(gè)著名的Ora1555錯(cuò)誤。這是由于內(nèi)存不足引起的，因此我判斷這是由于關(guān)聯(lián)后出現(xiàn)了大量的匹配記錄，這些匹配記錄在進(jìn)行update操作時(shí)出現(xiàn)了回滾段充滿而又沒被釋放的情況，因此出現(xiàn)了Ora1555。這怎么辦？沒什么作為公司研發(fā)中心的技術(shù)總監(jiān)，我當(dāng)然有辦法。我馬上重寫了一個(gè)過程，這個(gè)過程類似代碼如下：

……

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;

這個(gè)過程的思想就是查詢出T2表中的主鍵和身份證號(hào)碼字段，然后循環(huán)這個(gè)結(jié)果集，通過身份證去逐條比對(duì)，這樣看上去好像效率不高，但是在T1表中的身份證字段sfzhm在人口數(shù)據(jù)庫中是一個(gè)索引字段，而且游標(biāo)只查詢一張表的兩個(gè)字段，并且在更新時(shí)是通過主鍵更新的，效率也很高，并且不會(huì)浪費(fèi)太多空間。也就是說這個(gè)過程充分利用了數(shù)據(jù)庫的索引字段來完成操作。在運(yùn)行這個(gè)過程半個(gè)多小時(shí)后，他安全的返回了，當(dāng)我滿心歡喜的去檢查結(jié)果時(shí)，又一件讓我捉摸不透的事情發(fā)生了，這個(gè)過程只比對(duì)成功了9700條記錄，還剩下幾十萬人根本沒有參與運(yùn)算。這又是怎么回事？于是我將已經(jīng)比對(duì)上的人員導(dǎo)出，又執(zhí)行了一下，結(jié)果還是一樣只比對(duì)上了9千人左右，因此我懷疑這個(gè)過程每次只運(yùn)行前一萬條記錄，后面的根本就不參與運(yùn)算。這樣也行啊，不就二十幾萬人嗎，執(zhí)行幾次就可以了！可是我的如意算盤又落空了，但執(zhí)行到第五次時(shí)，過程以閃電般的速度返回了，而且沒有任何錯(cuò)誤信息。但是卻一條記錄也沒有比對(duì)上。在運(yùn)行以下小果還是一樣，我趕忙查詢了日志信息，發(fā)現(xiàn)沒有任何錯(cuò)誤，只是打開了很多游標(biāo)。對(duì)于for循環(huán)方式打開游標(biāo)，是由Oracle自動(dòng)完成的，而且它的關(guān)閉也是由Oracle控制的。因此我懷疑每次循環(huán)Oracle都會(huì)打開一次游標(biāo)，而且關(guān)閉的不太及時(shí)，另外在這個(gè)數(shù)據(jù)庫中對(duì)游標(biāo)的打開最大數(shù)量還有限制。這時(shí)時(shí)間已經(jīng)很晚了，當(dāng)天的工作只好先結(jié)束，我?guī)е魫灥男那椴坏貌浑x開。

第二天早上我到公司繼續(xù)思考，昨天的問題，突然有一個(gè)想法涌上心頭。可不可以利用分頁查詢的辦法來解決這個(gè)問題呢？在應(yīng)用系統(tǒng)開發(fā)中，分頁查詢是經(jīng)常采用的一項(xiàng)技術(shù)，就是當(dāng)記錄數(shù)很多時(shí)，不是一次性全部查出，而是分批分次查詢，每次查詢控制一定的查詢數(shù)量。我想這個(gè)方法也能解決我遇到的問題，因此我針對(duì)這個(gè)想法，又采用了一些調(diào)優(yōu)手段，，寫出了類似如下的過程：

procedure fetchmatchperson(prm_appcode out number,  prm_errmsg out 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表中增加了兩個(gè)字段，source和object_type并通過update語句賦予相應(yīng)的值分別是’le’和’ma’，然后在aac001,source,object_type上建立了復(fù)合索引index--ma，如下：

create index INDX_MA on TEDA_RYXX (SOURCE,OBJECT_TYPE,AAC001) tablespace INDX pctfree 10 initrans 2 maxtrans 255 storage (    initial 64K    minextents 1    maxextents unlimited );然后我寫出了上面過程藍(lán)色部分的那個(gè)帶參數(shù)的游標(biāo)，而且這次采用open/close方式來控制游標(biāo)的打開與關(guān)閉。這個(gè)游標(biāo)為什么這樣來寫，在講解這個(gè)之前我需要講兩個(gè)專題。第一就是在oracle中實(shí)現(xiàn)分頁的技術(shù)，在oracle中實(shí)現(xiàn)分頁查詢，是通過rownum關(guān)鍵字來實(shí)現(xiàn)的，rownum代表一個(gè)表中的記錄數(shù)，比如我要查詢一個(gè)表中的前１００條記錄，可以這樣寫：

select * from (select row_.*,rownum rownum_

                  from (select * from tablename) row_

                  where rownum<=100)

           where rownum_>0;

通過這句查詢我們就可以實(shí)現(xiàn)分頁查詢，并且我們可以將分頁兩個(gè)端點(diǎn)作為參數(shù)傳入語句中，這樣就可實(shí)現(xiàn)逐批地查詢了。

第二我要講講Oracle索引技術(shù)，這項(xiàng)技術(shù)在我們這個(gè)方案中是一個(gè)決定成敗的關(guān)鍵技術(shù)，索引的使用對(duì)于各種oracle調(diào)優(yōu)也是非常關(guān)鍵的。oracle采用樹形結(jié)構(gòu)的索引而且索引是有序的，我們?cè)谌粘５牟樵冎校瑢?duì)索引的使用應(yīng)該有以下５種情況：

1、  索引唯一掃描：通過主鍵或者唯一索引來查詢記錄，這種方式也是效率最高的。

2、索引范圍掃描：這種方式發(fā)生在返回多個(gè)值時(shí)，如通過ｗｈｅｒｅ　ｉｄ＞�。幔睿洹。椋洹。迹蛘叻俏ㄒ凰饕椋洌剑ВУ日Z句，范圍掃描要求返回的結(jié)果集不能太大，否則將不會(huì)使索引發(fā)揮作用。

3、   索引全掃描：oracle會(huì)按照索引的順序全部掃描該索引，類似全表掃描，效率不高。

4、  索引快速全掃描：它不按照索引順序來訪問，而是直接讀取索引塊來訪問索引數(shù)據(jù)。在這種存取方法中可以利用多塊讀功能，該種情況一般出現(xiàn)在ｓｅｌｅｃｔ　ｃｏｕｎｔ（＊）這種情況中。

５、                索引跳躍式掃描：這是９ｉ之后的新功能，一般出現(xiàn)在復(fù)合索引中，如在字段（Ａ１，Ａ２）上建立符合索引，當(dāng)執(zhí)行ｗｈｅｒｅ�。粒玻剑觯幔欤酰宀樵儠r(shí)，oracle會(huì)跳過Ａ１，直接使Ａ２使用這個(gè)符合索引。（這個(gè)功能在實(shí)踐中尚待考量）

oracle中的索引，其實(shí)是ｒｏｗｉｄ的一個(gè)邏輯名，在通過索引進(jìn)行檢索時(shí)，都是先檢索到索引代表的ｒｏｗｉｄ值，然后通過ｒｏｗｉｄ回表檢索目標(biāo)數(shù)據(jù)。如果我們要檢索的數(shù)據(jù)就在索引字段中，那么我們通過索引就能獲得數(shù)據(jù)，也就不用回表檢索了，那么效率將會(huì)更加提高，因此我們要在上面的ｓｏｕｒｃｅ，ｏｂｊｅｃｔ＿ｔｙｐｅ，ａａｃ００１字段上建立符合索引，而且將ａａｃ００１作為排序字段，我們的思路是利用索引，但是在索引上先排序，再分頁，選擇完ｒｏｗｉｄ，再回表查詢（我們這里其實(shí)不用回表再查詢了）。因此我們的游標(biāo)構(gòu)造如下：

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;在對(duì)索引排序時(shí)要注意符合索引的所有列必須都要用到，而且順序要與索引定義順序一樣，否則ｏｒｄｅｒ　ｂｙ　將會(huì)棄用索引。另外由于我們?cè)谒饕模颍铮鳎椋渖舷冗M(jìn)行了排序，因此不會(huì)出現(xiàn)分頁查詢中的邏輯讀積增的問題。所謂邏輯讀積增，就是第一次分頁讀１００條記錄加入邏輯讀是１００，那么第１０次讀邏輯讀就是１０００，但是卻只返回了９００到１０００的１００條記錄，因此對(duì)性能造成損害，合理的使用索引以及合理利用索引特性，利用了索引是有序的，所以先對(duì)索引排序分頁，在回表查詢，這樣就避免了邏輯讀積增。這個(gè)游標(biāo)的每一次的執(zhí)行計(jì)劃類似下面這樣：

select statement

 view

 count stopkey

   view

     table access by index rowｉd

       index range scan descending

可以看出這里成功的使用了ｓｔｏｐｋｅｙ內(nèi)部關(guān)鍵字，限制了每次的返回記錄數(shù)，另外每次對(duì)表的檢索都是通過索引完成的，大大提高了效率。另外在這個(gè)游標(biāo)中我還使用了ｈｉｎｔ線索/*+ordered use_nl(t,teda_ryxx)*/，通過這個(gè)線索，可以實(shí)現(xiàn)將每次的執(zhí)行計(jì)劃穩(wěn)定下來，避免不必要的硬解析。這在高可用的ＯＬＴＰ環(huán)境中是必要的。

　　通過以上這些技術(shù)的采用，成功地完成了這個(gè)比對(duì)工作，而且比對(duì)程序在數(shù)據(jù)庫中高效并且平滑的運(yùn)行，沒有對(duì)系統(tǒng)造成性能損害。特此將這次的心得與體會(huì)貢獻(xiàn)出來，供大家參考。

  本文關(guān)鍵詞：Oracle索引技術(shù)，由筆耕文化傳播整理發(fā)布。