Oracle英文版培訓(xùn)課件之SQL Tuning：Les14-RW

MaterializedViewsObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:IdentifythecharacteristicsandbenefitsofmaterializedviewsUsematerializedviewstoenablequeryrewritesVerifythepropertiesofmaterializedviewsPerformrefreshesonmaterializedviewsMaterializedViewsAmaterializedview:IsaprecomputedsetofresultsHasitsowndatasegmentandoffers:SpacemanagementoptionsUseofitsownindexesIsusefulfor:ExpensiveandcomplexjoinsSummaryandaggregatedataIfMaterializedViewsAreNotUsedSELECTc.cust_id,SUM(amount_sold)FROMsaless,customerscWHEREs.cust_id=c.cust_idGROUPBYc.cust_id;CREATETABLEcust_sales_sumASSELECTc.cust_id,SUM(amount_sold)ASamountFROMsaless,customerscWHEREs.cust_id=c.cust_idGROUPBYc.cust_id;SELECT*FROMcust_sales_sum;CREATEMATERIALIZEDVIEWcust_sales_mvENABLEQUERYREWRITEASSELECTc.cust_id,SUM(amount_sold)ASamountFROMsaless,customerscWHEREs.cust_id=c.cust_idGROUPBYc.cust_id;SELECTc.cust_id,SUM(amount_sold)FROMsaless,customerscWHEREs.cust_id=c.cust_idGROUPBYc.cust_id;ExecutionPlan0SELECTSTATEMENTOptimizer=ALL_ROWS(Cost=6…)10MAT_VIEWREWRITEACCESS(FULL)OF'CUST_SALES_MV'(MAT_VIEWREWRITE)(Cost=6…)BenefitsofUsingMaterializedViewsHowManyMaterializedViews?Onematerializedviewformultiplequeries:Onematerializedviewcanbeusedtosatisfymultiplequeries.Lessdiskspaceisneeded.Lesstimeisneededformaintenance.Queryrewritechoosesthematerializedviewtouse.Onematerializedviewperquery:IsnotrecommendedbecauseitconsumestoomuchdiskspaceImprovesonequery'sperformanceCreatingMaterializedViews:

SyntaxOptionsCREATEMATERIALIZEDVIEWmview_name[TABLESPACEts_name][PARALLEL(DEGREEn)][BUILD{IMMEDIATE|DEFERRED}][{REFRESH{FAST|COMPLETE|FORCE}[{ONCOMMIT|ONDEMAND}]

|NEVERREFRESH}][{ENABLE|DISABLE}QUERYREWRITE]

ASSELECT…FROM…CreatingMaterializedViews:ExampleCREATEMATERIALIZEDVIEWcost_per_year_mvENABLEQUERYREWRITEASSELECTt.week_ending_day,t.calendar_year,d_subcategory,sum(c.unit_cost)ASdollarsFROMcostsc,timest,productspWHEREc.time_id=t.time_idANDd_id=d_idGROUPBYt.week_ending_day,t.calendar_year,d_subcategory;Materializedviewcreated.TypesofMaterializedViewsMaterializedviewswithaggregatesMaterializedviewscontainingonlyjoinsCREATEMATERIALIZEDVIEWcust_sales_mvASSELECTc.cust_id,s.channel_id,SUM(amount_sold)ASamountFROMsaless,customerscWHEREs.cust_id=c.cust_idGROUPBYc.cust_id,s.channel_id;CREATEMATERIALIZEDVIEWsales_products_mvASSELECTs.time_id,d_nameFROMsaless,productspWHEREd_id=d_id;RefreshMethodsYoucanspecifyhowyouwantyourmaterializedviewstoberefreshedfromthedetailtablesbyselectingoneoffouroptions:COMPLETEFASTFORCENEVERYoucanviewtheREFRESH_METHODintheALL_MVIEWSdatadictionaryview.FullNotesPagesRefreshModesManualrefreshSpecifyONDEMANDoptionByusingtheDBMS_MVIEWpackageAutomaticrefreshSynchronousSpecifyONCOMMIToptionUponcommitofchangestotheunderlyingtablesbutindependentofthecommittingtransactionAutomaticrefreshAsynchronousSpecifyusingSTARTWITHandNEXTclausesDefinesarefreshintervalforthematerializedviewREFRESH_MODEinALL_MVIEWSFullNotesPagesManualRefreshwithDBMS_MVIEWForON

DEMANDrefreshonlyThreeprocedureswiththeDBMS_MVIEWpackage:REFRESHREFRESH_ALL_MVIEWSREFRESH_DEPENDENTMaterializedViews:ManualRefreshSpecificmaterializedviews:ExecDBMS_MVIEW.REFRESH('cust_sales_mv');VARIABLEfailNUMBER;execDBMS_MVIEW.REFRESH_ALL_MVIEWS(:fail);VARIABLEfailNUMBER;execDBMS_MVIEW.REFRESH_DEPENDENT(-:fail,'CUSTOMERS,SALES');Materializedviewsbasedononeormoretables:Allmaterializedviewsdueforrefresh:QueryRewritesIfyouwanttouseamaterializedviewinsteadofthebasetables,aquerymustberewritten.Queryrewritesaretransparenttoapplications.Queryrewritesdonotrequirespecialprivilegesonthematerializedview.Amaterializedviewcanbeenabledordisabledforqueryrewrites.QueryRewritesUseEXPLAIN

PLAN

or

AUTOTRACEtoverifythatqueryrewritesoccur.Checkthequeryresponse:Fewerblocksareaccessed.Responsetimeshouldbesignificantlybetter.EnablingandControlling

QueryRewritesQueryrewritesareavailablewithcost-basedoptimizationonly.

Thefollowingoptimizerhintsinfluencequeryrewrites:REWRITE

NOREWRITE

REWRITE_OR_ERRORQUERY_REWRITE_ENABLED={true|false|force}QUERY_REWRITE_INTEGRITY={enforced|trusted|stale_tolerated}FullNotesPagesQueryRewrite:ExampleEXPLAINPLANFORSELECTt.week_ending_day,t.calendar_year,d_subcategory,sum(c.unit_cost)ASdollarsFROMcostsc,timest,productspWHEREc.time_id=t.time_id...ExecutionPlan0SELECTSTATEMENTOptimizer=ALL_ROWS(Cost…)10MAT_VIEWREWRITEACCESS(FULL)OF'costs_per_year_mv'(MAT_VIEWREWRITE)(Cost…)QueryRewrite:ExampleSELECTt.week_ending_day,t.calendar_year,d_subcategory,sum(c.unit_cost)ASdollarsFROMcostsc,timest,productspWHEREc.time_id=t.time_idANDd_id=d_idANDt.calendar_year='1999'GROUPBYt.week_ending_day,t.calendar_year,d_subcategoryHAVINGsum(c.unit_cost)>10000;SELECTweek_ending_day,prod_subcategory,dollarsFROMcost_per_year_mvWHEREcalendar_year='1999'ANDdollars>10000;VerifyingQueryRewriteCREATEMATERIALIZEDVIEWcust_orders_mvENABLEQUERYREWRITEASSELECTc.customer_id,SUM(order_total)ASamtFROMoe.orderss,oe.customerscWHEREs.customer_id=c.customer_idGROUPBYc.customer_id;SELECT/*+REWRITE_OR_ERROR*/c.customer_id,SUM(order_total)ASamtFROMoe.orderss,oe.customerscWHEREs.customer_id=c.customer_idGROUPBYc.customer_id;ORA-30393:aqueryblockinthestatementdidnotrewriteSQLAccessAdvisorForagivenworkload,theSQLAccessAdvisor:Recommendscreatingtheappropriate:MaterializedviewsMaterializedviewlogsIndexesProvidesrecommendationstooptimizefor:FastrefreshQueryrewriteCanberun:FromOracleEnterpriseManagerbyusingtheSQLAccessAdvisorWizardByinvokingtheDBMS_ADVISORpackageUsingtheDBMS_MVIEWPackageDBMS_MVIEWmethodsEXPLAIN_MVIEWEXPLAIN_REWRITETUNE_MVIEWTuningMaterializedViewsfor

FastRefreshandQueryRewriteDBMS_ADVISOR.TUNE_MVIEW(

task_nameINOUTVARCHAR2,

mv_create_stmtIN[CLOB|VARCHAR2]);ResultsofTune_MVIEWIMPLEMENTATIONrecommendationsCREATEMATERIALIZEDVIEWLOGstatements

ALTERMATERIALIZEDVIEWLOGFORCEstatementsOneormoreCREATEMATERIALIZEDVIEWstatementsUNDOrecommendationsDROPMATERIALIZEDVIEWstatementsFullNotesPagesDBMS_MVIEW.EXPLAIN_MVIEWProcedureAccepts:MaterializedviewnameSQLstatementAdviseswhatisandwhatisnotpossible:ForanexistingmaterializedviewForapotentialmaterializedviewbeforeyoucreateitStoresresultsinMV_CAPABILITIES_TABLE(relationaltable)orinaVARRAYutlxmv.sqlmustbeexecutedasthecurrentusertocreateMV_CAPABILITIES_TABLE.ExplainMaterializedView:ExampleEXECdbms_mview.explain_mview(

'cust_sales_mv','123');SELECTcapability_name,possible,related_text,msgtxtFROMmv_capabilities_tableWHEREstatement_id='123'ORDERBYseq;CAPABILITY_NAMEPRELATED