“Performing a refresh operation requires temporary space to rebuild the indexes and can require additional space for performing the refresh operation itself.”
“Not all materialized views may be fast refreshable. Therefore, use the package DBMS_MVIEW.EXPLAIN_MVIEW to determine what refresh methods are available for a materialized view.”
Refresh methods:
- Incremental : Two types: logg-based refresh and partition change tracking (PCT).
“The incremental refresh is commonly called FAST refresh as it usually performs faster than the complete refresh.” - Complete refresh
DBMS_MVIEW package
- DBMS_MVIEW.REFRESH
- DBMS_MVIEW.REFRESH_ALL_MVIEWS
- DBMS_MVIEW.REFRESH_DEPENDENT
Traditional methods:
FAST (‘F’), COMPLETE (‘C’), PCT (‘P’), and FORCE (‘?’).
- Monitoring and information about the refresh
- V$SESSION_LONGOPS;
- DBA_JOBS_RUNNING;
- MVIEW_NAME;
https://www.youtube.com/watch?v=_JOu5cg70Js – Oracle sql materialized view refresh fast by Sridhar Raghavan
Data Warehousing and Business Intelligence -> Database Data Warehousing Guide -> 7 Refreshing Materialized Views
Hi! I am Bruno, a Brazilian born and bred. Former Oracle ACE, Computer Scientist, MSc in Data Science, over ten years of experience in companies such as IBM, Epico Tech, and Playtech based in three different countries (Brazil, Hungary, and Sweden) and joined projects remotely in many others. I am super excited to show you my interest in Databases, Cloud, Data Science, Data Engineering, Bigdata, AI, Programming, Software Engineering, and data in general.
(Continue reading)