JAVA编程规范V1.1(16)

JAVA编程规范V1.1

Finally { Conn.close(); ResultSet.close(); Statement.close(); }

Use where clause in SQL queries to prevent full table scan Use varchar2 over varchar & char Avoid joining more than 3 tables

Use Oracle Sequence to generate unique ID Avoid fetching too many records at one time

List the driving table (table that returns the least number of records from the

joined table) last in the FROM clause Do not use != Use > or < as applicable

Use operators with the AND clause instead of LIKE if possible NO …

LIKE %ABC% or … LIKE _ABC%

3.3 SQL性能指导

3.3.1

SQL 级别

JAVA编程规范V1.1

ROF M W…ERE Hvluae =>:B nd iVraabil effEcieit:nT h eECDOD fuEcnion tacnof ent be ues dot rplace meluitle qperiue so nhets ametable . onsiCde usingr DECDO Eot compile garggateestat sitiscfor ex pessrois whinchare to coomlpxe ofr a ROGU PY Bcalus. Yoeuc anag rggetea rngaseb using tyhesig nunfctino.SELEC Tm xavalue( F)RMO …Exmpale Qeur yo toucntnumbe ofr ucstmeor isn thee areggr ops, F_aue variagbelre trusnt hea g ef othe cutsoer mni eaysr Infeicient:fSE LETCRFM OHEREWFR MO WERHE UINNO ESLETC RFOM WHER EEficfinet: SEECTL ‘>4’ 0ounc(*)tcus omtrs aeeg 4>0

<‘5’ 2cunt(o)*cutsoersmage < 25 uctosmrs ege aebtwen 25e nad40 UNONISE LETC 25-40‘’ ocut(*)n

de(cde(soin(gf_ag(ega)-e5)2,-11,,))0un er_2d5 ,sm(decode(usgi(n_afe(agg)-e5),-2,0,1 edocd(sigen(f_gae(gea-4)),-0,1,10)) )“25_o_4t”0,su md(cedeo(igns(fag_eage()-04,1)1,,)0)ove _40rFROM3. 31.2.FROM C lasueuscomtrse

List het rdiing vabtl et(abe thatl rteursnt he laet nsmbue rf rocoers frdo mth ejoiendta be) llas itn hteFRO M cluae.sxEmpla

e Eqalu nmbur oe frwosi ne ca tabhle table_neo=0,2000 rws oretrnudet alb_eto=w2 rows retruned Ienffiicnet:SELE TC cuno(*)t FRO tMalebt_ow table_,oenWH EER emp1>0;027 se cnods otcom lpet Eeficfeitn :SEELC Tountc*()F OMRt blaeone,_ abtl_tewoWHER Eemp10>; 1 se0codn toc omlpeet18/62

JAVA编程规范V1.1

.33..31WHREE lCusaeSepifcy sa mayncol umn ssapossib l ein ht weehre causl. Thereeofre,fewer r osw fro mte hatleb re raeurntd.e Aways slecpiyfkey filedv alus (eif knon) wi WHEnR sEtaementt Use csre in ordearign prdicatees .Oredr anca ffcetp rfeormanc. Ie tsi gneerlayl orem ffiecint etoput tehm so tseeltice vocndtioins frsti.Se oncdly ,redpicaets hsoudl rsembleeth eodering rf otehr ciroerpsodnngif eils id ntehtab e. Exalmlp eEp m 1>00 causel etrurn 1s00 ro0ws Det p=333 114509 cluas returens 10r ows nIffeciietn :SELCET… FRM O …WHRE Eepm> 10 0ND depAt= 331130549Effcieni: tELSCET …

RFOM …W HREE epd = 333t11409 AND emp >5 001S eondacyrExam pl eTalebco lunm ordre= nmae, ddaesr, spohen hponean dna me abtesl retrn uelraivelt yteh sma enmube or fowrInsefifceint:S EECTL…

FOMRWHEER

…pohen= 3331044159AND ame n ‘Joe=Mug ’ Efficient SEL:CET FRM WHORE … E…n ae m=‘ oe JMu’gAN phDone= 3 33110449

5Mke asru etht taale dbta daeinifitnos atcm. Iht is viat tlaht bothd taait ems eibg comnpaedrh vae ht saeemd taadef iitionns; ohetriwse rspeonse tmeis dterioraet.e D(o not ompacre ani tnegr nueemri tcoa

E xampl Eepm ieFld =numric deaattype ,S SN Feidl = harccatr deataytpeIefficnint:e ESELT C…

ROMFHERW

…Eepm= ss n

912/6

JAVA编程规范V1.1

decimla unmreci)Efficeitn :SELET CFOMR …… WHERE T_Coha(emr) p =ssnAvio dsinu gocnctanetioa.nCo nctaeatnoinal olswc oumn lavule tso b etsungrt ogtehre.T ish wil lcuae s aflult abl scaen (.e. iprferomacn wile ledteirrota). Plaeecf ucntoni ss(ch ausE xmpaleSY DASTE )n WiHEER calseu sonthe rig t hhad sndie f ote hneIffciinte:S EELC T… opreaorts F.ROM …WHE RES YDSAET< cr ate_deatEfficieetn: ELSCT … FERM O WHER… crEetaeda_et >S YSDAET3.3.1. 4 OJIN SEaxmplCeutsmoe traleb= 0,100 t0otlar owsSPEICYF TH ERIVIDGNTAB E LLSTAIN HET RFO MCAUSL.E

epD t =,0010 total rwo Isnfeifceitn :ESLET … FRCM Oedtp ,ustcoer WmEHERcus omertd.ept =dpetna.m eEffiiect: SnELEC T …ROMF ucsoter, mdpet WEHREdep t.nam = cuseomer.tdetp

IWHIN TTHEW EHR ELAUCES, HT DERVINI TGBLAE SHOLDUB E HT EFIRTST ABELIN HTE JON IODERR, .IE.O THNE EFT LISED.TH EDIRVIGNT BLEAS OULDHBE THE NOE TAH TREUTRN STEHL EST NUMBEARO RFOW.

SnclIude ll tahen ecsseay rolcunsmi nhe tojn. Oiherwtise unw,aten drow masybe nciluded ,orrows wil l e mbluipltie.d

2/02

6

JAVA编程规范V1.1

Kee pin mnd itehke y ifedl tshta edifn edsiinttc rwos.T se usingt datawith umtlpli vealeus of erachke yfie d.l Jionss ouhdl lwaay usseindexe tso attan idaquaee perftrmoacen .Oe nay to wcaomcliph sthsiis jo in on sa anm kyeyfiel d ass opsible.sAv io dth ese ofu ouet joirns A. ounter jon iallwso rowst obeinc lued even dift eh yhve an oatmchi n ht oehet rtalbe. TheMI US oNepatrroca nbe

use to effidcently pirfeor an anti-mojin.Th e MNIS cUmmonadre tunr alslrows rof mneor eust lest execptt hso efunodin an thor reesult est H.owveer ,ecaho f ht eresltu stse mustbe id netiac wliht rgeardt o te humner bndat ye opf olumcns.A void siun mgor ehtan hrte etales inba jo n.iH wovere, whena trhee abtl jeoi nsi rquirede,s lecte tehin etrescton itabe aslt eh rdivnigt abl eExmpaleS LEEC Tusnare, mfrsitnmae,date of__birhtFROM mpelyoee sIMUNS SEELTC cnotcta_ursnmae,c noatt_cirsfnamet, do FbORM csutoersmExaple Inefmifienc:tS LECET F…ORMA NDAN ADN EfficDine: StEELC T… FORMAN AND catDgeory , locaCiotnL, em lpyoe eE E.mpneo > =100 0.Eempco< =2000 E .acnot= C .ctanoWHE ERe mlopee yE categ,ry oC,l coaion Ctca.tno= E.c atno L.olcn =.Elon cE.menop>= 1 000 Ee.pmco < =002 L0WH ER

AND 3E3...5 1NUINSOxaEmple

E.loc = L.nolncUs UNION inested of aRO hen weevrp osiblesIenfficiet: nSLEECTs laar yFOR EMM WHPEE emRp_on =‘23154 O’ Rempn_ame = ‘MSIH’

2T12/

6 …… 此处隐藏：3420字，全部文档内容请下载后查看。喜欢就下载吧 ……