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

MaterializedViewsObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:DescribehowsummariescanbeusedtoimproveperformanceDifferentiatematerializedviewtypesExplainmaterializedviewintegrityCreateamaterializedviewListglobalizationimplicationsformaterializedviewsTheNeedforSummaryManagementHowcanyouimprovequeryresponsetime?Useindexes.Partitionyourdata.Implementparallelexecution.Whataboutprecomputingqueryresults?Createsummaries:MaterializedviewsAutomaticallyrewriteSQLapplications.Automaticallyrefreshdata.FullNotesPageUsingSummariestoImprovePerformanceSpecialtypesofaggregateviewsImprovequeryexecutiontimebyprecalculatingexpensivejoinsandaggregationoperationsbeforeexecutionandstoringresultsinadatabasetableCreatedusingaschemaobjectcalledamaterializedviewUsingSummariesOriginalquerybyuser:DBAcreatessummarytable:Newquerybyuserusingsummarytable:SELECT*FROMcust_sales_sum;SELECTc.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;UsingMaterializedViewsfor

SummaryManagementDBAcreatesmaterializedview:Userissuesoriginalquery:QueryisrewrittenbytheOracleserver:SELECT*FROMcust_sales_mv;CREATEMATERIALIZEDVIEWcust_sales_mvENABLEQUERYREWRITEASSELECTc.cust_id,SUM(amount_sold)FROMsaless,customerscWHEREs.cust_id=c.cust_idGROUPBYc.cust_id;SELECTc.cust_id,SUM(amount_sold)FROMsaless,customerscWHEREs.cust_id=c.cust_idGROUPBYc.cust_id;UsingMaterializedViewsfor

SummaryManagementDBAcreatesmaterializedview.Enduserqueriestablesandviews.Oracleserver

rewritesSQLquerytousematerializedview.123SummaryManagementComponentsMechanismstodefinematerializedviewsanddimensionsRefreshmechanismtoensurethatmaterializedviewscontainthelatestdataQueryrewritecapabilitytotransparentlyrewriteaquerytouseamaterializedviewSQLAccessAdvisor:RecommendsmaterializedviewsandindexestobecreatedDBMS_ADVISOR.TUNE_MVIEWprocedure:ShowsyouhowtomakeyourmaterializedviewfastrefreshableandusegeneralqueryrewriteUsingSummaryManagement1. UsetheSQLAccessAdvisortodeterminehowyouwillusematerializedviews.2. Creatematerializedviewsanddesignhowquerieswillberewritten.3. UseDBMS_ADVISOR.TUNE_MVIEWtoobtainanoptimizedmaterializedviewasnecessary.HowManyMaterializedViews?Queryrewritechooseswhichmaterializedviewtouse.Onematerializedviewperquery:IdealforqueryperformanceConsumestoomuchdiskspaceNotrecommendedOnematerializedviewformultiplequeries:OnematerializedviewcanbeusedtosatisfymultiplequeriesLessdiskspaceneededLesstimeneededtomaintainmaterializedviewsOneMaterializedViewforMultipleQueriesCREATEMATERIALIZEDVIEWcust_sales_mv2ENABLEQUERYREWRITEASSELECTcust_last_name,channel_id,SUM(amount_sold)FROMsaless,customerscWHEREs.cust_id=c.cust_idGROUPBYc.cust_last_name,s.channel_id;SELECTcust_last_name,channel_id,SUM(amount_sold)FROMsaless,customerscWHEREs.cust_id=c.cust_idGROUPBYc.cust_last_name,s.channel_id;SELECT*FROMcust_sales_mv2;QueryrewriteOneMaterializedViewforMultipleQueriesCREATEMATERIALIZEDVIEWcust_sales_mv2ENABLEQUERYREWRITEASSELECTcust_last_name,channel_id,SUM(amount_sold)FROMsaless,customerscWHEREs.cust_id=c.cust_idGROUPBYc.cust_last_name,s.channel_id;SELECTcust_last_name,SUM(amount_sold)FROMsaless,customerscWHEREs.cust_id=c.cust_idGROUPBYcust_last_name;SELECTcust_last_name,SUM(amount)FROMcust_sales_mv2GROUPBYcust_id;QueryrewriteOneMaterializedViewforMultipleQueriesCREATEMATERIALIZEDVIEWcust_sales_mv2ENABLEQUERYREWRITEASSELECTcust_last_name,channel_id,SUM(amount_sold)FROMsaless,customerscWHEREs.cust_id=c.cust_idGROUPBYc.cust_last_name,s.channel_id;SELECTchannel_id,SUM(amount_sold)FROMsalesGROUPBYchannel_id;SELECTchannel_id,SUM(amount)FROMcust_sales_mv2GROUPBYchannel_id;QueryrewriteDeterminingWhichMaterialized

ViewtoCreateOnematerializedviewcanbeusedtosatisfymultiplequeries.Multiplematerializedviewscansatisfythesamequery.Abalancebetweenperformanceandspaceusagemustbefound.Whichmaterializedviewshouldyoucreate?Analyzeyourworkload.UsetheSQLAccessAdvisor.UseDBMS_MVIEW.EXPLAIN_REWRITEtoseewhyamaterializedviewisusedorignored.TypesofMaterializedViewsMaterializedviewswithaggregates:Materializedviewscontainingonlyjoins:CREATEMATERIALIZEDVIEWcust_sales_mvASENABLEQUERYREWRITEASSELECTc.cust_id,s.channel_id,SUM(amount_sold)FROMsaless,customerscWHEREs.cust_id=c.cust_idGROUPBYc.cust_id,s.channel_id;CREATEMATERIALIZEDVIEWsales_products_mvASENABLEQUERYREWRITEASSELECTs.time_id,d_nameFROMsaless,productspWHEREd_id=d_id(+);TheNeedforNestedMaterializedViews

Typicaldatawarehouseneed:Createaggregatematerializedviewsusingdifferentgroupingcolumnsonasinglejoin.Maintainingthematerializedviewsistimeconsumingbecausetheunderlyingjoinisperformedmultipletimes.SALES_TIME_PROD_MVPRODUCTSSALESTIMESSALES_PROD_TIME_MVSametablesjoinedDifferentgroupingcolumnsPRODUCTSSALESTIMESUsingNestedMaterializedViews

Definitionisbasedonanothermaterializedview.Definitioncanalsoreferencenormaltables.PRODUCTSSALESTIMESSALES_PROD_TIME_JOINSALES_TIME_PROD_MVSALES_PROD_TIME_MVTablesarejoinedonetime.NestedMaterializedViews:RestrictionsAnestedmaterializedviewcannotbeaparentandagrandparentasshown:SALESTIMESSALES_TIMES_JOINPRODUCTSSALES_TIME_PROD_SUMParentofPRODUCTSGrandparentofPRODUCTSMaterializedView:ExampleCREATEMATERIALIZEDVIEWcust_sales_mv

PCTFREE0TABLESPACEexampleSTORAGE(INITIAL1MNEXT1MPCTINCREASE0)BUILDDEFERREDREFRESHCOMPLETEENABLEQUERYREWRITE ASSELECTc.cust_id,s.channel_id,SUM(amount_sold)FROMsaless,customerscWHEREs.cust_id=c.cust_idGROUPBYc.cust_id,s.channel_idORDERBYc.cust_id,s.channel_id;NameStorageoptionsWhentobuilditHowtorefreshthedataUsethisforqueryrewriteDetail

queryDetailtablesMVkeysMaterializedViewsStorageWhenamaterializedviewiscreated,thefollowingobjectsarecreated:AcontainertabletostorethematerializedviewrowsThematerializedviewitselfOneindexformaterializedviewswithaggregatesonlyTheOBJECT_TYPEcolumnofDBA_OBJECTScontainsMATERIALIZED

VIEWfortheobject.Thecontainertablehasthesamenameasthematerializedviewobject.Acontainertablecanbeprebuilt.EstimatingMaterializedViewSize>VARIABLEnum_rowsNUMBER;>VARIABLEmv_sizeNUMBER;EXECDBMS_MVIEW.ESTIMATE_MVIEW_SIZE(-'simple_store',-'SELECTc.cust_state_province,'||-'SUM(amount_sold)'||-'FROMsaless,customersc'||-'WHEREs.cust_id=c.cust_id'||-'GROUPBYc.cust_state_province',-:num_rows,:mv_size);EstimatedrowsEstimatedstorageinbytesStatementIDforEXPLAINPLANSpecifyingBuildMethodsTwobuildmethodsareavailablewhencreatingthematerializedview:BUILDDEFERRED:CreatedbutnotpopulatedBUILDIMMEDIATE:CreatedandpopulatedTheBUILD_MODEcolumninDBA_MVIEWScontainsthemethodused.SpecifyingRefreshOptionsSpecifyhowthematerializedviewshouldberefreshedfromthedetailtables:COMPLETEFASTFORCENEVERTheREFRESH_METHODcolumninDBA_MVIEWScontainstheoptionvalue.SpecifyingRefreshExecutionModesTworefreshexecutionmodes:ONDEMAND:ManualONCOMMIT:Refreshdoneattransactioncommit;onlypossibleforfast-refreshablematerializedviews.Incaseoffailure,subsequentrefreshesaremanual.Schedule:AtregularintervalsTheREFRESH_MODEcolumninDBA_MVIEWScontainstherefreshexecutionmodevalue.UsingColumnAliases

inMaterializedViewsCREATEMATERIALIZEDVIEWsales_mvENABLEQUERYREWRITEASSELECTs.time_idsales_tid,c.time_idcosts_tidFROMsaless,productsp,costscWHEREd_id=d_idANDd_id=d_idANDd_nameIN(SELECTprod_name

FROMproducts);SELECTs.time_id,c.time_idFROMsaless,productsp,costscWHEREd_id=d_idANDd_id=d_idANDd_nameIN(SELECTprod_nameFROMproducts);QueryrewriteCREATEMATERIALIZEDVIEWsales_mv(sales_tid,costs_tid)ENABLEQUERYREWRITEASSELECTs.time_id,c.time_idFROMsaless,productsp,costscWHEREd_id=d_idANDd_id=d_idANDd_nameIN(SELECTprod_nameFROMproducts);SELECTs.time_id,c.time_idFROMsaless,productsp,costscWHEREd_id=d_idANDd_id=d_idANDd_nameIN(SELECTprod_nameFROMproducts);UsingMaterializedView

ColumnAliasListsQueryrewriteRegisteringUser-Defined

MaterializedViewTablesRegisterexistingmaterializedviewtableswithONPREBUILTTABLE.Theregisteredmaterializedviewcanbeusedforqueryrewrites.Itcanbemaintainedbyrefreshmethods.Requirements:Tableandmaterializedviewhavethesamename.Columnaliasesindetailquerymustcorrespond.Columndatatypesmustmatch;canusetheWITHREDUCED

PRECISIONclause.Tablecolumnsnotreferencedinthedefiningquery

areunmanagedcolumns.Tableremainsafterthematerializedviewisdropped.PartitioningandMaterializedViewsPartitioningthefacttables:ImprovestheopportunityoffastrefreshingthematerializedviewMayenablePartitionChangeTracking(PCT)refreshonthematerializedviewPartitioningthematerializedview:Partitionpruningcanbeusedforqueryrewrite.PartitionedMaterializedView:ExampleCREATEMATERIALIZEDVIEWpart_sales_mvPARALLELPARTITIONBYLIST(gid)(PARTITIONg1VALUES(0),PARTITIONg2VALUES(1),PARTITIONg3VALUES(3))BUILDIMMEDIATEREFRESHCOMPLETEENABLEQUERYREWRITEASSELECTprod_id,cust_id,time_id,GROUPING_ID(prod_id,cust_id,time_id)ASgid,sum(amount_sold)ASsum_salesFROMsalesGROUPBYGROUPINGSETS((prod_id,cust_id,time_id),(prod_id,cust_id),(prod_id));UsingEnterpriseManagerto

CreateMaterializedViewscr_mv_gen.gifPrivilegesRequiredto

CreateMaterializedViewsMustbegranteddirectly,notthroughrolesTocreateamaterializedviewinyourschema:CREATE

MATERIALIZED

VIEW,andCREATE

[ANY]

TABLE,andSELECTprivilegeoneachdetailtablenotownedTocreateamaterializedviewinanotherschema:CREATE

ANY

MATERIALIZED

VIEW,andMaterializedviewownermusthaveCREATE

[ANY]

TABLE,andSELECTprivilegeoneachdetailtablenotownedAdditionalPrivilegesRequiredto

CreateMaterializedViewsTocreateamaterializedviewrefreshedatcommittime:ON

COMMIT

REFRESHobjectprivilegeoneachdetailtablenotowned,orON

COMMIT

REFRESHsystemprivilegeToenablequeryrewrite:DetailtableownermusthaveQUERY

REWRITEsystemprivilegeIfyoudonotowndetailtables:GLOBAL

QUERY

REWRITEsystemprivilegeorQUERY

REWRITEoneachdetailtablenotownedSELECT

WITH

GRANT

OPTIONifmaterializedviewisdefinedonaprebuilttableGlobalizationandMaterializedViewsMaterializedviewsusethesettingsineffectduringmaterializedviewcreation.Alwaysspecifyglobalizationparameterstoensurethatcorrectresultsarereturned.WHEREvarchar_col=TO_DATE('01-FEB-02')TO_DATE('01-FEB-2002','DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN')GlobalizationParametersSignificantforMaterializedViewsNLS_LANGUAGENLS_TIMESTAMP_TZ_FORMATNLS_ISO_CURRENCYNLS_TIMESTAMP_FORMATNLS_DUAL_CURRENCYNLS_TIME_TZ_FORMATNLS_DATE_LANGUAGENLS_TIME_FORMATNLS_DATE_FORMATNLS_NUMERIC_CHARACTERSNLS_CALENDARNLS_TERRITORYNLS_CURRENCYNLS_SORTNLS_COMPAddingCommentstoMaterializedViewsAddingacommentforanexistingmaterializedview:Viewingcomments:COMMENTONMATERIALIZEDVIEWcust_sales_mvIS'salesmaterializedview';SELECTmview_name,commentsFROMuser_mview_commentsWHEREmview_name='CUST_SALES_MV';AlteringMaterializedViewsALTERMATERIALIZEDVIEWcust_sales_mvDISABLEQUERYREWRITE;Changingtherefreshoptionandrefreshmode:Recompilingthematerializedview:Enablingordisablingitsuseforqueryrewrite:ALTERMATERIALIZEDVIEWcust_sales_mvREFRESHFASTONCOMMIT;ALTERMATERIALIZEDVIEWcust_sales_mvCOMPILE;AlteringMaterializedViewsAllocatinganextent:Modifyingtheloggingattribute:ALTERMATERIALIZEDVIEWcust_sales_mvALLOCATEEXTENT;ALTERMATERIALIZEDVIEWcust_sales_mvNOLOGGING;MaintainingPartitionsof

aMaterializedViewALTERMATERIALIZEDVIEWsales_mv

TRUNCATEPARTITIONyear_1995;ALTERMATERIALIZEDVIEWsales_mv

DROPPARTITIONyear_1994;ALTERMATERIALIZEDVIEWfact_mv

EXCHANGEPARTITIONyear_2001