SQL_PLSQL培训

【网学网提醒】：网学会员鉴于大家对十分关注，论文会员在此为大家搜集整理了“SQL_PLSQL培训”一文，供大家参考学习

    SQLPL/SQL语言基础
    参考资料
    SQLReference:语法及函数参考ErrorMessage:出错信息SQLPlusGuideandReferencePL/SQLUser’sGuideandReferenceApplicationDeveloper’sGuide–Fundamentals:开发ORACLE应用的一般性指导SuppliedPL/SQLPackagesReference：PL/SQL内置Package参考
    SQL基础
    历史
    Dr.E.F.Codd于1970年在ACM(AssociationOfComputerMechanism)月刊上发表了"ARelationalModelofDataforLargeSharedDataBanks",建立了关系模型。1979年,RelationalSoftware,Inc.(现在的OracleCorporation)发布了第一个SQL商业应用。
    标准
    SQL-92:Entry,Transitional,Intermediate,andFullCoreSQL-99参见SQLReference附录B：OracleandStandardSQL
    BasicElementsofOracleSQL
    Literals(Text,Integer,Number,Interval)DatatypesFormatModelsNullsPseudocolumns[sju:dou];CommentsDatabaseObjectsSchemaObjectNamesandQualifiersReferringtoSchemaObjectsandParts
    Phil
    第1页
    12/3/2010
     Literals
    Thetermsliteralandconstantvaluearesynonymousandrefertoafixeddatavalue.Text’jack’’ssword’length<4000IntegerNumber38digitsofprecision.Intervalspecifiesaperiodoftime
    Datatypes
    Built-inDatatypesUser-definedtype
    Built-inDatatypes
    VARCHAR2(size)NVARCHAR2(size)NUMBER(p,s)LONGDATERAW(size)LONGRAWROWIDUROWID[(size)]CHAR(size)NCHAR(size)CLOBNCLOBBLOBBFILE批注[phil2]:Therearetwocomparisonruleswhencomparingcharactervalues:blank-paddedcomparisonsemanticsandnonpaddedcomparisonsemantics.Blank-paddedcomparisonisappliedinCHAR/NCHAR,textliterals.批注[phil1]:Specialskills:thewhereclauselike‘wherecollike:x1||’%’areinefficient,thereindexovercolumncolwillnotbeapplied,use‘wherecolbetween:x1and:x1||chr(255)instead.
    MoreinformationaboutOracledatatype,seeSQLReferenceorServerApplicationDeveloper’sGuideROWID:(extendedfmt:OOOOOO-FFF-BBBBBB-RRR;restrictedfmt:BBBBBBBB.RRRR.FFFF,useDBMS_ROWID.ROWID_TO_RESTRICTEDtomaketheconversion)
    DataConversion
    ImplicitDataConversionExplicitDataConversionTogetmoreinformationaboutexplicitdataconversion,seeSQLReference,frompage2-35‘Dataconversion’topage2-53;orseethereferencesofrelevantoraclefunctions.
    NULL
    Totestfornulls,useonlythecomparisonoperatorsISNULLandISNOTNULL.Ifyouuseanyotheroperatorwithnullsandtheresultdependsonthevalueofthenull,theresultisUNKNOWN.
    Phil第2页12/3/2010
     Pseudocolumns
    CURRVALandNEXTVALLEVELROWIDROWNUM
    Comment
    Hint
    Theoptimizerusesthesehintsassuggestionsforchoosinganexecutionplanforthestatement.
    DatabaseObjects
    SchemaobjectsNonschemaobjects
    Schemaobjects
    Aschemaisacollectionoflogicalstructuresofdata,orschemaobjects.Aschemaisownedbyadatabaseuserandhasthesamenameasthatuser.Eachuserownsasingleschema.Schemaobjectscanbecreated
    andmanipulatedwithSQLandincludethefollowingtypesofobjects:ClustersDatabaselinksDatabasetriggersDimensionsExternalprocedurelibrariesIndex-organizedtablesIndexes.DatabaseObjectsIndextypesJavaclasses,Javaresources,JavasourcesMaterializedviews/snapshotsMaterializedviewlogs/snapshotlogsObjecttablesObjecttypesObjectviewsOperatorsPackages
    Phil第3页12/3/2010
    批注[phil3]:ThenamingrulesofobjectsaredescribedinSQLReference,page2-72.Namespace.
     SequencesStoredfunctions,storedproceduresSynonymsTablesViews
    Nonschemaobjects
    ContextsDirectoriesProfilesRolesRollbacksegmentsTablespacesUsers
    NamingRules
    NamespaceWithinanamespace,notwoobjectscanhavethesamenameDoublequotesContainspacesBecasesensitiveBeginwithacharacterotherthananalphabeticcharacter,suchasanumericcharacterContaincharactersotherthanalphanumericcharactersand_,$,and#Beareservedword
    Operators
    UnaryandBinaryOperatorsPrecedenceArithmeticOperatorsConcatenationOperatorComparisonOperatorsLogicalOperatorsSetOperatorsOtherBuilt-InOperatorsUser-DefinedOperators.
    Phil
    第4页
    12/3/2010
     UnaryandBinaryOperators
    UnaryBinary:operatoroperand:operand1operatoroperand2
    Precedence
    ThefollowingtablelistslevelsofprecedenceamongSQLoperatorsfromhightolow.(Operatorslistedonthesamelinehavethesameprecedence)
    +,-(Identity,negation)*,/+,-,||=,!=,<,>,<=,>=,ISNULL,LIKE,BETWEEN,INNOTANDOR
    SetOperators
    UNIONUNIONALLINTERSECTMINUS
    Additionalinformation
    ESCAPEOptionOuterjoin(+)/PRIORALL/ANY/BETWEENAND/EXIST/LIKE/NOTIN/
    Functions
    SQLFunctionsUser-DefinedFunctions
    SQLFunctions
    Single-RowFunctionAggregateFunction
    Phil
    第5页
    12/3/2010
     AnalyticFunction
    Single-RowFunction
    NumberFunctionsabs,ceil,floor,mod,sign,truncCharacterFunctionschr,lower,lpad,ltrim,replace,rpad,rtrim,substr,trim,upper,ascii,instr,lengthDateFunctionsadd_months,last_day,months_between,next_day,round,sysdate,truncnote:add_month(‘19990228’,1)=‘19990331’ConversionFunctionsto_char,to_date,to_numberMiscellaneousSingle-RowFunctionsdump,greatest,least,nvl,sys_context,uid,user,userenv,vsize
    AggregateFunction
    Avg,count,max,min,sum
    Expressions,Conditions,andQueries
    Expressions
    SimpleExpressionsCompoundExpressionsFunctionExpressionsDECODEExpressionsCASEExpressionsExpressionList
    DECODE
    Syntax:decode(expr,search1,result1,search2,result2,search3,result3,…default)
    InaDECODEexpression,Oracleconsiderstwonullstobeequivalent.Ifexprisnull,Oraclereturnstheresultofthefirstsearchthatisalsonull.ThemaximumnumberofcomponentsintheDECODEexpression,includingexpr,searches,results,anddefaultis255.
    Phil
    第6页
    12/3/2010
     CASE
    Syntax:Casewhenexpr1thenexpra,whenexpr2thenexprb,…elseexprcend
    To
    avoidexceedingthelimitof128choices,youcannestCASEexpressions.Thatisexpr1canitselfbeaCASEexpression.
    ExpressionList
    Example:(’SCOTT’,’BLAKE’,’TAYLOR’)
    Conditions
    simple_comparison_conditiongroup_comparison_conditionmembership_conditionrange_conditionNULL_conditionEXISTS_conditionLIKE_conditioncompound_condition
    批注[phil4]:Any,some,all
    QueriesandSubqueries
    SimpleQueries
    HierarchicalQueries
    Startwith/ConnectbyExample:seedemo.sql
    SELECTLPAD('',4*(LEVEL-1))||nameFROMstaffstartwithid=3connectbyid=priormgrid
    Phil
    第7页
    12/3/2010
     SortingQueryResults
    Example:
    Selectempname,deptnamFromempe,deptdWheree.depid=d.depidOrderby2,1;
    Join
    SelfJoinsCartesianProductsOuterJoins
    UsingSubqueries
    Example:
    UPDATEempaSETsalary_cat=(SELECTsalary_catFROMempbWHEREa.mgrid=b.id)WHEREmgridIN(SELECTidFROMemp);批注[phil5]:Oracleoffersstatement-levelconsistencybydefault.
    Dualtable
    SelectingfromtheDUALtableisusefulforcomputingaconstantexpressionwiththeSELECTstatement.
    SQLStatements
    Summary
    DataDefinitionLanguage(DDL)StatementsCreate,drop,altertable,truncate,comment,revoke,grant,analyze…DataManipulationLanguage(DML)StatementsSelect,delete,insert,update,locktable,call,explainplanTransactionControlStatementsCommit,rollback,savepoint,settransaction
    批注[phil6]:OracleimplicitlycommitsthecurrenttransactionbeforeandaftereveryDDLstatement.
    Phil
    第8页
    12/3/2010
     SessionControlStatementsAltersessionSystemControlStatementsAltersystem
    SQL*PLUS
    Editing
    Appendline/textChangetextDeleteline(s)Editwithsystemeditor
    Examples
    Usingbindvariables
    VARIABLErateNUMBERVARIABLEtableCHARACCEPTtablePROMPT'tablename:';
    DECLAREtot_blksNUMBER;unused_blksNUMBER;hiwm_blksNUMBER;real_blksNUMBER;BEGINSELECTblocksINTOtot_blksFROMuser_segmentsWHEREsegment_name=:tableANDsegment_type='TABLE';SELECTempty_blocksINTOunused_blksFROMuser_tablesWHEREtable_name=:table;hiwm_blks:=(tot_blks-unused_blks)-1;
    Phil
    第9页
    12/3/2010
     SELECTCOUNT(DISTINCTSUBSTR(DBMS_ROWID.ROWID_TO_RESTRICTED(ROWID,0),1,8)||SUBSTR(DBMS_ROWID.ROWID_TO_RESTRICTED(ROWID,0),15,4))INTOreal_blksFROMSI2000.IC01;:rate:=((hiwm_blks-real_blks)/hiwm_blks)*100;END;/PRINTrate;
    FormatingResults
    columntablespace_nameformata25columntsformata25colMegabytesfor99999.99computesumofbytesusedfreeonreportbreakonreportspooltablespaceselecta.tablespace_namets,a.file_id,trunc(sum(b.bytes)/(count(*)*1024*1024),2)Megabytes,trunc((sum(b.bytes)/count(*)-sum(a.bytes))/(1024*1024),2)used,trunc(sum(a.bytes)/(1024*1024),2)free,
    trunc(nvl(100-(sum(nvl(a.bytes,0))/(sum(nvl(b.bytes,0))/count(*)))*100,0),2)pct_usedfromsys.dba_free_spacea,sys.dba_data_filesbwherea.tablespace_name=b.tablespace_nameanda.file_id
    =b.file_idgroupbya.tablespace_name,a.file_id;
    spooloff
    Automation
    acceptobj_ownercharprompt"inputowner:"setpagesize0spoolrecompile.sqlselect'alterpackage'||owner||'.'||object_name||'compile'||decode(object_type,'PACKAGE','package','body')||';'fromdba_objectswhereowner='&;obj_owner'andobject_typelike'PACK%'Phil第10页12/3/2010
     andstatus='INVALID'orderbystatusdesc,object_name;spooloff!virecompile.sql@recompile.sql!rmrecompile.sql
    AccessingSQLDatabases
    ConnectingtotheDefaultDatabaseConnectingtoaRemoteDatabaseSqlplususer/password@databaseCopyingDatafromOneDatabasetoAnother
    SQL>COPYFROMSCOTT/TIGER@BOSTONDB->TOTODD/FOX@CHICAGODB->CREATENEWDEPT(DNUMBER,DNAME,CITY)->USINGSELECT*FROMDEPT
    CopyingDatabetweenTablesonOneDatabase
    COPY
    UsingcopycommandinSQLPlustocopydatafromanotherdatabase.COMMITCONTROLBATCHMoving
    Example:setcopycommit1setarraysize2000
    copyfromuser/passwd@NT_SERVreplacetablenameusingselect*fromtablename
    PL/SQLGuide
    PL/SQL:APeek
    Phil
    第11页
    12/3/2010
     BLOCK
    PL/SQL代码按节编写成为块
    DECLAREBEGINEXCEPTIONWHENTHENEND;
    块可以嵌套
    CURSOR
    ExplicitCursor
    DECLARECURSORcCur1ISSELECT…;
    OPENFETCHCLOSE
    CURSOR…FORLOOP
    FORCur_RecINcCur1LOOPENDLOOP;
    Cur_Rec不需要说明。引用规则：Cur_Rec.ColumnName
    ImplicitCursor
    任何SQL语句将创建游标。查询v$session,v$cursor可以得到每个session最近一次打开的游标所执行的语句从v$sqlarea中可以得到更多信息
    Phil
    第12页
    12/3/2010
     Explicit/ImplicitCursorAttributes
    %FOUND%ISOPEN%NOTFOUND%ROWCOUNT引用：ExplicitCursor:IFc1%ROWCOUNT>10THENImplicitCursor:DELETEFROMempWHEREempno=my_empno;
    IFSQL%FOUNDTHEN--deletesucceeded
    Cursorwithparameters
    DECLARECURSORC1(VIEW_PATTERNVARCHAR2)ISSELECTVIEW_NAMEFROMALL_VIEWSWHEREVIEW_NAMELIKEVIEW_PATTERN||'%'ANDROWNUM<=10ORDERBYVIEW_NAME;VNAMEVARCHAR2(40);BEGINFORIINC1('USER_AR')LOOPDBMS_OUTPUT.PUT_LINE(I.VIEW_NAME);ENDLOOP;DBMS_OUTPUT.PUT_LINE();FORIINC1('USER')LOOPDBMS_OUTPUT.PUT_LINE(I.VIEW_NAME);ENDLOOP;EXCEPTIONWHENOTHERSTHENDBMS_OUTPUT.PUT_LINE('AAA');END;
    DataTypes
    NumberTypes
    NUMBER
    Phil第13页12/3/2010
     NUMBER(precision,scale)NUMBER(precision)同于NUMBER(precision,0)NUMBER同于FLOAT
    NUMBERSubtypes
    DEC,DECIMAL,DOUBLE,PRECISION,FLOAT,INTEGERINT,NUMERIC,REAL,SMALLINT
    Charactertypes
    CHARVARCHAR2VARCHARROWID…
    OtherTypes
    DATEBOOLEAN
    RECORD
    TYPEStudent_rectypeISRECORD(first_namelast_nameStudent_recVARCHAR2(12),VARCHAR2(15),…);Student_rectype;
    引用：Student_rec.first_name
    TABLE
    TYPEStud_tbltypeISTABLEOFStudent_rectypeINDEXBYBINARY_INTEGER;
    PL/SQL表中能够容纳的行数只受限于INDEX变量值的范围。N
    ote:PL/SQL表中的数据在被引用之前，必须初始化。否则，会产生NO_DATA_FOUND异常。
    %TYPEand%ROWTYPE
    Oracle的一个好处就是允许动态地说明变量。
    VFirstNameEMP.firstname%TYPE;Student_RecStudent_Cur%ROWTYPE;
    Phil
    第14页
    12/3/2010
     Exceptions
    异常处理源于ADA语言。例子：
    EXCEPTIONWHENNO_DATA_FOUNDTHEN--catchesall’nodatafound’errors
    ExceptionOracleErrorSQLCODEValue常见异常
    CURSOR_ALREADY_OPENORA-06511-6511DUP_VAL_ON_INDEXINVALID_CURSORNO_DATA_FOUNDTOO_MANY_ROWSORA-00001-1ORA-01001-1001ORA-01403+100ORA-01422–1422
    OTHERSUserdefineexceptions
    DECLAREBAD_ROWIDEXCEPTION;XROWID;PRAGMAEXCEPTION_INIT(BAD_ROWID,-01445);BEGINSELECTROWIDINTOXFROMTABWHEREROWNUM=1;EXCEPTIONWHENBAD_ROWIDTHENDBMS_OUTPUT.PUT_LINE('CANNOTQUERYROWIDFROMTHISVIEW');END;
    Raiseanexception
    DECLARESALARY_CODEVARCHAR2(1);INVALID_SALARY_CODEEXCEPTION;BEGINSALARY_CODE:='X';IFSALARY_CODENOTIN('A','B','C')THENRAISEINVALID_SALARY_CODE;ENDIF;EXCEPTIONWHENINVALID_SALARY_CODETHENDBMS_OUTPUT.PUT_LINE('INVALIDSALARYCODE');END;
    Transaction
    COMMIT,ROLLBACK,SAVEPOINTImplicitRollbackWhenastatementfails,Oraclerollbackstothesavepointjustatthebeginningofthestatement.
    Phil第15页12/3/2010
     EndingTransactionsOracleswillcommitthetransactionafteraDDL,DCL,orCOMMITstatementorifyouissuetheEXIT,DISCONNECT,orQUITcommand.ExecutingaROLLBACKstatementorabortingfromtheSQL*Plussession,Oraclerollsbackthetransaction.
    UseFORUPDATEtooverridedefaultlock
    DECLARECURSORc1ISSELECTempno,salFROMempWHEREjob=’SALESMAN’ANDcomm>salFORUPDATENOWAIT;
    Oracleprovidestwolevelofconsistency:statement-level(default)andtransaction-level.Considerthiscase:attheendofthetransaction,youfindtheresultsetthatyouhavegotfromapriorselectstatementhasbeenmodifiedbyanothertransaction.FORUPDATEclauseprovidesawaytoavoidthatphenomena,becauseitlockseveryrowaffectedbytheselectstatement.
    AutonomousTransactions
    AnAutonomoustransactionisfullyindependenttotheoutertransaction.Forexample:
    PROCEDUREproc3ISPRAGMAAUTON...dept_idNUMBER;BEGINdept_id:=20;UPDATE...INSERT...UPDATE...COMMIT;END;
    NOTE:Nochangesmadebythemaintransactionarevisibletotheautonomoustransaction.Ontheotherhand,aftertheautonomoustransactioncommits,allchangesarevisibletothemaintransactionunlesstheisolationlevelissettoserializable.
    ControlStructure
    ConditionalControlIterativeControlSequentialControl
    Phil
    第16页
    12/3/2010
     ConditionalControl
    IFcondition1THENSequence_of_statements1ELSIFcondition2THENSequence_of_statements2ELSESequence_of_statements3ENDIF;
    IterativeControl
    LOOP
    LOOPsequence_of_statementsENDLOOP;
    Completealoop:EXIT/EXITWHENWHILE…LOOPFOR…LOOP
    SequentialControl
    GOTOsta
    tementRestrictions:CannotbranchintoanIFstatement,LOOPstatement,orsub-blockCannotbranchfromanexceptionhandlerintothecurrentblock.NULLstatement
    Subprograms
    ProcedureFunction
    Dependency
    Whenanobjectisreferencingbyanother,thereisadependentrelationbetweenthem.Usethefollowingscripttofindoutwhatobjectsareaffectedwhenastoredprocedure:
    SELECTtype||’‘||owner||’,’||nameFROMall_dependenciesWHEREreferenced_ower=UPPER(‘&;1’)
    Phil
    第17页
    12/3/2010
     ANDreferenced_name=UPPER(‘&;2’)ORDERBYtype;
    Parameters
    INOUTINOUT
    NOCOPYCompilerhint
    Bydefault,theOUTandINOUTparametersarepassedbyvalue.Thatwillslowdownexecutionanduseupmemory.Topreventthat,youcanspecifytheNOCOPYhint,whichallowsthePL/SQLcompilertopassOUTandINOUTparametersbyreference.Example:
    DECLARETYPEStaffISVARRAY(200)OFEmployee;PROCEDUREreorganize(my_staffINOUTNOCOPYStaff)IS...
    Packages
    AdvantagesofPackages
    ModularityEasierApplicationDesignInformationHidingAddedFunctionalityBetterPerformance
    PackagespecandPackageBody
    PackageSpecIncludingDeclarationofpublicprocedures/functions.IncludingDeclarationofpublicvariables,constantsanduser-defineexceptionsPackageBodyIncludingDeclarationandImplementationofprivateprocedures/functionsIncludingDeclarationofprivatevariables,constantsanduser-defineexceptionsIncludingImplementationofpublicprocedures/functions
    Phil
    第18页
    12/3/2010
     Overloading
    Takethisasanexample:
    FUNCTIONNextWord(vStringToParseINFUNCTIONNextWord(vStringToParseINvDelimiterINvarchar2)RETURNvarchar2;varchar2,varchar2);
    PL/SQLdetermineswhichofthetwoproceduresisbeingcalledbycheckingtheirformalparameters.
    NativeDynamicSQL
    TheNeedForDynamicSQL
    DDL,DCLandsessioncontrolstatementcannotbeexecutedstaticallyinPL/SQL.Wantmoreflexibility.UsedinapplicationsthatallowuserstochoosequerysearchcriteriaoroptimizerhintsatrunTogetbetterperformance,usedynamicSQLotherthanthepackageDBMS_SQL(Pre-Oracle8iPL/SQLsolution).
    UsingtheEXECUTEIMMEDIATEStatement
    Syntax:
    EXECUTEIMMEDIATEdynamic_string[INTO{define_variable[,define_variable]...|record}][USING[IN|OUT|INOUT]bind_argument[,[IN|OUT|INOUT]bind_argument]...][{RETURNING|RETURN}INTObind_argument[,bind_argument]...];
    Example:
    sql_stmt:=’INSERTINTOdeptVALUES(:1,:2,:3)’;EXECUTEIMMEDIATEsql_stmtUSINGdept_id,dept_name,location;
    UsingtheOPEN-FOR,FETCH,andCLOSEStatements
    Example:
    DECLARETYPEEmpCurTypISREFCURSOR;emp_cvEmpCurTyp;emp_recemp%ROWTYPE;sql_stmtVARCHAR2(200);
    Phil
    第19页
    12/3/2010
     my_jobVARCHAR2(15):=’CLERK’;BEGINsql_stmt:=’SELECT*FROMempWHEREjob=:j’;OPENemp_cvFORsql_stmtUSINGmy_job;LOOPFETCHemp_cvINTOemp_rec;EXITWHENemp_cv%NOTFOUND;--processrecordENDLOOP;CLOSEemp_cv;
    END;
    SQLTuningTips
    数据库工具
    EXPLAINPLANTKPROFCBOandRBO
    EXPLAINPLAN
    使用：使用：
    EXPLAINPLANSETstatement_id=FOR
    察看：
    SELECTlpad('',2*(level-1))||operationoptions||''||||''||
    object_name||''||decode(id,0,'Cost='||position)"EXPLAINPLAN";FROMPLAN_TABLE
    STARTWITHid=0ANDstatement_id=
    CONNECTBYPRIORid=parent_idANDstatement_id=;
    Phil
    第20页
    12/3/2010
     UnderstandExecutionPlan
    例子
    SQL>selectcount(*)23fromic01,ac01whereic01.aac001=ac01.aac001;
    012340122
    SELECTSTATEMENTOptimizer=CHOOSESORT(AGGREGATE)NESTEDLOOPSTABLEACCESS(FULL)OF'IC01'INDEX(UNIQUESCAN)OF'PK_AC01'(UNIQUE)
    显然，Oracle选择了较小的表作为驱动表
    TKPROF
    ALTERSESSIONSETSQL_TRACE=TURE/FALSE;跟踪文件名称：ora_pid.trctkproftracefileoutputfile可得到跟踪文件的格式化输出
    CBOandRBO
    CBO:costbasedoptimizerRBO:rulebasedoptimizer必须经常对表和索引进行ANALYZE，这样才能保证CBO选择正确的执行方式
    SQL优化技巧
    避免无计划的TableFullScan
    TableFullScan得到的数据在LRU一端复合索引中列的次序是很重要的：只使用非主列的查询是不会用到索引的以下情况将导致TableFullScanWhere子句中，对索引列使用了函数索引主列上的条件是NULL判断或!=判断对索引主列使用like操作，但值以%开始或值是一个赋值变量Like‘%xxxx’或like:x1
    Phil
    第21页
    12/3/2010
     只使用选择性索引
    对于普通索引，所选择的列应该有较高的选择性这里，选择性=索引列的取值/表的行数可以通过查询USER_INDEXES中的distinct_key字段来得到，当然，表需要做ANALYZE在复合索引和多个单列索引间作选择如果经常同时使用的是多个列，复合索引将更具有优势，因为避免了不必要的AND-EQUAL操作
    多表联接
    MergeJoint
    一般情况下，执行步骤为：tableaccessfull->sortjoint->mergejoint在头两个步骤中，也需要花很多的时间。如果有必要，优化SGA的SORT_AREA_SIZE参数和TEMP表空间的存储参数
    Nestedloops
    通常表明在联接期间使用了索引。选择正确的驱动表：驱动表一般是做全表搜索的表，选择正确有助于减少不必要的I/O。显而易见，我们应该选择较小的驱动集。比如表a,b,c都以列col作为联接条件，而同时col上有一个限定条件，我们应当选择在该限定条件下、返回记录数最少的表作为驱动表。经常性的ANALYZE，将有利于ORACLE做出正确的判断
    Hashjoin
    在hashjoin中，两个表都作了全表扫描。可以通过初始化参数hash_join_enable来打开或关闭它。而hash_area_size决定用于hash联接的内存总数。通常，提高该值有利于hash联接的
    性能。Hashjoin在以下场合中具有优势：批处理应用两个巨大表之间的连接一个巨大表和一个小表之间的连接，会自动设置小标为cache
    Phil
    第22页
    12/3/2010
     优化子查询
    返回最大值的子查询
    比如：
    SELECT*FROMemp,emp_hehWHEREemp.id=eh.idANDemp.name='Beckham'ANDeh.hdate=(SELECTMAX(hdate)FROMemp_he2WHEREe2.id=eh.id)
    查询返回某职员最近一次的记录，而假设hdate上有一个索引idx_eh_date，那么，下面的查询会更有效率：
    SELECT/*+INDEX_DESC(ehidx_eh_date)*/*FROMemp,emp_hehWHEREemp.id=eh.idANDemp.name='Beckham'ANDrownum=1;
    使用any,exist,outerjoint代替某些子查询
    最典型的例子莫过于使用outerjoin代替notin:
    SELECT*FROMempWHEREidNOTIN(SELECTidFROMemp_hWHEREid=emp.id);
    改写为
    SELECT*FROMemp,emp_hWHEREemp.id=emp_h.id(+)ANDemp_h.idISNULL;
    或使用exist代替in
    SELECT*FROMempWHEREidIN(SELECTidFROMemp_hWHEREid=emp.id);
    改写为
    SELECT*Phil第23页12/3/2010
     FROMempWHEREEXIST(SELECT1FROMemp_hWHEREid=emp.id);
    对巨大的表的查询
    放弃使用没有帮助的索引当一次选取的记录比例足够大时，使用索引先是进行IndexRangeScan，然后是表访问，这会给SGA带来较大的开销，而且在较坏的情况下，也不会大大较少I/O操作。因为在全表扫描的数据放在LRU一端，通常只有multi_read_count块保留在SGA中；而通过INDEX访问的数据在MRU一端，通常全部保留。创建充分索引的表查询期间，所有的访问都通过索引进行，不需要任何表访问。
    创建view时使用UNIONALL而不用UNION
    UNION操作在合并的时候，会排斥重复的纪录，这与DISTINCT相似。然而，我们一般需要对视图进行一些限制操作，这些限制操作都是在UNION完成之后才发生作用的。
    CREATEVIEWSALESASSELECT*FROMSALES_PERIOD_1UNIONALLSELECT*FROMSALES_PERIOD_2
    而对于上面的视图SALES进行的限制操作，都会在UNIONALL之前进行
    避免在SQL中使用PL/SQL功能调用
    比如
    SELECTtrn_type,exchg(amt,currency)FROMinternaltional_transWHEREexchg(amt,currency)>1000;
    其中的exchg函数将被作为anonymousPL/SQLBlock在每一行记录中调用两次。
    使用execute…using
    使用execute…using而不是executeimmidate，可以减少发送到共享SQL区域的SQL语句，并且不必进行多余的parsing。问题，以下的SQL语句会使用建立在Company_name上的索引吗？
    SELECT*FROMcompanyWHEREcompany_namelike:name||’%’;
    Phil
    第24页
    12/3/2010
     杂项
    使用AND条件时，应该确保让最先导致失败的条件先执行使用OR条件时，应该确保让最先导致失败的条件后执行使用索引的主列如果使用了多个表，最好在引用列的时候加上表的别名
    限制。尽可能使用>=代替LIKE在RBO下，where字句中字段的出现顺序，最好和索引的字段顺序相同
    PL/SQL
    使用游标
    SELECTnameINTOsNameFROMSTUDENTWHEREid=‘2334234’;
    该查询将导致两次取操作。经可能使用异常处理显然，异常比IF-THEN更具有效率
    -=end=-
    Phil
    第25页
    12/3/2010

上一篇资讯： SQL_Server2000数据库置疑的解决方法

下一篇资讯： sql_log