《Oracle執(zhí)行計(jì)劃詳細(xì)解讀》課件

Oracle執(zhí)行計(jì)劃解讀

培訓(xùn)教師：謝高興

時(shí)間：2006.4.27

BEGINOracle執(zhí)行計(jì)劃解讀培訓(xùn)教師：謝高興時(shí)間SQL執(zhí)行過程1,解析SQL2,執(zhí)行3,顯示結(jié)果集4,轉(zhuǎn)換字段數(shù)據(jù)檢查安全性檢查SQL語法查詢重新書寫創(chuàng)建執(zhí)行計(jì)劃捆綁執(zhí)行計(jì)劃執(zhí)行執(zhí)行計(jì)劃讀取結(jié)果集SQL執(zhí)行過程1,解析SQL2,執(zhí)行3,顯示結(jié)果集4,轉(zhuǎn)換字執(zhí)行計(jì)劃執(zhí)行計(jì)劃:Oracle內(nèi)部的機(jī)器級(jí)代碼,決定如何訪問存儲(chǔ)器，得到需要的結(jié)果集。執(zhí)行計(jì)劃的主要內(nèi)容：訪問方式，訪問順序。執(zhí)行計(jì)劃執(zhí)行計(jì)劃:Oracle內(nèi)部的機(jī)器級(jí)代碼,決定得到執(zhí)行計(jì)劃的方式1.Explain（解釋）2.Autotrace(自動(dòng)跟蹤)3.其他工具基本格式：explain

plan

set

STATEMENT_ID='testplan'ForSelect…..(Select,insert,update等數(shù)據(jù)操作語句均可)基本使用方式：SQL>setautotraceon;(SQLPLUS中使用)得到執(zhí)行計(jì)劃的方式1.Explain（解釋）2.Autotr準(zhǔn)備：創(chuàng)建Plan_table表createtableplan_table(statement_idvarchar2(30),timestampdate,remarksvarchar2(80),operationvarchar2(30),optionsvarchar2(30),object_nodevarchar2(128),object_ownervarchar2(30),object_namevarchar2(30),object_instanceinteger,object_typevarchar2(30),optimizervarchar2(255),search_columnsinteger,idinteger,parent_idinteger,positioninteger,costinteger,cardinalityinteger,bytesinteger,other_tagvarchar2(255),partition_startvarchar2(255),partition_stopvarchar2(255),partition_idinteger,otherlong,distributionvarchar2(30));準(zhǔn)備：創(chuàng)建Plan_table表createtablepAUTOTRACESQL>setautotraceon;SQL>select*fromdual;D-XExecutionPlan----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE10TABLEACCESS(FULL)OF'DUAL'Statistics----------------------------------------------------------0recursivecalls0dbblockgets3consistentgets0physicalreads0redosize372bytessentviaSQL*Nettoclient511bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)1rowsprocessedSQL>統(tǒng)計(jì)信息執(zhí)行計(jì)劃結(jié)果數(shù)據(jù)AUTOTRACESQL>setautotraceo使用Explain1,刪除上次解析數(shù)據(jù)2,執(zhí)行解析delete

from

plan_table

where

STATEMENT_ID='testplan';explainplansetSTATEMENT_ID='testplan'forselect*fromdual;3,顯示執(zhí)行計(jì)劃selectlpad('',5*(level-1))||operationoperation,options,object_name,cost,positionfromplan_tablestartwithid=0andSTATEMENT_ID='testplan'connectbypriorid=parent_id;使用Explain1,刪除上次解析數(shù)據(jù)2,執(zhí)行解析delet第一個(gè)執(zhí)行計(jì)劃對(duì)應(yīng)SQL語句：select*fromdual;執(zhí)行計(jì)劃:第一個(gè)執(zhí)行計(jì)劃對(duì)應(yīng)SQL語句：select*from怎樣看執(zhí)行計(jì)劃執(zhí)行計(jì)劃其實(shí)是一棵樹，層次最深的最先執(zhí)行，層次相同，上面的先執(zhí)行。顯示時(shí)已經(jīng)按照層次縮進(jìn)，因此從最里面的看起。最后一組就是驅(qū)動(dòng)表。例：驅(qū)動(dòng)表

selectcount(*)

from

prm_adjustpricemain,prm_adjustprice_bdetail

wheremain.cadjpriceid=detail.CADJPRICEIDandmain.DADJPRICEDATE>=

'2004-04-18'

anddetail.CINVENTORYIDin

(selectpk_invmandocfrom

bd_invmandoc

where

bd_invmandoc.SEALFLAG='N');怎樣看執(zhí)行計(jì)劃執(zhí)行計(jì)劃其實(shí)是一棵樹，層次最深的最先執(zhí)行，層次表訪問方式散列獲?。喝頀呙瑁篟OWID訪問：讀取表的每一條記錄，順序地從第一個(gè)數(shù)據(jù)塊開始知道結(jié)尾標(biāo)志。ROWID包含記錄的數(shù)據(jù)塊號(hào)和數(shù)據(jù)塊中的偏移量。因此它是獲取一條記錄的最快的方法。使用散列算法得到符號(hào)關(guān)鍵值的來確定記錄所在的數(shù)據(jù)塊。它能減少數(shù)據(jù)讀入量，但是存在重新定位記錄的問題，只能在靜態(tài)表中使用。表訪問方式散列獲取：全表掃描：ROWID訪問：讀取表的每一條索引訪問方式快速全索引掃描：索引掃描：從索引中讀取一個(gè)或多個(gè)ROWID。索引值通常按照升序方式掃描。有些查詢可以不掃描表只掃描索引就能得到期望的查詢結(jié)果。所有查詢字段都在索引中指定;查詢返回大于索引所有記錄數(shù)的10%;進(jìn)行Count(*)操作。Oracle索引索引訪問方式快速全索引掃描：索引掃描：從索引中讀取一個(gè)或多個(gè)表連接操作11,嵌套循環(huán)連接-NESTEDLOOP兩個(gè)表，一個(gè)小的內(nèi)部表和一個(gè)外部表一般情況下速度較快，特別是中間結(jié)果集非常小的情況下速度快。外部表索引內(nèi)部表外部表表連接操作11,嵌套循環(huán)連接-NESTEDLOOP兩個(gè)表，表連接操作22,散列連接-HASHJOIN兩個(gè)表，一個(gè)較小的驅(qū)動(dòng)表和一個(gè)大表中間結(jié)果集非常大的情況下速度較快。Hash_Area_SizeRAM驅(qū)動(dòng)表大表散列訪問ROWIDRAM溢出使用臨時(shí)表空間表連接操作22,散列連接-HASHJOIN兩個(gè)表，一個(gè)較小表連接操作33,排序合并連接-MERGEJOIN兩個(gè)表都使用全表掃描，分別進(jìn)行排序，然后再合并成查詢的結(jié)果集。極少情況適合。只有包含兩個(gè)表的決大多數(shù)記錄的查詢適合。A表全表掃描B表全表掃描A表排序B表排序輸出結(jié)果集合并表連接操作33,排序合并連接-MERGEJOIN兩個(gè)表都使Step1:全表掃描對(duì)查詢影響最大的就是全表掃描-TableAccessFull.計(jì)執(zhí)行劃中所有的全表掃描都值得懷疑.除非是數(shù)據(jù)量非常小的表可能引起全表掃描的原因：表上沒有索引沒有WHERE條件對(duì)索引字段使用了內(nèi)置函數(shù)，如To(Dcredate)=‘2005-02-15’Like操作符而參數(shù)以“%”開始表記錄非常少Step1:全表掃描對(duì)查詢影響最大的就是全表掃描-TableStep2:驅(qū)動(dòng)表－1驅(qū)動(dòng)表最內(nèi)層的驅(qū)動(dòng)表是不是能在執(zhí)行過程中得到最小的中間數(shù)據(jù)集？例如：查詢銷售訂單明細(xì)，涉及三個(gè)表，訂單主表，訂單附表,存貨基本檔案表。條件中包含：訂單日期等于某天和存貨編碼等于某個(gè)值。方案一：如果以訂單主表為驅(qū)動(dòng)表，可以使用日期上的索引過濾出所有的當(dāng)天的訂單，然后關(guān)聯(lián)附表，最后再關(guān)聯(lián)到存貨檔案表，過濾出相應(yīng)存貨。訂單附表訂單主表存貨檔案表日期索引主表PK索引存貨主鍵索引Step2:驅(qū)動(dòng)表－1驅(qū)動(dòng)表最內(nèi)層的驅(qū)動(dòng)表是不是能在執(zhí)行過程Step2:驅(qū)動(dòng)表－2方案二：如果以存貨檔案為驅(qū)動(dòng)表，可以使用編碼上的索引過濾出所有存貨，然后關(guān)聯(lián)訂單附表，得到所有存貨符合條件的訂單附表記錄，最后再用訂單附表上的主表主鍵關(guān)聯(lián)主表，判斷訂單主表上的日期是否符合條件，最后得到結(jié)果集。訂單附表訂單主表存貨檔案表編碼索引存貨ID索引主表主鍵索引結(jié)論：如果按照存貨去過濾，得到訂單附表上符合條件記錄會(huì)較少，認(rèn)為方案二更優(yōu)。Step2:驅(qū)動(dòng)表－2方案二：如果以存貨檔案為驅(qū)動(dòng)表，可以使Step3:不該建的索引錯(cuò)誤索引：對(duì)于只有少數(shù)一個(gè)可能值的列，不應(yīng)該建索引。如單據(jù)狀態(tài)，單據(jù)主表上的部門人員，附表上的倉庫，庫存組織等。索引不是越多越好。數(shù)據(jù)的插入，刪除和修改都需要維護(hù)索引表，也是有成本的。建議建索引的列。所有可能用做查詢條件的日期列,客戶列；附表上的主表主鍵列，存貨列，來源單據(jù)列；基本檔案的編碼列等。Step3:不該建的索引錯(cuò)誤索引：對(duì)于只有少數(shù)一個(gè)可能值的列Step4:復(fù)合索引列順序?qū)?fù)合索引，應(yīng)將重復(fù)值少的列作為先導(dǎo)列例如訂單附表上有來源單據(jù)類型(srctype)和來源單據(jù)ID(srcid),建立索引的順序應(yīng)該是srcid,srctype.如果將srctype列作為先導(dǎo)列，可能在查詢時(shí)，沒有srcid的條件也使用這個(gè)索引掃描，其結(jié)果是掃描了大半個(gè)表，比全表掃描還慢。Step4:復(fù)合索引列順序?qū)?fù)合索引，應(yīng)將重復(fù)值少的列作為先Step5:非最優(yōu)索引對(duì)一個(gè)表存在多個(gè)索引的列作為條件，解析中可能使用了非最優(yōu)索引使用提示，指定使用某個(gè)索引(存在SQL兼容問題，不推薦)將索引盡可能多的使用條件使用內(nèi)置函數(shù)或運(yùn)算使不想使用的條件列失效。整型數(shù)據(jù)列+0字符型加trim函數(shù)例如：在進(jìn)行調(diào)撥的時(shí)候需要判斷這個(gè)倉庫中此存貨不存在沒有記帳的出庫單

Selectcount(*)fromXXXwherepk_inv=:1andstatus=0這個(gè)查詢會(huì)用到存貨索引，但是速度還是慢。但是仔細(xì)分析會(huì)發(fā)現(xiàn)，其實(shí)實(shí)際運(yùn)行中沒記帳的出庫單比例很少，如果在存貨索引上再加上狀態(tài)列，可以過濾更少的數(shù)據(jù)，速度將大大提高。Step5:非最優(yōu)索引對(duì)一個(gè)表存在多個(gè)索引的列作為條件，解析Step6:數(shù)據(jù)非均勻分布對(duì)于某些SQL可能存在時(shí)快時(shí)慢，這個(gè)即可能是數(shù)據(jù)的分布不均導(dǎo)致舉例：做銷售訂單時(shí)，去判斷此客戶是否存在未結(jié)算訂單。SQL語句：這個(gè)語句在做多數(shù)客戶的時(shí)候沒有感覺，但是在做某一兩個(gè)客戶時(shí)速度奇慢，查其執(zhí)行計(jì)劃，也使用了主表上的客戶索引。Select..Fromso_order,so_order_b,..whereso_order.pk=so_order_b.pkandccustomerid=:1and……..最后發(fā)現(xiàn)情況是這樣的：企業(yè)是做批發(fā)業(yè)務(wù)的，政策法規(guī)上不允許直接銷售給個(gè)人，內(nèi)部職工需要購買時(shí)都是按照統(tǒng)一個(gè)特定的客戶開票，因此數(shù)據(jù)庫中數(shù)據(jù)嚴(yán)重不均，此客戶的訂單數(shù)量大于總數(shù)的10%。一進(jìn)行這樣的查詢就嚴(yán)重占用數(shù)據(jù)庫資源，導(dǎo)致系統(tǒng)相應(yīng)速度慢。解決的辦法：這種行為都是現(xiàn)金交易根本不需要進(jìn)行檢查，查詢的結(jié)果永遠(yuǎn)是空。程序繞過這個(gè)檢查就行了。Step6:數(shù)據(jù)非均勻分布對(duì)于某些SQL可能存在時(shí)快時(shí)慢，這Step7:EXIST查詢基本格式例：selectcount(*)

from

prm_adjustpricemain,prm_adjustprice_bdetail

wheremain.cadjpriceid=detail.CADJPRICEIDandmain.cadjpriceidin

('1','2')

and

exists

(select

*

from

bd_invmandoc

where

bd_invmandoc.SEALFLAG='N‘

anddetail.CINVENTORYID=bd_invmandoc.pk_invmandoc);不要將主表的其他條件寫在子查詢中，這樣可能使外層查詢沒有條件而進(jìn)行全表掃描。如果想按照子查詢中的表作為驅(qū)動(dòng)表進(jìn)行查詢，需要改造成IN子句。Select..From主表where主表?xiàng)l件andexists(Select*from子表where子表?xiàng)l件and主表關(guān)聯(lián)字段＝子表PK)Step7:EXIST查詢基本格式例：selectcoStep8:IN查詢基本格式例：

selectcount(*)

from

prm_adjustpricemain,prm_adjustprice_bdetail

wheremain.cadjpriceid=detail.CADJPRICEIDandmain.cadjpriceidin

('1','2')

anddetail.CINVENTORYIDin

(selectpk_invmandoc

from

bd_invmandoc

where

bd_invmandoc.SEALFLAG='N');如果不是想按照子查詢中的表作為驅(qū)動(dòng)表進(jìn)行查詢，建議改造成EXIST子句，特別是自查詢存在較多重復(fù)數(shù)據(jù)時(shí)。Select..From主表where主表?xiàng)l件and主表關(guān)聯(lián)字段in(Select子表PKfrom子表where子表?xiàng)l件)Step8:IN查詢基本格式例：selectcount(Step9:NOTIN盡量不要使用NOTINNOTIN都可以改造成NOTEXISTSNOTIN中子查詢?nèi)绻麑?duì)某個(gè)記錄返回空，整個(gè)結(jié)果集都會(huì)為空Step9:NOTIN盡量不要使用NOTINNOTINStep10:視圖視圖不要存在視圖套視圖情況視圖中不要使用Distinct視圖中希望作為條件的關(guān)聯(lián)列和結(jié)果列，不要進(jìn)行運(yùn)算。如月份的加減。不要使用條件太復(fù)雜的視圖。Step10:視圖視圖不要存在視圖套視圖情況視圖中不要使用DStepOver當(dāng)所有的調(diào)整都無效時(shí)：那就是算法的問題！StepOver當(dāng)所有的調(diào)整都無效時(shí)：那就是算法的問題！實(shí)例采購計(jì)劃查詢時(shí)，先查詢出采購計(jì)劃，然后查詢執(zhí)行數(shù)據(jù)，再追加到采購計(jì)劃數(shù)據(jù)中。查詢執(zhí)行數(shù)據(jù)SQL如下：

select

planbill.cplanbid,sum(

po_planexe.npraysnum),sum……from

(SELECTpo_plan_b.cplanbid,po_plan_h.pk_corp,po_plan_b.cobj1id,po_plan_b.cobj2id,po_plan_b.cobj3id,po_plan_h.dstartdate,po_plan_h.denddate,aa.invclasscodeFROMpo_plan_h,po_plan_b,bd_invclaawherepo_plan_h.cplanhid=po_plan_b.cplanhidandpo_plan_h.dr=0andpo_plan_b.dr=0andaa.pk_invcl(+)=po_plan_b.cobj2idandpo_plan_h.dprocessdate<='2005-04-19')planbill,

po_planexe,

bd_invclinvclass

where

po_planexe.pk_corp=planbill.pk_corpand

po_planexe.dr=

0

and

(

po_planexe.cdeptid=planbill.cobj1idorplanbill.cobj1idis

null)

and

(

po_planexe.cinvmandocid=planbill.cobj3idorplanbill.cobj3idis

null

)

andinvclass.pk_invcl=

po_planexe.cinvclassid

and

((invclass.invclasscodelikeplanbill.invclasscode||'%'

andplanbill.invclasscodeis

not

null

)

orplanbill.cobj2idis

null

)

and

po_planexe.dstartdate>=planbill.dstartdate

and

po_planexe.denddate<=planbill.denddate

group

byplanbill.cplanbid;實(shí)例采購計(jì)劃查詢時(shí)，先查詢出采購計(jì)劃，然后查詢執(zhí)行數(shù)據(jù)，再追實(shí)例-續(xù)1各表數(shù)據(jù)關(guān)系如下：采購計(jì)劃主表采購計(jì)劃子表執(zhí)行情況表cobjid1cobjid2cobjid3部門ID存貨分類ID存貨ID存貨分類表實(shí)例-續(xù)1各表數(shù)據(jù)關(guān)系如下：采購計(jì)劃主表采購計(jì)劃子表執(zhí)行情況實(shí)例-續(xù)2查看執(zhí)行計(jì)劃如下：－成本優(yōu)化器模式下的計(jì)劃，全是全表掃描實(shí)例-續(xù)2查看執(zhí)行計(jì)劃如下：－成本優(yōu)化器模式下的計(jì)劃，全是全實(shí)例-續(xù)3第一反應(yīng)：加規(guī)則提示select

/*+rule*/planbill.cplanbid,sum…….好像有提高，只有一個(gè)FullScan了。但是這個(gè)計(jì)劃執(zhí)行起來更差，原因是：內(nèi)部驅(qū)動(dòng)表bd_invcl到執(zhí)行計(jì)劃表沒有用到任何條件，結(jié)果集是兩個(gè)表的完全關(guān)聯(lián)實(shí)例-續(xù)3第一反應(yīng)：加規(guī)則提示實(shí)例-續(xù)4看看內(nèi)部的子查詢情況SELECT

po_plan_b.cplanbid,…,aa.invclasscode

FROM

po_plan_h,

po_plan_b,

bd_invclaa

where

po_plan_h.cplanhid=

po_plan_b.cplanhidand

po_plan_h.dr=

0

and

po_plan_b.dr=

0

andaa.pk_invcl(

+

)

=

po_plan_b.cobj2id

and

po_plan_h.dprocessdate<=

'2005-04-19'

實(shí)例-續(xù)4看看內(nèi)部的子查詢情況實(shí)例-續(xù)5發(fā)現(xiàn)計(jì)劃表上相應(yīng)日期列沒有索引。子表上的主表字段也沒有索引。加上索引。CREATEINDEXPO_PLAN_H_IDX1ONPO_PLAN_H(DPROCESSDATE);CREATEINDEXIDX_PO_PLAN_B_1ONPO_PLAN_B(CPLANHID);內(nèi)部子查詢已經(jīng)沒有太大問題。但是整個(gè)查詢?nèi)匀粵]有起色。實(shí)例-續(xù)5發(fā)現(xiàn)計(jì)劃表上相應(yīng)日期列沒有索引。子表上的主表字段也實(shí)例-續(xù)6在內(nèi)部也都用上了索引，但是效果仍不好，速度照樣慢.看到執(zhí)行表使用的索引是I_MPP_PLANEXEX_4索引列（DSTARTDATE,DENDDATE）-刪除先關(guān)聯(lián)了執(zhí)行表，然后再關(guān)聯(lián)最外層的存貨分類表，對(duì)數(shù)據(jù)沒有起到過濾作用實(shí)例-續(xù)6在內(nèi)部也都用上了索引，但是效果仍不好，速度照樣慢.實(shí)例-續(xù)7仔細(xì)分析一下外圍關(guān)聯(lián)條件：

where

po_planexe.pk_corp=planbill.pk_corpand

po_planexe.dr=

0

and

(

po_planexe.cdeptid=planbill.cobj1idorplanbill.cobj1idis

null)

and

(

po_planexe.cinvmandocid=planbill.cobj3idorplanbill.cobj3idis

null

)

andinvclass.pk_invcl=

po_planexe.cinvclassid

and

((invclass.invclasscodelikeplanbill.invclasscode||'%'

andplanbill.invclasscodeis

not

null

)

orplanbill.cobj2idis

null

)

and

po_planexe.dstartdate>=planbill.dstartdate

and

po_planexe.denddate<=planbill.denddate這里的po_planexe.cdeptid=planbill.cobj1idorplanbill.cobj1idis

null和

po_planexe.cinvmandocid=planbill.cobj3idorplanbill.cobj3idis

null是無法使用執(zhí)行表上的部門和存貨索引。（部門上單獨(dú)加索引？？？）采購計(jì)劃表上有一個(gè)邏輯是：存貨和存貨分類兩個(gè)列是互斥的，且必有一個(gè)不為空.我們可以使用這個(gè)邏輯將條件分開，而不是寫在一起。and((

po_planexe.cinvmandocid=planbill.cobj3idandplanbill.cobj2idis

null

)or(po_planexe.cinvclassid=invclass.pk_invcl…..))但是，對(duì)存貨不需要存貨分類表，而對(duì)存貨分類因?yàn)榭梢圆欢x到最末級(jí)，因此必須關(guān)聯(lián)兩次存貨分類表。最后可行的解決辦法是將兩個(gè)查詢UNION達(dá)到解決問題實(shí)例-續(xù)7仔細(xì)分析一下外圍關(guān)聯(lián)條件：實(shí)例-續(xù)8先按照計(jì)劃表上有存貨的進(jìn)行查詢select

/*+rule*/planbill.cplanbid,…..from

(

SELECT

po_plan_b.cplanbid,

po_plan_h.pk_corp,

FROM

po_plan_h,

po_plan_b

where

po_plan_h.cplanhid=

po_plan_b.cplanhidand

po_plan_h.dr=

0

and

po_plan_b.dr=

0

and

po_plan_h.dprocessdate<=

'2005-04-19'

)planbill,

po_planexe

where

po_planexe.pk_corp=planbill.pk_corpand

po_planexe.dr=

0

and

(

po_planexe.cdeptid=planbill.cobj1idorplanbill.cobj1idis

null)

and

po_planexe.cinvmandocid=planbill.cobj3idandplanbill.cobj2idis

null

and

po_planexe.dstartdate>=planbill.dstartdate

and

po_planexe.denddate<=planbill.denddate

group

byplanbill.cplanbid實(shí)例-續(xù)8先按照計(jì)劃表上有存貨的進(jìn)行查詢實(shí)例-續(xù)9執(zhí)行計(jì)劃如下，看到已經(jīng)能得到比較優(yōu)化的結(jié)果。查詢時(shí)間1秒（3305行）實(shí)例-續(xù)9執(zhí)行計(jì)劃如下，看到已經(jīng)能得到比較優(yōu)化的結(jié)果。查詢時(shí)實(shí)例-續(xù)10按照存貨分類查詢的呢？按照編碼進(jìn)行l(wèi)ike查詢已證明比較慢。那么我們可以考慮建立一個(gè)存貨分類上下級(jí)關(guān)系的完全對(duì)照表，使用對(duì)照表關(guān)聯(lián)。CREATE

TABLE

temp_inv2up(pkid NUMBER(10)

NOT

NULL,//主鍵ID用處不大pk_invcl CHAR(20)

NOT

NULL,//子分類pk_upinvcl CHAR(20)

NOT

NULL,//上級(jí)分類CONSTRAINTPK_temp_inv2upPRIMARY

KEY

(pkid));CREATE

INDEXtemp_inv2up_idxinvONUAP0318.TEMP_INV2UP(PK_INVCL);CREATE

INDEXTEMP_INV_UPIDXON

TEMP_INV2UP(PK_UPINVCL);create

sequencetemp_invseqstart

with

1;//生成主鍵ID用//按照分類編碼關(guān)聯(lián)出上下級(jí)對(duì)照表數(shù)據(jù)insert

into

temp_inv2up(pkid,pk_invcl, pk_upinvcl)

selecttemp_invseq.nextval,bas.pk_invcl,up.pk_invclfrom

bd_invclbas,bd_invclup

wherebas.invclasscodelikeup.invclasscode||'%';實(shí)例-續(xù)10按照存貨分類查詢的呢？按照編碼進(jìn)行l(wèi)ike查詢已實(shí)例-續(xù)11按照存貨分類查詢:select

/*+rule*/planbill.cplanbid,sum..

from

(

SELECT

po_plan_b.cplanbid,

po_plan_h.pk_corp,

…

FROM

po_plan_h,

po_plan_b

where

po_plan_h.cplanhid=

po_plan_b.cplanhidand

po_plan_h.dr=

0

and

po_plan_b.dr=

0

and

po_plan_h.dprocessdate<=

'2005-04-19'

)planbill,

po_planexe,

temp_inv2up

where

po_planexe.pk_corp=planbill.pk_corpand

po_planexe.dr=

0

and

(

po_planexe.cdeptid=planbill.cobj1idorplanbill.cobj1idis

null)

andplanbill.cobj3idis

null

and

temp_inv2up.pk_invcl=

po_planexe.cinvclassid

and

temp_inv2up.PK_UPINVCL=planbill.COBJ2ID

and

po_planexe.dstartdate>=planbill.dstartdate

and

po_planexe.denddate<=planbill.denddate

group

byplanbill.cplanbid;實(shí)例-續(xù)11按照存貨分類查詢:實(shí)例-續(xù)12按照存貨分類查詢的執(zhí)行計(jì)劃:效果不錯(cuò)。實(shí)例-續(xù)12按照存貨分類查詢的執(zhí)行計(jì)劃:效果不錯(cuò)。實(shí)例-成果按照這種方式優(yōu)化后，分別union前分別加rule提示，查詢時(shí)間小于2秒。再去掉規(guī)則提示，讓Oracle按照成本優(yōu)化器解析，執(zhí)行時(shí)間0.297秒。代碼業(yè)務(wù)邏輯并沒有改變，執(zhí)行結(jié)果也相同。?。?！OVER!實(shí)例-成果按照這種方式優(yōu)化后，分別union前分別加rule￥1111111111111111111111111111111222222222222222222222222222222222222222222222222222222223333333333333333333333333333333333333333333333333333333333333333333344444＄§|β↓×√㎜ɡ?≥≧ɑ←‰?↓←≠￥θ￥?÷㎝??￥＄§|β↓×√㎜②￥◎…ɡ?≥≧ɑ←‰?ɡ?≥≧￥◎…ɡ?≥≧ɑ←‰?ɡ?≥≧－￥???￥＄§|β↓×√㎜??￥＄§|β↓×√㎜↓←≠￥θ￥?÷㎝②￥◎…ɡ?≥≧ɑ←‰???￥＄§|β↓×√㎜ɡ?≥≧ɑ←‰?↓←≠￥θ￥?÷㎝??￥＄§|β↓×√㎜②￥◎…ɡ?≥≧ɑ←‰?ɡ?≥≧￥◎…ɡ?≥≧ɑ←‰?ɡ?≥≧－￥???￥＄§|β↓×√㎜??￥＄§|β↓×√㎜≧ɑ←‰ɡ?≥←‰???↓←≠￥θ￥?÷㎝②￥◎…ɡ?≥≧ɑ←‰???￥＄§|β↓×√㎜ɡ?≥≧ɑ←‰?↓←≠￥θ￥?÷㎝??￥＄§|β↓×√㎜②￥◎…ɡ?≥≧ɑ←‰?ɡ?≥≧￥◎…ɡ?≥≧ɑ←‰?ɡ?≥≧－￥???￥＄§|β↓×√㎜??￥＄§|β↓×√㎜￥＄§|β↓×√㎜??￥＄§|β↓×√㎜￥＄§|β↓×√㎜??￥＄§|β↓×√㎜↓×√㎜↓←②￥←‰???←‰???←‰???←‰???←‰???←‰??↓←≠￥θ￥?÷㎝②￥◎…ɡ?≥≧ɑ←‰???￥＄§|β↓×√㎜ɡ?≥≧ɑ←‰?↓←≠￥θ￥?÷㎝??￥＄§|β↓×√㎜②￥◎…ɡ?≥≧ɑ←‰?ɡ?≥≧￥◎…ɡ?≥≧ɑ←‰?ɡ?≥≧－￥???￥＄§|β↓×√㎜??￥＄§|β↓×√㎜￥＄§|β↓×√㎜??￥＄§|β↓×√㎜￥＄§|β↓×√㎜??￥＄§|β↓×√㎜↓×√㎜↓←②￥←‰???←‰???←‰???←‰???←‰???←‰???↓←≠￥θ￥?÷㎝②￥◎…ɡ?≥≧ɑ←‰???￥＄§|β↓×√㎜ɡ?≥≧ɑ←‰?↓←≠￥θ￥?÷㎝??￥＄§|β↓×√㎜②￥◎…ɡ?≥≧ɑ←‰?ɡ?≥≧￥◎…ɡ?≥≧ɑ←‰?ɡ?≥≧－￥???￥＄§|β↓×√㎜??￥＄§|β↓×√㎜￥＄§|β↓×√㎜??￥＄§|β↓×√請(qǐng)刪除后使用，謝謝請(qǐng)刪除后使用，謝謝請(qǐng)刪除后使用，謝謝請(qǐng)刪除后使用，謝謝請(qǐng)刪除后使用，謝謝請(qǐng)刪除后使用，謝謝請(qǐng)刪除后使用，謝謝請(qǐng)刪除后使用，謝謝￥1111111111111111111111111111141dskdfibsdkbfndsldkhslhokdhsodhvkskdnvklsoifyoishigibivbisbdvibsidbvoisbvoibsoivbiosbviohsivhkjshivuhiuhfiugruysdbvslkdbvlkbokbvkozbxkjcbjkbvjkbkjvbkjccbvkjbcxzvhoisdbvibsdivbisdvknflksnlkvnsodskdfibsdkbfndsldkhslhokdhsodhvkskdnvklsoifyoishigibivbisbdvibsidbvoisbvoibsoivbiosbviohsivhkjshivuhiuhfiugruysdbvslkdbvlkbokbvkozbxkjcbjkbvjkbkjvbkjccbvkjbcxzvhoisdbvibsdivbisdvknflksnlkvnsodskdfibsdkbfndsldkhslhokdhsodhvkskdnvklsoifyoishigibivbisbdvibsidbvoisbvoibsoivbiosbviohsivhkjshivuhiuhfiugruysdbvslkdbvlkbokbvkozbxkjcbjkbvjkbkjvbkjccbvkjbcxzvhoisdbvibsdivbisdvknflksnlkvnsodskdfibsdkbfndsldkhslhokdhsodhvkskdnvklsoifyoishigibivbisbdvibsidbvoisbvoibsoivbiosbviohsivhkjshivuhiuhfiugruysdbvslkdbvlkbokbvkozbxkjcbjkbvjkbkjvbkjccbvkjbcxzvhoisdbvibsdivbisdvknflksnlkvnsodskdfibsdkbfndsldkhslhokdhsodhvkskdnvklsoifyoishigibivbisbdvibsidbvoisbvoibsoivbiosbviohsivhkjshivuhiuhfiugruysdbvslkdbvlkbokbvkozbxkjcbjkbvjkbkjvbkjccbvkjbcxzvhoisdbvibsdivbisdvknflksnlkvnsoxkbvoxlknvlkxncklvhsiudlknvknsklvnlksnvkjbsijvbksjvkskvbkjsbdvkklsvlnkclnvlknczxklnvlksndkvnksdvlkslkvlkczvlknspivhsojvposvnsknvlksnklhissoiyuregohkenklfvnosihvlmsdlvvnlknxclkvlkscnvlksdnkjvbsdkvklsndlkhsdoihfoisdnlnslkbvoishdovnlkznkjvgsoidnvposdnklvbkjsdhgoivhsonvlsnvhoisnvlnskvoisnvnsdlkhviusshoinvpnksgivhnosnvlkbxslkvnlkbxkvblkcxnvlknxlkvnoidskdfibsdkbfndsldkhslhokdhsodh42dsfdbsy384y982ythb3oibt4oy39y409705923y09y53b2lkboi2y58wy0ehtoibwoify98wy049ywh4b3oiut89u983yf9ivh98y98sv98hv98ys9f698y9v698yv98x98tb98fyd98gyd98h98ds98nt98d8genklgb4klebtlkb5ktkeirh893y89ey698vhkrnelkhgi8eyokbnkdhf98hodfhxvy78fd678t9fdu90gys98y9shihixyv78dfhvifndovhf9f8yv9onvkobkwkjfegiudsfdbsy384y982ythb3oibt4oy39y409705923y09y53b2lkboi2y58wy0ehtoibwoify98wy049ywh4b3oiut89u983yf9ivh98y98sv98hv98ys9f698y9v698yv98x98tb98fyd98gyd98h98ds98nt98d8genklgb4klebtlkb5ktkeirh893y89ey698vhkrnelkhgi8eyokbnkdhf98hodfhxvy78fd678t9fdu90gys98y9shihixyv78dfhvifndovhf9f8yv9onvkobkwkjfegiudsfdbsy384y982ythb3oibt4oy39y409705923y09y53b2lkboi2y58wy0ehtoibwoify98wy049ywh4b3oiut89u983yf9ivh98y98sv98hv98ys9f698y9v698yv98x98tb98fyd98gyd98h98ds98nt98d8gendsfdbsy384y982ythb3oibt4oy39y44345643453425gvmkbmvbmcf45643453425gvmkbmvbmcf45643453425gvmkbmvbmcf45643453425gvmkbmvbmcffgjhghkggggggggggggggggggg45643453425gvmkbmvbmcf45643453425gvmkbmvbmcfgkkkkkkkkkkkkkkkk45643453425gvmkbmvbmcf45643453425gvmkbmvbmcf45643453425gvmkbmvbmcf45643453425gvmkbmvbmcf45643453425gvmkbmvbmcfgkkkkkkkkkkkkkkkkkkkk45643453425gvmkbmvbmcf45643453425gvmkbmvbmcf45643453425gvmkbmvbmcf45643453425gvmkbmvbmcf45643453425gvmkbmvbmcf4564345344dsfdbsy384y982ythb3oibt4oy39y409705923y09y53b2lkboi2y58wy0ehtoibwoify98wy049ywh4b3oiut89u983yf9ivh98y98sv98hv98ys9f698y9v698yv98x98tb98fyd98gyd98h98ds98nt98d8genklgb4klebtlkb5ktkeirh893y89ey698vhkrnelkhgi8eyokbnkdhf98hodfhxvy78fd678t9fdu90gys98y9shihixyv78dfhvifndovhf9f8yv9onvkobkwkjfegiudsfdbsy384y982ythb3oibt4oy39y409705923y09y53b2lkboi2y58wy0ehtoibwoify98wy049ywh4b3oiut89u983yf9ivh98y98sv98hv98ys9f698y9v698yv98x98tb98fyd98gyd98h98ds98nt98d8genklgb4klebtlkb5ktkeirh893y89ey698vhkrnelkhgi8eyokbnkdhf98hodfhxvy78fd678t9fdu90gys98y9shihixyv78dfhvifndovhf9f8yv9onvkobkwkjfegiudsfdbsy384y982ythb3oibt4oy39y409705923y09y53b2lkboi2y58wy0ehtoibwoify98wy049ywh4b3oiut89u983yf9ivh98y98sv98hv98ys9f698y9v698yv98x98tb98fyd98gyd98h98ds98nt98d8gendsfdbsy384y982ythb3oibt4oy39y44556384866666gjfdghmghm56384866666gjfdghmghm

56384866666gjfdghmghm

56384866666gjfdghmghm

56384866666gjfdghmghm

56384866666gjfdghmghm

56384866666gjfdghmghm

56384866666gjfdghmghm

56384866666gjfdghmghm

56384866666gjfdghmghm

56384866666gjfdghmghm

56384866666gjfdghmghm

56384866666gjfdghmghm

56384866666gjfdghmghm56384866666gjfdghmghm56384866666gjfdghmghm56384866666gjfdghmghm56384866666gjfdghmghm56384866666gjfdghmghm56384866666gjfdghmghm56384866666gjfdghmghm56384866666gjfdghmghm56384866666gjfdghmghm56384866666gjfdghmghm56384866646￥1111111111111111111111111111111222222222222222222222222222222222222222222222222222222223333333333333333333333333333333333333333333333333333333333333333333344444＄§|β↓×√㎜ɡ?≥≧ɑ←‰?↓←≠￥θ￥?÷㎝??￥＄§|β↓×√㎜②￥◎…ɡ?≥≧ɑ←‰?ɡ?≥≧￥◎…ɡ?≥≧ɑ←‰?ɡ?≥≧－￥???￥＄§|β↓×√㎜??￥＄§|β↓×√㎜↓←≠￥θ￥?÷㎝②￥◎…ɡ?≥≧ɑ←‰???￥＄§|β↓×√㎜ɡ?≥≧ɑ←‰?↓←≠￥θ￥?÷㎝??￥＄§|β↓×√㎜②￥◎…ɡ?≥≧ɑ←‰?ɡ?≥≧￥◎…ɡ?≥≧ɑ←‰?ɡ?≥≧－￥???￥＄§|β↓×√㎜??￥＄§|β↓×√㎜≧ɑ←‰ɡ?≥←‰???↓←≠￥θ￥?÷㎝②￥◎…ɡ?≥≧ɑ←‰???￥＄§|β↓×√㎜ɡ?≥≧ɑ←‰?↓←≠￥θ￥?÷㎝??￥＄§|β↓×√㎜②￥◎…ɡ?≥≧ɑ←‰?ɡ?≥≧￥◎…ɡ?≥≧ɑ←‰?ɡ?≥≧－￥???￥＄§|β↓×√㎜??￥＄§|β↓×√㎜￥＄§|β↓×√㎜??￥＄§|β↓×√㎜￥＄§|β↓×√㎜??￥＄§|β↓×√㎜↓×√㎜↓←②￥←‰???←‰???←‰???←‰???←‰???←‰??↓←≠￥θ￥?÷㎝②￥◎…ɡ?≥≧ɑ←‰???￥＄§|β↓×√㎜ɡ?≥≧ɑ←‰?↓←≠￥θ￥?÷㎝??￥＄§|β↓×√㎜②￥◎…ɡ?≥≧ɑ←‰?ɡ?≥≧￥◎…ɡ?≥≧ɑ←‰?ɡ?≥≧－￥???￥＄§|β↓×√㎜??￥＄§|β↓×√㎜￥＄§|β↓×√㎜??￥＄§|β↓×√㎜￥＄§|β↓×√㎜??￥＄§|β↓×√㎜↓×√㎜↓←②￥←‰???←‰???←‰???←‰???←‰???←‰???↓←≠￥θ￥?÷㎝②￥◎…ɡ?≥≧ɑ←‰???￥＄§|β↓×√㎜ɡ?≥≧ɑ←‰?↓←≠￥θ￥?÷㎝??￥＄§|β↓×√㎜②￥◎…ɡ?≥≧ɑ←‰?ɡ?≥≧￥◎…ɡ?≥≧ɑ←‰?ɡ?≥≧－￥???￥＄§|β↓×√㎜??￥＄§|β↓×√㎜￥＄§|β↓×√㎜??￥＄§|β↓×√￥1111111111111111111111111111147Oracle執(zhí)行計(jì)劃解讀

培訓(xùn)教師：謝高興

時(shí)間：2006.4.27

BEGINOracle執(zhí)行計(jì)劃解讀培訓(xùn)教師：謝高興時(shí)間SQL執(zhí)行過程1,解析SQL2,執(zhí)行3,顯示結(jié)果集4,轉(zhuǎn)換字段數(shù)據(jù)檢查安全性檢查SQL語法查詢重新書寫創(chuàng)建執(zhí)行計(jì)劃捆綁執(zhí)行計(jì)劃執(zhí)行執(zhí)行計(jì)劃讀取結(jié)果集SQL執(zhí)行過程1,解析SQL2,執(zhí)行3,顯示結(jié)果集4,轉(zhuǎn)換字執(zhí)行計(jì)劃執(zhí)行計(jì)劃:Oracle內(nèi)部的機(jī)器級(jí)代碼,決定如何訪問存儲(chǔ)器，得到需要的結(jié)果集。執(zhí)行計(jì)劃的主要內(nèi)容：訪問方式，訪問順序。執(zhí)行計(jì)劃執(zhí)行計(jì)劃:Oracle內(nèi)部的機(jī)器級(jí)代碼,決定得到執(zhí)行計(jì)劃的方式1.Explain（解釋）2.Autotrace(自動(dòng)跟蹤)3.其他工具基本格式：explain

plan

set

STATEMENT_ID='testplan'ForSelect…..(Select,insert,update等數(shù)據(jù)操作語句均可)基本使用方式：SQL>setautotraceon;(SQLPLUS中使用)得到執(zhí)行計(jì)劃的方式1.Explain（解釋）2.Autotr準(zhǔn)備：創(chuàng)建Plan_table表createtableplan_table(statement_idvarchar2(30),timestampdate,remarksvarchar2(80),operationvarchar2(30),optionsvarchar2(30),object_nodevarchar2(128),object_ownervarchar2(30),object_namevarchar2(30),object_instanceinteger,object_typevarchar2(30),optimizervarchar2(255),search_columnsinteger,idinteger,parent_idinteger,positioninteger,costinteger,cardinalityinteger,bytesinteger,other_tagvarchar2(255),partition_startvarchar2(255),partition_stopvarchar2(255),partition_idinteger,otherlong,distributionvarchar2(30));準(zhǔn)備：創(chuàng)建Plan_table表createtablepAUTOTRACESQL>setautotraceon;SQL>select*fromdual;D-XExecutionPlan----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE10TABLEACCESS(FULL)OF'DUAL'Statistics----------------------------------------------------------0recursivecalls0dbblockgets3consistentgets0physicalreads0redosize372bytessentviaSQL*Nettoclient511bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)1rowsprocessedSQL>統(tǒng)計(jì)信息執(zhí)行計(jì)劃結(jié)果數(shù)據(jù)AUTOTRACESQL>setautotraceo使用Explain1,刪除上次解析數(shù)據(jù)2,執(zhí)行解析delete

from

plan_table

where

STATEMENT_ID='testplan';explainplansetSTATEMENT_ID='testplan'forselect*fromdual;3,顯示執(zhí)行計(jì)劃selectlpad('',5*(level-1))||operationoperation,options,object_name,cost,positionfromplan_tablestartwithid=0andSTATEMENT_ID='testplan'connectbypriorid=parent_id;使用Explain1,刪除上次解析數(shù)據(jù)2,執(zhí)行解析delet第一個(gè)執(zhí)行計(jì)劃對(duì)應(yīng)SQL語句：select*fromdual;執(zhí)行計(jì)劃:第一個(gè)執(zhí)行計(jì)劃對(duì)應(yīng)SQL語句：select*from怎樣看執(zhí)行計(jì)劃執(zhí)行計(jì)劃其實(shí)是一棵樹，層次最深的最先執(zhí)行，層次相同，上面的先執(zhí)行。顯示時(shí)已經(jīng)按照層次縮進(jìn)，因此從最里面的看起。最后一組就是驅(qū)動(dòng)表。例：驅(qū)動(dòng)表

selectcount(*)

from

prm_adjustpricemain,prm_adjustprice_bdetail

wheremain.cadjpriceid=detail.CADJPRICEIDandmain.DADJPRICEDATE>=

'2004-04-18'

anddetail.CINVENTORYIDin

(selectpk_invmandocfrom

bd_invmandoc

where

bd_invmandoc.SEALFLAG='N');怎樣看執(zhí)行計(jì)劃執(zhí)行計(jì)劃其實(shí)是一棵樹，層次最深的最先執(zhí)行，層次表訪問方式散列獲?。喝頀呙瑁篟OWID訪問：讀取表的每一條記錄，順序地從第一個(gè)數(shù)據(jù)塊開始知道結(jié)尾標(biāo)志。ROWID包含記錄的數(shù)據(jù)塊號(hào)和數(shù)據(jù)塊中的偏移量。因此它是獲取一條記錄的最快的方法。使用散列算法得到符號(hào)關(guān)鍵值的來確定記錄所在的數(shù)據(jù)塊。它能減少數(shù)據(jù)讀入量，但是存在重新定位記錄的問題，只能在靜態(tài)表中使用。表訪問方式散列獲取：全表掃描：ROWID訪問：讀取表的每一條索引訪問方式快速全索引掃描：索引掃描：從索引中讀取一個(gè)或多個(gè)ROWID。索引值通常按照升序方式掃描。有些查詢可以不掃描表只掃描索引就能得到期望的查詢結(jié)果。所有查詢字段都在索引中指定;查詢返回大于索引所有記錄數(shù)的10%;進(jìn)行Count(*)操作。Oracle索引索引訪問方式快速全索引掃描：索引掃描：從索引中讀取一個(gè)或多個(gè)表連接操作11,嵌套循環(huán)連接-NESTEDLOOP兩個(gè)表，一個(gè)小的內(nèi)部表和一個(gè)外部表一般情況下速度較快，特別是中間結(jié)果集非常小的情況下速度快。外部表索引內(nèi)部表外部表表連接操作11,嵌套循環(huán)連接-NESTEDLOOP兩個(gè)表，表連接操作22,散列連接-HASHJOIN兩個(gè)表，一個(gè)較小的驅(qū)動(dòng)表和一個(gè)大表中間結(jié)果集非常大的情況下速度較快。Hash_Area_SizeRAM驅(qū)動(dòng)表大表散列訪問ROWIDRAM溢出使用臨時(shí)表空間表連接操作22,散列連接-HASHJOIN兩個(gè)表，一個(gè)較小表連接操作33,排序合并連接-MERGEJOIN兩個(gè)表都使用全表掃描，分別進(jìn)行排序，然后再合并成查詢的結(jié)果集。極少情況適合。只有包含兩個(gè)表的決大多數(shù)記錄的查詢適合。A表全表掃描B表全表掃描A表排序B表排序輸出結(jié)果集合并表連接操作33,排序合并連接-MERGEJOIN兩個(gè)表都使Step1:全表掃描對(duì)查詢影響最大的就是全表掃描-TableAccessFull.計(jì)執(zhí)行劃中所有的全表掃描都值得懷疑.除非是數(shù)據(jù)量非常小的表可能引起全表掃描的原因：表上沒有索引沒有WHERE條件對(duì)索引字段使用了內(nèi)置函數(shù)，如To(Dcredate)=‘2005-02-15’Like操作符而參數(shù)以“%”開始表記錄非常少Step1:全表掃描對(duì)查詢影響最大的就是全表掃描-TableStep2:驅(qū)動(dòng)表－1驅(qū)動(dòng)表最內(nèi)層的驅(qū)動(dòng)表是不是能在執(zhí)行過程中得到最小的中間數(shù)據(jù)集？例如：查詢銷售訂單明細(xì)，涉及三個(gè)表，訂單主表，訂單附表,存貨基本檔案表。條件中包含：訂單日期等于某天和存貨編碼等于某個(gè)值。方案一：如果以訂單主表為驅(qū)動(dòng)表，可以使用日期上的索引過濾出所有的當(dāng)天的訂單，然后關(guān)聯(lián)附表，最后再關(guān)聯(lián)到存貨檔案表，過濾出相應(yīng)存貨。訂單附表訂單主表存貨檔案表日期索引主表PK索引存貨主鍵索引Step2:驅(qū)動(dòng)表－1驅(qū)動(dòng)表最內(nèi)層的驅(qū)動(dòng)表是不是能在執(zhí)行過程Step2:驅(qū)動(dòng)表－2方案二：如果以存貨檔案為驅(qū)動(dòng)表，可以使用編碼上的索引過濾出所有存貨，然后關(guān)聯(lián)訂單附表，得到所有存貨符合條件的訂單附表記錄，最后再用訂單附表上的主表主鍵關(guān)聯(lián)主表，判斷訂單主表上的日期是否符合條件，最后得到結(jié)果集。訂單附表訂單主表存貨檔案表編碼索引存貨ID索引主表主鍵索引結(jié)論：如果按照存貨去過濾，得到訂單附表上符合條件記錄會(huì)較少，認(rèn)為方案二更優(yōu)。Step2:驅(qū)動(dòng)表－2方案二：如果以存貨檔案為驅(qū)動(dòng)表，可以使Step3:不該建的索引錯(cuò)誤索引：對(duì)于只有少數(shù)一個(gè)可能值的列，不應(yīng)該建索引。如單據(jù)狀態(tài)，單據(jù)主表上的部門人員，附表上的倉庫，庫存組織等。索引不是越多越好。數(shù)據(jù)的插入，刪除和修改都需要維護(hù)索引表，也是有成本的。建議建索引的列。所有可能用做查詢條件的日期列,客戶列；附表上的主表主鍵列，存貨列，來源單據(jù)列；基本檔案的編碼列等。Step3:不該建的索引錯(cuò)誤索引：對(duì)于只有少數(shù)一個(gè)可能值的列Step4:復(fù)合索引列順序?qū)?fù)合索引，應(yīng)將重復(fù)值少的列作為先導(dǎo)列例如訂單附表上有來源單據(jù)類型(srctype)和來源單據(jù)ID(srcid),建立索引的順序應(yīng)該是srcid,srctype.如果將srctype列作為先導(dǎo)列，可能在查詢時(shí)，沒有srcid的條件也使用這個(gè)索引掃描，其結(jié)果是掃描了大半個(gè)表，比全表掃描還慢。Step4:復(fù)合索引列順序?qū)?fù)合索引，應(yīng)將重復(fù)值少的列作為先Step5:非最優(yōu)索引對(duì)一個(gè)表存在多個(gè)索引的列作為條件，解析中可能使用了非最優(yōu)索引使用提示，指定使用某個(gè)索引(存在SQL兼容問題，不推薦)將索引盡可能多的使用條件使用內(nèi)置函數(shù)或運(yùn)算使不想使用的條件列失效。整型數(shù)據(jù)列+0字符型加trim函數(shù)例如：在進(jìn)行調(diào)撥的時(shí)候需要判斷這個(gè)倉庫中此存貨不存在沒有記帳的出庫單

Selectcount(*)fromXXXwherepk_inv=:1andstatus=0這個(gè)查詢會(huì)用到存貨索引，但是速度還是慢。但是仔細(xì)分析會(huì)發(fā)現(xiàn)，其實(shí)實(shí)際運(yùn)行中沒記帳的出庫單比例很少，如果在存貨索引上再加上狀態(tài)列，可以過濾更少的數(shù)據(jù)，速度將大大提高。Step5:非最優(yōu)索引對(duì)一個(gè)表存在多個(gè)索引的列作為條件，解析Step6:數(shù)據(jù)非均勻分布對(duì)于某些SQL可能存在時(shí)快時(shí)慢，這個(gè)即可能是數(shù)據(jù)的分布不均導(dǎo)致舉例：做銷售訂單時(shí)，去判斷此客戶是否存在未結(jié)算訂單。SQL語句：這個(gè)語句在做多數(shù)客戶的時(shí)候沒有感覺，但是在做某一兩個(gè)客戶時(shí)速度奇慢，查其執(zhí)行計(jì)劃，也使用了主表上的客戶索引。Select..Fromso_order,so_order_b,..whereso_order.pk=so_order_b.pkandccustomerid=:1and……..最后發(fā)現(xiàn)情況是這樣的：企業(yè)是做批發(fā)業(yè)務(wù)的，政策法規(guī)上不允許直接銷售給個(gè)人，內(nèi)部職工需要購買時(shí)都是按照統(tǒng)一個(gè)特定的客戶開票，因此數(shù)據(jù)庫中數(shù)據(jù)嚴(yán)重不均，此客戶的訂單數(shù)量大于總數(shù)的10%。一進(jìn)行這樣的查詢就嚴(yán)重占用數(shù)據(jù)庫資源，導(dǎo)致系統(tǒng)相應(yīng)速度慢。解決的辦法：這種行為都是現(xiàn)金交易根本不需要進(jìn)行檢查，查詢的結(jié)果永遠(yuǎn)是空。程序繞過這個(gè)檢查就行了。Step6:數(shù)據(jù)非均勻分布對(duì)于某些SQL可能存在時(shí)快時(shí)慢，這Step7:EXIST查詢基本格式例：selectcount(*)

from

prm_adjustpricemain,prm_adjustprice_bdetail

wheremain.cadjpriceid=detail.CADJPRICEIDandmain.cadjpriceidin

('1','2')

and

exists

(select

*

from

bd_invmandoc

where

bd_invmandoc.SEALFLAG='N‘

anddetail.CINVENTORYID=bd_invmandoc.pk_invmandoc);不要將主表的其他條件寫在子查詢中，這樣可能使外層查詢沒有條件而進(jìn)行全表掃描。如果想按照子查詢中的表作為驅(qū)動(dòng)表進(jìn)行查詢，需要改造成IN子句。Select..From主表where主表?xiàng)l件andexists(Select*from子表where子表?xiàng)l件and主表關(guān)聯(lián)字段＝子表PK)Step7:EXIST查詢基本格式例：selectcoStep8:IN查詢基本格式例：

selectcount(*)

from

prm_adjustpricemain,prm_adjustprice_bdetail

wheremain.cadjpriceid=detail.CADJPRICEIDandmain.cadjpriceidin

('1','2')

anddetail.CINVENTORYIDin

(selectpk_invmandoc

from

bd_invmandoc

where

bd_invmandoc.SEALFLAG='N');如果不是想按照子查詢中的表作為驅(qū)動(dòng)表進(jìn)行查詢，建議改造成EXIST子句，特別是自查詢存在較多重復(fù)數(shù)據(jù)時(shí)。Select..From主表where主表?xiàng)l件and主表關(guān)聯(lián)字段in(Select子表PKfrom子表where子表?xiàng)l件)Step8:IN查詢基本格式例：selectcount(Step9:NOTIN盡量不要使用NOTINNOTIN都可以改造成NOTEXISTSNOTIN中子查詢?nèi)绻麑?duì)某個(gè)記錄返回空，整個(gè)結(jié)果集都會(huì)為空Step9:NOTIN盡量不要使用NOTINNOTINStep10:視圖視圖不要存在視圖套視圖情況視圖中不要使用Distinct視圖中希望作為條件的關(guān)聯(lián)列和結(jié)果列，不要進(jìn)行運(yùn)算。如月份的加減。不要使用條件太復(fù)雜的視圖。Step10:視圖視圖不要存在視圖套視圖情況視圖中不要使用DStepOver當(dāng)所有的調(diào)整都無效時(shí)：那就是算法的問題！StepOver當(dāng)所有的調(diào)整都無效時(shí)：那就是算法的問題！實(shí)例采購計(jì)劃查詢時(shí)，先查詢出采購計(jì)劃，然后查詢執(zhí)行數(shù)據(jù)，再追加到采購計(jì)劃數(shù)據(jù)中。查詢執(zhí)行數(shù)據(jù)SQL如下：

select

planbill.cplanbid,sum(

po_planexe.npraysnum),sum……from

(SELECTpo_plan_b.cplanbid,po_plan_h.pk_corp,po_plan_b.cobj1id,po_plan_b.cobj2id,po_plan_b.cobj3id,po_plan_h.dstartdate,po_plan_h.denddate,aa.invclasscodeFROMpo_plan_h,po_plan_b,bd_invclaawherepo_plan_h.cplanhid=po_plan_b.cplanhidandpo_plan_h.dr=0andpo_plan_b.dr=0andaa.pk_invcl(+)=po_plan_b.cobj2idandpo_plan_h.dprocessdate<='2005-04-19')planbill,

po_planexe,

bd_invclinvclass

where

po_planexe.pk_corp=planbill.pk_corpand

po_planexe.dr=

0

and

(

po_plane