Oracle英文版培训课件之Data Warehouse：Les11

RefreshingMaterializedViewsObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:DescribethevariousrefreshmethodsandmodesRefreshmaterializedviewsCreatefast-refreshablematerializedviewsExplainwhymaterializedviewscannotbefastrefreshableCreatematerializedviewlogsDescribethebenefitofPartitionChangeTrackingUseparallelrefreshesRefreshOptionsRefreshoptionsspecifiedwhenthematerializedviewiscreated:COMPLETE:Re-executesqueryFAST:RefreshisbasedondeltaUsingmaterializedviewlogsUsingdirectloaderlog:ALL_SUMDELTAUsingPartitionChangeTrackingrefreshFORCE:FASTifpossible,otherwiseCOMPLETENEVERFullNotesPageRefreshModesRefreshexecutionmodesspecifiedwhenthematerializedviewiscreated:ON

DEMAND:ManualrefreshusingproceduresinDBMS_MVIEWpackage(default)ON

COMMIT:RefreshperformedattransactioncommitOnlypossibleforfast-refreshable

materializedviewsON

COMMIT

REFRESHobjectprivilegeneededIncaseoffailure,subsequentrefreshesaremanualRefreshatScheduledTimeSchedulerefreshatmaterializedviewcreationtimewithSTARTWITHandNEXT:CREATEMATERIALIZEDVIEWemp_data_mv…REFRESHFASTNEXTsysdate+7…CREATEMATERIALIZEDVIEWcust_data_mv…REFRESHSTARTWITHROUND(SYSDATE+1)+11/24NEXTNEXT_DAY(TRUNC(SYSDATE),'MONDAY')+15/24…ManualRefreshUsingDBMS_MVIEWForON

DEMANDrefreshThreeproceduresintheDBMS_MVIEWpackage:REFRESHREFRESH_ALL_MVIEWSREFRESH_DEPENDENTTherefreshcanbeperformedby:Foregroundsession,ifnojobqueueprocessisstartedOneormultiplejobqueueprocesses,ifJOB_QUEUE_PROCESSESissetMultiplerefresh,whichcanbedoneinparallelFullNotesPageRefreshSpecificMaterializedViews

withREFRESHexecDBMS_MVIEW.REFRESH(-'MV1,MV2','FC','',FALSE,TRUE,0,0,0,FALSE);Listofmaterializedviews

RefreshmethodforeachDonotpushchanges

Continuewhenthereareerrors

Refresheachinaseparatetransaction

FullNotesPageRefreshAllMaterializedViewswithREFRESH_ALL_MVIEWS

VARIABLEfailNUMBER;execDBMS_MVIEW.REFRESH_ALL_MVIEWS(-:fail,'C','',TRUE,FALSE);Returnsnumberoffailedrefreshes

RefreshmethodforeachContinuewhenthereareerrors

Refresheachinaseparatetransaction

RefreshDependentMaterializedViewswithREFRESH_DEPENDENTRefreshallmaterializedviewsdirectlydependingonalistofdetailtablesormaterializedviews:VARIABLEfailNUMBER;execDBMS_MVIEW.REFRESH_DEPENDENT(-:fail,'CUSTOMERS,SALES','CF','',TRUE,FALSE);Listofmaterializedviews

RefreshmethodforeachContinuewhenthereareerrors

Refresheachinaseparatetransaction

FindingDependentMaterializedViewsFinddirectdependenciesonly.CanbeusedtodeterminethematerializedviewrefreshorderfornestedmaterializedviewsVARIABLEdeplistVARCHAR2(500)execDBMS_MVIEW.GET_MV_DEPENDENCIES(-'CUSTOMERS,SALES',:deplist);Listofmaterializedviews

Returnslistofdependentmaterializedviews

WhenDoesaCompleteRefreshOccur?Initialbuildofthematerializedview(BUILD

IMMEDIATE)FirstrefreshforBUILDDEFERREDmaterializedviewsManualrequestConditionsThatEffectPossibilityof

FastRefreshExistenceofmaterializedviewlogsMaterializedview’sdetailqueryTypesofmodificationsmadetothedetailtablesSequenceofmodificationsmadetothedetailtablesExplainMaterializedViewDBMS_MVIEW.EXPLAIN_MVIEWaccepts:Materializedviewname,orSQLstatementAdviseswhatispossiblefor:AnexistingmaterializedviewApotentialmaterializedviewbeforeyoucreateitResultsarestoredinMV_CAPABILITIES_TABLE(relationaltable)orinaVARRAY.utlxmv.sqlmustbeexecutedasthecurrentusertocreateMV_CAPABILITIES_TABLE.UsingDBMS_MVIEW.EXPLAIN_MVIEWEXECdbms_mview.explain_mview('sales_sum','123');SELECTcapability_name,possible,related_text,

msgtxtFROMmv_capabilities_tableWHEREstatement_id='123'ORDERBYseq;CAPABILITY_NAMEPREL_TEXTMSGTXT-…REFRESH_COMPLETEYREFRESH_FASTNREFRESH_FAST_AFTER_INSERTNTIMESmvlogmusthavenewvalues…MaterializedViewLogs:GeneralConceptsDefinition:TablethatcontainsrowsdescribingchangesmadetoadetailtableThelogsarerequirediffastrefreshisused.LogdefinitionmustincludetheWITH

ROWIDclause.Forjoinsandaggregatematerializedviewstobefast-refreshableafterINSERTs,logsmustinclude:EverycolumnreferencedinthematerializedviewTheINCLUDING

NEW

VALUESclauseAnINSERT,UPDATE,DELETEcombinationonmultipledetailtablesrequiresthelogtoincludetheSEQUENCEclause.SEQUENCEprovidesorderinginformation.CreatingaMaterializedViewLogCREATEMATERIALIZEDVIEWLOGONsh.salesTABLESPACEusersNOLOGGINGPARALLELWITHSEQUENCE,ROWID(quantity_sold,amount_sold)INCLUDINGNEWVALUES;RestrictionsforFast-RefreshableMaterializedViewsGeneralrestrictions:Nonon-repeatingexpressionsNoreferencestoRAWorLONG

RAWdatatypesSupportforsubqueriesintheWHEREclauselimitedtodatasubsetting(WHEREEXISTS)Additionalrestrictionsapplyforspecifictypesofmaterializedviews.IndexesandFast-Refreshable

MaterializedViewsFastrefreshjoinsandaggregatematerializedviewTheOracleserverautomaticallycreatesauniquefunctionalindexonamaterializedview’skeysforbetterfastrefreshperformance.Indexexpression:SYS_OP_MAP_NONNULL(keyn)USING[NO]INDEXclauseatcreationtimeFastrefreshjoins-onlymaterializedviewNoindexisautomaticallycreated.Forbetterfastrefreshperformance,createanindexonrowIDcolumnsoftheselectlist.SpecifyingREFRESHFORCEMaterializedviewmaybefastrefreshableinsome,butnotallcases.Joins-onlymaterializedviewwithoutrowIDsfromoneofitsdetailtablescanstillbefastrefreshedwheneverupdatingonlytheotherdetailtables.ShouldbeexceptionandshouldnotbegeneralizedUseREFRESHFORCEoptionduringcreationtoensurefastrefreshwheneverpossible.QueryDBA_MVIEWS.FAST_REFRESHABLEtodeterminewhetherthematerializedviewiseligibleforfastrefresh.FullNotesPagePartitionChangeTracking(PCT)RefreshFastrefreshusesmaterializedviewlogordirect-loaderlog.Certaincasespreventusingamaterializedviewlogthuspreventinggeneralfastrefresh.Droppingofadetailtable’spartitionMixedDMLwhenthematerializedviewusestheMINfunctionMaintainstalenessatpartitionlevel:OnlydetectedforpartitioneddetailtablesOnlyspecificsectionsofthematerializedviewneedtoberefreshedRowscorrespondingtoimpactedpartitionsonlyShouldbefasterthanacompleterefreshPCTRefreshRequirementsOneofthematerializedviewdetailtablesmustbepartitioned:RANGE,COMPOSITE,orLIST.Partitionkeymustconsistofasinglecolumn.SELECTandGROUP

BYlistmustcontain:partitionkeycolumn,partitionmarker(DBMS_MVIEW.PMARKER),ROWID,orjoin-dependentexpressionofoneofitsdetailtables.NoreferencetoviewsorouterjoinsWhenIsPCTRefreshUsed?Fastrefreshisrequested,andAdetailtableispartitioned,andPCTrefreshispossibleforthematerializedview,andOneorbothofthefollowingtwoconditionshaveoccurredondetailtables:PartitionDDLoperationsCombinationsofDML/direct-loadsnothandledbyfastrefreshRequestedusingPparameterwithrefreshproceduresFAST_PCTisindicatedinLAST_REFRESH_TYPE.PartitionKeyorPartitionMarker?Cardinality=#part_keysxdistinct(prod_name)CREATEMATERIALIZEDVIEWcost_product_mv(tid,pname,su,cu,cnt)BUILDIMMEDIATEREFRESHFASTONDEMANDENABLEQUERYREWRITEASSELECTtime_id,prod_name,SUM(unit_cost),COUNT(unit_cost),COUNT(*)FROMsh.costsc,ductspWHEREd_id=d_idGROUPBYtime_id,prod_name;PartitionKeyorPartitionMarker?Cardinality=#partitionsxdistinct(prod_name)CREATEMATERIALIZEDVIEWcost_product_pm_mv(pm,pname,su,cu,cnt)BUILDIMMEDIATEREFRESHFASTONDEMANDENABLEQUERYREWRITEASSELECTDBMS_MVIEW.PMARKER(c.rowid),prod_name,SUM(unit_cost),COUNT(unit_cost),COUNT(*)FROMcostsc,productspWHEREd_id=d_idGROUPBYDBMS_MVIEW.PMARKER(c.rowid),prod_name;PCTUsingROWIDCREATEMATERIALIZEDVIEWpct_rowid_mv(srid,prid,cid,pname)BUILDIMMEDIATEREFRESHFORCEENABLEQUERYREWRITEASSELECTs.rowid,p.rowid,s.channel_id,d_nameFROMsaless,productspWHEREd_id=d_id;ConsiderFresh:FastestRefreshScenarioFactMVDroppartitionALTERMATERIALIZEDVIEWCONSIDERFRESH123CONSIDERFRESHRefreshImplicationsSTALENESScolumnofDBA_MVIEWSsettoUNKNOWNuntilnextcompleterefreshUntilnextcompleterefresh,PCTrefreshnolongerpossibleFastrefreshstillpossibleGeneralTipsforMaterializedViewRefreshForcompleterefreshusingDBMS_MVIEWinterface,setATOMIC_REFRESHtoFALSE.Disablematerializedviewloggingbeforerefreshing(NOLOGGING).Alwayscheckalert.logandtracefilesafterarefresh.Trytoperformonlyonetyp