Sqlselectp.pk_invbasdoc,p.pk_calbody,p.pk_invmandoc,b.invcode,b.invnamefrombd_invbasdocb,bd_producepwherep.pk_invbasdoc(+)=and(p.pk_invbasdocin('0001AA WM16','0001AA WM1V','0001AA 這里的外連接是沒(méi)必要的,加了外連接,oracle2fulltable+hashouterjoin,把外連接selectp.pk_invbasdoc,p.pk_calbody,p.pk_invmandoc,b.invcode,b.invnamefrombd_invbasdocb,bd_producepwherep.pk_invbasdoc=and(p.pk_invbasdocin('0001AA WM16','0001AA WM1V','0001AA selectpk_invmandocfrombd_invmandocwherepk_corp='1363'and(selectfromwherebd_invbasdoc.pk_invbasdoc=bd_invmandoc.pk_invbasdocandbd_invbasdoc.pk_invbasdocin 83', 01', 72',' 72','000178100000000FPAMV','0001781000000008Z3ZC','000178100000003L8AVA', 63',,selectfromwherepk_corp='1363'wherepk_invbasdocin(5011(元祖SQL:selectCOUNT(*)fromic_general_hh,ic_general_bbwhereh.cgeneralhid=b.cgeneralhidand(h.cwarehouseid='1008A110000000004ZB8'ANDh.dr=0AND(h.cbilltypecode='40'orh.cbilltypecode='45'orh.cbilltypecode='46'orh.cbilltypecode='47'or='4A'orh.cbilltypecode='4C'orh.cbilltypecode='4D'orh.cbilltypecode='4E'orh.cbilltypecode='4F'orh.cbilltypecode='4I'orh.cbilltypecode='4O')ANDb.dr=0andh.fbillflag='2'andb.dbizdate>='2009-06-01'andb.dbizdate<='2009-06-30')selectCOUNT(*)fromic_general_hh,ic_general_bbwhereh.cgeneralhid=b.cgeneralhidand(h.cwarehouseid='1008A110000000004ZB0'ANDh.dr=0AND(h.cbilltypecode='40'orh.cbilltypecode='45'orh.cbilltypecode='46'orh.cbilltypecode='47'or='4A'orh.cbilltypecode='4C'orh.cbilltypecode='4D'orh.cbilltypecode='4E'orh.cbilltypecode='4F'orh.cbilltypecode='4I'orh.cbilltypecode='4O')ANDb.dr=0andh.fbillflag='2'andb.dbizdate>='2009-06-01'andb.dbizdate<='2009-06-30')selectCOUNT(*),cwarehouseidfromic_general_hh,ic_general_bbwhereh.cgeneralhid=b.cgeneralhidand(h.cwarehouseidin()ANDh.dr=0AND(h.cbilltypecode='40'orh.cbilltypecode='45'orh.cbilltypecode='46'orh.cbilltypecode='47'or='4A'orh.cbilltypecode='4C'orh.cbilltypecode='4D'orh.cbilltypecode='4E'orh.cbilltypecode='4F'orh.cbilltypecode='4I'orh.cbilltypecode='4O')ANDb.dr=0andh.fbillflag='2'andb.dbizdate>='2009-06-01'andb.dbizdate<='2009-06-30')groupbyNC中提供了很多批處理的方法(特別是批量的插入和更新方法),利用批處理能減少程序與盡量使用where例如 groupbypk_accsubjhaving pk_accsubj,sum(localcreditamount)fromufnczz.gl_detailwherepk_accsubj='1017AA1000000000069S'groupbyHAVING中的條件一般用于對(duì)一些集合函數(shù)的比較,COUNT()等等.除此而外,一般的條件應(yīng)該寫在WHERE子句中下面的語(yǔ)句導(dǎo)致數(shù)據(jù)庫(kù)的cpu100%sqlselect2frombd_bdinfoawherepk_bdinfo(selectfrombd_subjassb,bd_accsubjaccwhereb.pk_accsubj=and(acc.pk_glorgbook= selecta.a.frombd_bdinfoa,(selectdistinctb.pk_bdinfofrombd_subjassbjoinbd_accsubjacconb.pk_accsubj=acc.pk_accsubjwhereacc.pk_glorgbook='111')bwherea.pk_bdinfoselectmax(iauditsequence)iauditsequence,whenbauditbatchflag='Y'thenendfromv_ia_inoutledgervwhereiauditsequence0andfdispatchflag=0andnpriceisnotand(v.badjustedItemflag<'Y'orv.cbilltypecode<'I9')and(v.cbiztypeidisnullorcsourcebilltypecode<'32')andv.crdcenterid||v.cinventoryidingroupbywhenbauditbatchflag='Y'then改成selectmax(iauditsequence)iauditsequence,whenbauditbatchflag='Y'thenendfromv_ia_inoutledgervwhereiauditsequence0andfdispatchflag=0andnpriceisnotand(v.badjustedItemflag<'Y'orv.cbilltypecode<'I9')and(v.cbiztypeidisnullorcsourcebilltypecode<'32')andv.cinventoryidin'1035V61000000000PT5J')andgroupbywhenbauditbatchflag='Y'thenselectDISTINCT(fa_cardhistory.localoriginvalue-fa_cardhistory.accudep),(fa_cardhistory.localoriginvalue-fa_cardhistory.accudep-fa_cardhistory.depamount,fa_cardhistory.Fk_category,fa_cardhistory.card_numfromwherefa_cardhistory.pk_corp='1018'andfa_cardhistory.laststate_flag=0andfa_cardhistory.fk_accbook='andfa_cardhistory.newasset_flagin(10,11)andfa_cardhistory.dr=0andfa_cardhistory.reduce_flag=andfa_cardhistory.Fk_categoryin('0001A11000000009J532')andAccyear||Period>='201003'andAccyear||Period<=accyearperiodaccyear=’2010’and使用notinnotinnotnullselectdistinctinvbas.invcode,invbas.invnamefromcm_product cm_product_bb, bd_invbasdocinvbaswherea.pk_product=andb.pk_produce=rt.pk_produce(+)andb.pk_produce=p.pk_produceandb.pk_invbasdoc=and(a.pk_corp='1002'anda.pk_calbody=' BD'anda.caccountyear='2009'anda.caccountmonth='09'anda.dr=0andb.dr=0andp.isuseroute='Y'andrt.pk_produceisnullb.pk_producenotin(selectfrombd_bom_bbwhereb.zxlx=andb.pk_corp=andb.gcbm= andb.dr=selectdistinctinvbas.invcode,invbas.invnamefromcm_product cm_product_bb, bd_invbasdocinvbaswherea.pk_product=andb.pk_produce=rt.pk_produce(+)andb.pk_produce=p.pk_produceandb.pk_invbasdoc=and(a.pk_corp='1002'anda.pk_calbody=' BD'anda.caccountyear='2009'anda.caccountmonth='09'anda.dr=0andb.dr=0andp.isuseroute='Y'andrt.pk_produceisnullb.pk_producenotin(selectfrombd_bom_bbwhereb.zxlx=andb.pk_corp=andb.gcbm= andb.dr=0andpk_produceisnot輔助余額表帶數(shù)據(jù)權(quán)限查詢(V55以前版本) 原始語(yǔ)句:客戶反映查詢輔助帳需要15分鐘左右deletefromufnc5610.glTMP_assORAwhereexists(selectpk_fromufnc5610.gl_valuewherechecktype= valueid=a andcheckvaluenotin(selectdistinctpower.resource_data_id ufnc5610.sm_user_roleurolewhereu.cuserid=andu.cuserid=urole.cuseridandresource_data_idisnotnullandurole.pk_corp='1033'andurole.pk_role=power.pk_typecode=1and(power.pk_org='1033' mon_powerin('y',修改后deletefromufnc5610.glTMP_assORAwhereexists(selectpk_fromufnc5610.gl_valuewherechecktype=andvalueid=aandcheckvalueisnotnullandcheckvaluenotin(selectdistinct ufnc5610.sm_user_roleurolewhereu.cuserid=andu.cuserid=urole.cuseridandresource_data_idisnotnullandurole.pk_corp='1033'andurole.pk_role=power.pk_typecode=1and(power.pk_org='1033' mon_powerin('y',11.不要在子查詢中主查詢的條updateset ='2010-01-26(selectnvl(ia_bill_b.nsettledretractnum,0)+b.nnumberfromv_ia_inoutledgerbwhereia_bill_b.cbill_bid=b.cadjustbillitemidandb.cbillid='1035V61000000008XXEQ'andb.nnumber<0andia_bill_b.dr=0)whereexists(selectfromv_ia_inoutledgerwhereia_bill_b.cbill_bid=b.cadjustbillitemidandb.cbillid='1035V61000000008XXEQ'andb.nnumber<0andia_bill_b.dr=0)updatesetts='2010-01-26(selectnvl(ia_bill_b.nsettledretractnum,0)+b.nnumberfromia_bill_bbwhereia_bill_b.cbill_bid=b.cadjustbillitemidandb.cbillid='1035V61000000008XXEQ'andb.nnumber< andia_bill_b.dr=)whereexists(selectfromv_ia_inoutledgerwhereia_bill_b.cbill_bid=b.cadjustbillitemidb.cbillid=andb.nnumber<)andia_bill_b.dr= setts='2009-09-2511:24:05',fstatusflag=7wheredr=0andctypecode<>'5A'andfstatusflag!=7andcbillidin(selectfromto_bill_b(selectcbillid,count(*)numfromto_bill_bgroupby)wherea.cbillid=b.cbillidb.num=anda.frowstatuflag=andcbill_bidinCYHF6',CYRND',CZ6W4',1000多秒，現(xiàn)在需要你改一下，改成下面的寫法1s以內(nèi) setts='2009-09-2511:24:05',fstatusflag=7wheredr=0andctypecode<>'5A'andfstatusflag!=7andcbillidin(SELECTCBILLIDFROMTO_BILL_BWHERECBILLIDINselectcbillidfromto_bill_bcbill_bidinCY8UJ',CYHF6',CYRND',CZ6W4', 16.update的時(shí)候注意些where條件,避免不必要的更新和造成數(shù)據(jù)錯(cuò)誤(oracle)update的時(shí)候注意些where條件,避免不必要的更新和造成數(shù)據(jù)錯(cuò)誤(oracle)updatetablesetcolumn=(casewhencode=’‘ thenvalueelsecolumnend);updatetablesetcolumn=..whereUpdatetable1setcol=(selectcolfr