Oracle英文版培训课件之SQL Tuning：Les13-RW

OptimizerHintsObjectivesAftercompletingthislesson,youshouldbeabletospecifyhintsfor:OptimizermodeQuerytransformationAccesspathJoinordersJoinmethodsOptimizerHints:OverviewOptimizerhints:AreusedtoalterexecutionplansInfluenceoptimizerdecisionsProvideamechanismtoinstructtheoptimizertochooseacertainqueryexecutionplanTypesofHintsApplytotheentireSQLstatementStatementhintsOperateonasinglequeryblockQueryblockhintsSpecifymorethanonetableorviewMultitablehintsSpecifiedononetableorviewSingle-tablehintsSpecifyingHintshintcommenttext*//*+hintcommenttext--+SELECTINSERTDELETEUPDATEMERGESELECTINSERTDELETEUPDATEMERGERulesforHintsPlacehintsimmediatelyafterthefirstSQLkeywordofastatementblock.Eachstatementblockcanhaveonlyonehintcomment,butitcancontainmultiplehints.Hintsapplytoonlythestatementblockinwhichtheyappear.Ifastatementusesaliases,hintsmustreferencealiasesratherthantablenames.HintRecommendationsUsehintscarefullybecausetheyimplyahighmaintenanceload.Beawareoftheperformanceimpactofhard-codedhintswhentheybecomelessvalid.OptimizerHintSyntax:ExampleUPDATE/*+INDEX(pPRODUCTS_PROD_CAT_IX)*/productspSETd_min_price=(SELECT(d_list_price*.95)FROMproductsprWHEREd_id=d_id)WHEREd_category='Men'ANDd_status='available,onstock'/HintCategoriesTherearehintsfor:OptimizationapproachesandgoalsAccesspathsQuerytransformationsJoinordersJoinoperationParallelexecutionOptimizationGoalsandApproachesInstructsOracleServertooptimizeanindividualSQLstatementforfastresponseFIRST_ROWS(n)Choosescost-basedapproachwithagoalofbestthroughputALL_ROWSHintsforAccessPathsPerformsafulltablescanFULLExplicitlychoosesabitmapaccesspathINDEX_COMBINEScansanindexinascendingorderINDEX_ASCScansanindexinascendingorderINDEXAccessesatablebyROWIDROWIDFullNotesPageHintsforAccessPathsINDEX_JOINInstructstheoptimizertouseanindexjoinasanaccesspathINDEX_DESCChoosesanindexscanforthespecifiedtableINDEX_FFSPerformsafast-fullindexscanNO_INDEXDisallowsusingasetofindexesAND_EQUALMergessingle-columnindexesFullNotesPageINDEX_COMBINEHint:ExampleSELECT--+INDEX_COMBINE(CUSTOMERS)cust_last_nameFROMSH.CUSTOMERSWHERE(CUST_GENDER='F'ANDCUST_MARITAL_STATUS='single')ORCUST_YEAR_OF_BIRTHBETWEEN'1917'

AND'1920';INDEX_COMBINEHint:ExampleExecutionPlan0SELECTSTATEMENTOptimizer=CHOOSE(Cost=491

Card=10481

Bytes=167696)10TABLEACCESS(BYINDEXROWID)OF'CUSTOMERS'

(Cost=491…)21BITMAPCONVERSION(TOROWIDS)32BITMAPOR43BITMAPAND54BITMAPINDEX(SINGLEVALUE)OF

'CUST_MARITAL_BIX'64BITMAPINDEX(SINGLEVALUE)OF

'CUST_GENDER_BIX'73BITMAPMERGE7BITMAPINDEX(RANGESCAN)OF

'CUST_YOB_BIX'HintsforQueryTransformationUSE_CONCATRewritesORintoUNIONALLanddisablesINLISTprocessingNO_EXPANDPreventsORexpansionsHintsforQueryTransformationMERGEMergesaviewforeachqueryNO_MERGEPreventsmergingofmergeableviewsSTAR_

TRANSFORMATIONMakestheoptimizerusethebestplaninwhichthetransformationcanbeusedFACTIndicatesthatthehintedtableshouldbeconsideredasafacttableNO_FACTIndicatesthatthehintedtableshouldnotbeconsideredasafacttableFullNotesPageHintsforJoinOrdersORDEREDCausestheOracleServertojointablesintheorderinwhichtheyappearintheFROMclauseLEADINGUsesthespecifiedtableasthefirsttableinthejoinorderFullNotesPageHintsforJoinOperationsUSE_NLJoinsthespecifiedtableusinganestedloopjoinNO_USE_NLDoesnotusenestedloopstoperformthejoinUSE_MERGEJoinsthespecifiedtableusingasort-mergejoinNO_USE_MERGEDoesnotperformsort-mergeoperationsforthejoinUSE_HASHJoinsthespecifiedtableusingahashjoinNO_USE_HASHDoesnotusehashjoinFullNotesPageOtherHintsAPPENDEnablesdirect-pathINSERTNOAPPENDEnableregularINSERTORDERED_PREDICATESForcestheoptimizertopreservetheorderofpredicateevaluationCURSOR_SHARING_EXACTPreventsreplacingliteralswithbindvariablesDYNAMIC_SAMPLINGControlsdynamicsamplingtoimproveserverperformanceFullNotesPageHintsforSuppressingIndexUsageHintDescriptionNO_INDEXDisallowsuseofanyindexesFULLForcesafulltablescanINDEXorINDEX_COMBINEForcestheoptimizertouseaspecificindexorasetoflistedindexesHintsandViewsDonotusehintsinviews.Useview-optimizationtechniques:StatementtransformationResultsaccessedlikeatableHintscanbeusedonmergeableviewsandnonmergeableviews.FullNotesPageHintsforViewProcessingMERGEMergescomplexviewsorsubquerieswiththesurroundingqueryNO_MERGEDoesnotmergemergeableviewsGlobalandLocalHintsExtendedhintsyntaxenablesthespecifyingof(global)hintsthroughviews.ReferencesatablenameinthehintwithadotnotationCREATEviewcity_viewASSELECT*FROMcustomerscWHEREcust_citylike'S%';SELECT/*+index(v.ccust_credit_limit_idx)*/v.cust_last_name,v.cust_credit_limitFROMcity_viewvWHEREcust_credit_limit>5000;SpecifyingaQueryBlockinaHintExplainplanforSELECTemployee_id,last_nameFROMhr.employeeseWHERElast_name='Smith';

SELECTPLAN_TABLE_OUTPUTFROMTABLE(DBMS_XPLAN.DISPLAY(NULL,NULL,'ALL'));SELECT/*+QB_NAME(qb)FULL(@qbe)*/

employee_id,last_nameFROMhr.emplo