Oracle之SQL语句性能优化

148OracleSQL1.ORACLEORACLE3:a.RULE()b.COST()c.CHOOSE(),init.oraOPTIMIZER_MODE,RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS.SQL(session).(CBO,Cost-BasedOptimizer),analyze,(objectstatistics).(CHOOSE),analyze.tableanalyze,CBO,,RULE.,ORACLECHOOSE,(fulltablescan),CHOOSE,.2.TableORACLE:a..ORACLE(databaseblock).b.ROWIDROWID,,,ROWID..ORACLE(INDEX)(ROWID).ROWID,.3.SQLSQL,,ORACLESQL.SGA(systemglobalarea)(sharedbufferpool).,SQL(),,ORACLE.ORACLESQL.ORACLE(cachebuffering),.init.ora,,,.ORACLESQL,ORACLE.,ORACLE,,SQL(,).:A.:.:248SELECT*FROMEMP;SELECT*fromEMP;Select*FromEmp;SELECT*FROMEMP;B.::Jacksal_limitprivatesynonymWork_citypublicsynonymPlant_detailpublicsynonymJillsal_limitprivatesynonymWork_citypublicsynonymPlant_detailtableownerSQL.SQLselectmax(sal_cap)fromsal_limit;privatesynonym-sal_limit,selectcount(*0fromwork_citywheresdesclike'NEW%';publicsynonym-work_cityselecta.sdesc,b.locationfromwork_citya,plant_detailbwherea.city_id=b.city_idjackprivatesynonymplant_detailjill,.C.SQL(bindvariables)SQL(),(,)a.selectpin,namefrompeoplewherepin=:blk1.pin;selectpin,namefrompeoplewherepin=:blk1.pin;b.selectpin,namefrompeoplewherepin=:blk1.ot_ind;selectpin,namefrompeoplewherepin=:blk1.ov_ind;4.()ORACLEFROM,FROM(drivingtable).FROM,.ORACLE,.,(FROM),348(FROM),.:TAB116,384TAB21TAB2()selectcount(*)fromtab1,tab20.96TAB2()selectcount(*)fromtab2,tab126.093,(intersectiontable),.:EMPLOCATIONCATEGORY.SELECT*FROMLOCATIONL,CATEGORYC,EMPEWHEREE.EMP_NOBETWEEN1000AND2000ANDE.CAT_NO=C.CAT_NOANDE.LOCN=L.LOCNSQLSELECT*FROMEMPE,LOCATIONL,CATEGORYCWHEREE.CAT_NO=C.CAT_NOANDE.LOCN=L.LOCNANDE.EMP_NOBETWEEN1000AND20005.WHEREORACLEWHERE,,WHERE,WHERE.:(,156.3)SELECTFROMEMPE448WHERESAL50000ANDJOB=MANAGER'AND25(SELECTCOUNT(*)FROMEMPWHEREMGR=E.EMPNO);(,10.6)SELECTFROMEMPEWHERE25(SELECTCOUNT(*)FROMEMPWHEREMGR=E.EMPNO)ANDSAL50000ANDJOB=MANAGER';6.SELECT*count(*)SELECTCOLUMN,SQL*'.,.,ORACLE,'*',,.7.SQL,ORACLE:SQL,,,.,,ORACLE.,03420291.1()SELECTEMP_NAME,SALARY,GRADEFROMEMPWHEREEMP_NO=342;SELECTEMP_NAME,SALARY,GRADE548FROMEMPWHEREEMP_NO=291;2()DECLARECURSORC1(E_NONUMBER)ISSELECTEMP_NAME,SALARY,GRADEFROMEMPWHEREEMP_NO=E_NO;BEGINOPENC1(342);FETCHC1INTO,..,..;..OPENC1(291);FETCHC1INTO,..,..;CLOSEC1;END;3()SELECTA.EMP_NAME,A.SALARY,A.GRADE,B.EMP_NAME,B.SALARY,B.GRADEFROMEMPA,EMPBWHEREA.EMP_NO=342648ANDB.EMP_NO=291;Pro*CARRAYSIZE,,2008.DECODEDECODE.:SELECTCOUNT(*)SUM(SAL)FROMEMPWHEREDEPT_NO=0020ANDENAMELIKESMITH%';SELECTCOUNT(*)SUM(SAL)FROMEMPWHEREDEPT_NO=0030ANDENAMELIKESMITH%';DECODESELECTCOUNT(DECODE(DEPT_NO,0020,'X',NULL))D0020_COUNT,COUNT(DECODE(DEPT_NO,0030,'X',NULL))D0030_COUNT,SUM(DECODE(DEPT_NO,0020,SAL,NULL))D0020_SAL,SUM(DECODE(DEPT_NO,0030,SAL,NULL))D0030_SALFROMEMPWHEREENAMELIKESMITH%';748,DECODEGROUPBYORDERBY.9.,,():SELECTNAMEFROMEMPWHEREEMP_NO=1234;SELECTNAMEFROMDPTWHEREDPT_NO=10;SELECTNAMEFROMCATWHERECAT_TYPE=RD';3:SELECTE.NAME,D.NAME,C.NAMEFROMCATC,DPTD,EMPE,DUALXWHERENVL(X',X.DUMMY)=NVL(X',E.ROWID(+))848ANDNVL(X',X.DUMMY)=NVL(X',D.ROWID(+))ANDNVL(X',X.DUMMY)=NVL(X',C.ROWID(+))ANDE.EMP_NO(+)=1234ANDD.DEPT_NO(+)=10ANDC.CAT_TYPE(+)=RD';10.(ROWID)DELETEFROMEMPEWHEREE.ROWID(SELECTMIN(X.ROWID)FROMEMPXWHEREX.EMP_NO=E.EMP_NO);11.TRUNCATEDELETE,,(rollbacksegments).COMMIT,ORACLE()TRUNCATE,.,.,.12.COMMIT,COMMIT,,COMMIT:COMMIT:a..b.c.redologbufferd.ORACLE34369black_snail(2003-9-719:16:56)DECODENULLSUMNULLNULL--NULL,SUM(NULL)=NULLNULL,SUM()NULLSQL948menlion(2003-9-412:38:01)DECODENULLSUMNULLNULLSELECTCOUNT(DECODE(DEPT_NO,0020,'X',NULL))D0020_COUNT,COUNT(DECODE(DEPT_NO,0030,'X',NULL))D0030_COUNT,SUM(DECODE(DEPT_NO,0020,SAL,0))D0020_SAL,SUM(DECODE(DEPT_NO,0030,SAL,0))D0030_SALFROMEMPWHEREENAMELIKESMITH%';13.,count(*)count(1),,.COUNT(EMPNO)14.WhereHAVINGHAVING,HAVING.,.WHERE,.::SELECTREGIONAVG(LOG_SIZE)FROMLOCATIONGROUPBYREGIONHAVINGREGIONREGION!=SYDNEY'ANDREGION!=PERTH'SELECTREGIONAVG(LOG_SIZE)FROMLOCATIONWHEREREGIONREGION!=SYDNEY'ANDREGION!=PERTH'GROUPBYREGION15.SQL,.:SELECTTAB_NAMEFROMTABLESWHERETAB_NAME=(SELECTTAB_NAMEFROMTAB_COLUMNSWHEREVERSION=604)ANDDB_VER=(SELECTDB_VERFROMTAB_COLUMNSWHEREVERSION=604)1048SELECTTAB_NAMEFROMTABLESWHERE(TAB_NAME,DB_VER)=(SELECTTAB_NAME,DB_VER)FROMTAB_COLUMNSWHEREVERSION=604)UpdateColumn::UPDATEEMPSETEMP_CAT=(SELECTMAX(CATEGORY)FROMEMP_CATEGORIES),SAL_RANGE=(SELECTMAX(SAL_RANGE)FROMEMP_CATEGORIES)WHEREEMP_DEPT=0020;:UPDATEEMPSET(EMP_CAT,SAL_RANGE)=(SELECTMAX(CATEGORY),MAX(SAL_RANGE)FROMEMP_CATEGORIES)WHEREEMP_DEPT=0020;114816.SQL.SELECTH.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC,COUNT(*)FROMHISTORY_TYPET,EMPE,EMP_HISTORYHWHEREH.EMPNO=E.EMPNOANDH.HIST_TYPE=T.HIST_TYPEGROUPBYH.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC;.FUNCTIONLOOKUP_HIST_TYPE(TYPINNUMBER)RETURNVARCHAR2ASTDESCVARCHAR2(30);CURSORC1ISSELECTTYPE_DESCFROMHISTORY_TYPEWHEREHIST_TYPE=TYP;BEGINOPENC1;FETCHC1INTOTDESC;CLOSEC1;RETURN(NVL(TDESC,'?'));END;FUNCTIONLOOKUP_EMP(EMPINNUMBER)RETURNVARCHAR2ASENAMEVARCHAR2(30);1248CURSORC1ISSELECTENAMEFROMEMPWHEREEMPNO=EMP;BEGINOPENC1;FETCHC1INTOENAME;CLOSEC1;RETURN(NVL(ENAME,'?'));END;SELECTH.EMPNO,LOOKUP_EMP(H.EMPNO),H.HIST_TYPE,LOOKUP_HIST_TY