Oracle英文版培訓(xùn)課件之Data Warehouse：Les12

QueryRewriteObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:DefinequeryrewriteEnablequeryrewriteDeterminewhetherqueryrewriteoccurredExplainthevariousmethodsusedtorewritequeriesListqueryrewriterestrictionsTriestousematerializedviewsinsteadofbasetablestoreturnqueryresultsCansaveordersofmagnitudeofCPUandelapsedtimetoreturnresultsasqueriesareprecomputedCansignificantlyreduceI/ONotnecessaryforquerytobeintheexactformofthematerializedviewquerytorewriteVariousrequirementsforqueryrewritetotakeplaceQueryRewrite:OverviewCost-BasedQueryRewriteProcessPickthebestGenerateplanCompareplancostsQueryrewriteGenerateplanWhatCanBeRewritten?QueriesandsubqueriesinthefollowingtypesofSQLstatements:SELECTCREATETABLE…ASSELECTINSERTINTO…SELECTSubqueriesinDMLstatements:INSERTUPDATEDELETESubqueriesinthesetoperators:UNIONUNIONALLINTERSECTMINUSSettingInitializationParametersfor

QueryRewriteQUERY_REWRITE_ENABLED:SetatinstanceandsessionlevelTRUE:DefaultFORCEQUERY_REWRITE_INTEGRITY:SetatinstanceandsessionlevelENFORCED:DefaultTRUSTEDSTALE_TOLERATEDFullNotesPageUsingTrustedConstraintsTRUSTEDCONSTRAINTS:AllowsuseofnonvalidatedRELYconstraintsandrewriteagainstmaterializedviewsinUNKNOWNorFRESHstateduringrefreshRefreshcanusetrustedconstraints.QUERY_REWRITE_INTEGRITY

=

TRUSTEDduringrefreshENFORCEDCONSTRAINTS:Allowsuseofonlyvalidated,enforcedconstraintsandrewriteagainstmaterializedviewsinFRESHstateduringrefreshRefreshcanusevalidatedconstraints.QUERY_REWRITE_INTEGRITY

=

ENFORCEDduringrefreshEnablingQueryRewriteSpecifyENABLE

QUERY

REWRITEwhenthematerializedviewiscreated.EnablequeryrewriteaftercreationofthematerializedviewwithALTER

MATERIALIZED

VIEW.QueryRewriteRequirementsEitherallorpartoftheresultrequestedby

thequerymustbeobtainablefromthematerializedview.Constraintsordimensionsaredefined.QueryRewriteHintsREWRITEandNOREWRITEREWRITE[(mv1,mv2,…)]:Forcestheoptimizertouseamaterializedview(ifanyexist)torewritethequeryregardlessofthecostNOREWRITE:PreventstheoptimizerfromrewritingREWRITE_OR_ERROR:GeneratesanORA-30393errorifthequeryfailedtorewriteDidQueryRewriteOccur?1. Executethequery:2. Examinetheexecutionplan:SELECTs.zip,duct_type,sum(s.amount)FROMsaless,productpWHEREd_id=d_idGROUPBYs.zip,d_type;OPERATIONNAME

MAT_VIEWREWRITEACCESSFULLSALES_SUMMARYVerifyingThatQueryRewriteOccurredGoalistounderstand:Ifrewriteispossible,whichmaterializedviewisused?Whywasthequerynotrewritten?UsetheDBMS_MVIEW.EXPLAIN_REWRITEprocedurewiththefollowingparameters:SQLstatement(neverexecuted)OptionalmaterializedviewnameResultsarestoredintheREWRITE_TABLEtableorinaVARRAY.utlxrw.sqlmustbeexecutedinthecurrentschematocreateREWRITE_TABLE.UsingDBMS_MVIEW.EXPLAIN_REWRITEDECLAREqrytextVARCHAR2(2000):='SELECTcust_last_name,SUM(amount_sold)FROMsaless,customerscWHEREs.cust_id=c.cust_idGROUPBYcust_last_name';BEGINdbms_mview.explain_rewrite(qrytext,'smv','124');END;SELECTmessageFROMrewrite_tableWHEREstatement_id='124'

ORDERBYsequence;MESSAGEQSM-01001:queryrewritenotenabledFullNotesPageQueryRewriteMethodsTwocategoriesofqueryrewritemethods:TextmatchGeneralrewriteTextMatchRewriteMethodsTheoptimizerusestwomethods:Exacttextmatch:Entiretextofqueryiscomparedwithentiretextofmaterializedviewdetailquery.Partialtextmatch:OnlytextstartingfromtheFROMclauseofthequeryandmaterializedviewarecompared.Whitespacesareignored.Acomplexmaterializedviewisoneforwhich:Rewritecapabilityislimitedtotextmatchrewrite(exactorpartial)REWRITE_CAPABILITY=TEXTMATCHinDBA_MVIEWSExpressionMatchingQueryRewriteRewritebasedonequivalentmatchingof:ExpressionsSubexpressionsExpressionsshouldbestoredinthematerializedview’sSELECTlist.Ifaquery’sexpressiondoesnotmatchmaterializedview’sexpression,subexpressionsmatchingistried.Canonicalexpressionstakenintoaccount:CommutativityAssociativityDistributivityIfmultiplequeriesshareacommonsubexpression,storeitinthematerializedviewtoavoidfuturecomputations.ExpressionMatching:ExampleCREATEMATERIALIZEDVIEWsales_by_age_mv(age_bracket,sum_amount_sold)ENABLEQUERYREWRITEASSELECTTO_CHAR((2000-c.cust_year_of_birth)/10-0.5,999),SUM(s.amount_sold)FROMsaless,customerscWHEREs.cust_id=c.cust_idGROUPBYTO_CHAR((2000-c.cust_year_of_birth)/10-0.5,999);ExpressionMatching:ExampleSELECTTO_CHAR(((2000-c.cust_year_of_birth)/10)-0.5,999),SUM(s.amount_sold)FROMsaless,customerscWHEREs.cust_id=c.cust_idGROUPBYTO_CHAR((2000-c.cust_year_of_birth)/10-0.5,999);SELECTage_bracket,sum_amount_soldFROMsales_by_age_mv;QueryrewrittenGeneralQueryRewriteMethodsSelectioncompatibility(filtered)Joincompatibility(joinback)Datasufficiency(rollup)GroupingcompatibilityAggregatecomputabilityPartitionChangeTracking(PCT)and

QueryRewriteUsesmetadatatomaintainstalenessatafinergranularity:Onlyspecificsectionsofthematerializedviewareconsideredstale.QueryrewritecanuseamaterializedviewinENFORCED(orTRUSTED)modeforrowsconsideredfreshonly.Amaterializedview’sfreshrowsareidentifiedbyaddingselectionpredicatestothematerializedview’sdefinition.Amaterializedview’sfreshrowsareidentifiedbyusingpartitionsmarkersinthematerializedviewdefinition.PCTandQueryRewrite:ExampleCREATETABLEsales(time_idDATENOTNULL,prod_idNUMBERNOTNULL,cust_idNUMBERNOTNULL,channel_idCHAR(1)NOTNULL,promo_idNUMBERNOTNULL,quanty_soldNUMBERNOTNULL,amount_soldNUMBERNOTNULL)PARTITIONBYRANGE(time_id)(PARTITIONq1VALUESLESSTHAN('1-APR-2001'),PARTITIONq2VALUESLESSTHAN('1-JUL-2001'),PARTITIONq3VALUESLESSTHAN('1-OCT-2001'),PARTITIONq4VALUESLESSTHAN('1-JAN-2002'));PCTandQueryRewrite:Example1. CreatetheSSP_MVmaterializedview:2. InsertdataintothesecondpartitionoftheSALEStable: 3. Materializedviewqueryreflectingfreshrows:CREATEMATERIALIZEDVIEWssp_mvENABLEQUERYREWRITEASSELECTtime_id,prod_name,SUM(amount_sold)ASsamtFROMproductsp,salessWHEREd_id=d_idGROUPBYtime_id,prod_name;WHEREd_id=s.store_idANDtime_id<'1-JUL-2001'ORtime_id>='1-OCT-2001'INSERT…('1-JUL-2001'<=time_id<'1-OCT-2001')…PCTandQuery

Rewrite:ExampleThefollowingquery:Canberewritten:SELECTtime_id,SUM(amount_sold)ASsamtFROMproductsp,salessWHEREd_id=d_idANDtime_id<'1-JUL-2001'GROUPBYtime_id;SELECTtime_id,SUM(samt)FROMssp_mvWHEREtime_id<'1-JUL-2001'GROUPBYtime_id;QueryRewriteandBindVariablesConsidertwosituations:Materializedviewdetailqueryhasliterals.Iftheapplicationqueryusesbindvariablesforthoseliterals,thereisnopossiblerewrite.IfCURSOR_SHARINGissettoFORCEorSIMILAR,generalrewritecanoccuriftheapplicationquerydoesnotusebindvariablesforthoseliterals.Materializedviewdetailquerydoesnothaveliterals.Iftheapplicationqueryusesbindvariables,generalrewriteispossible.IfCURSOR_SHARINGissettoFORCEorSIMILAR,generalrewriteispossible.CreatingIndexesforMaterializedViewsRecommendations:Createasingle-columnbitmapindexoneachmaterializedviewkeycolumnforqueryexecutionperformance.CreateindexesoncolumnscontainingrowIDsformaterializedviewscontainingonlyjoins.Que