“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, and I am also a naturalized Swedish citizen. I am a former Oracle ACE and, to keep up with academic research, I am a Computer Scientist with an MSc in Data Science and another MSc in Software Engineering. I have over ten years of experience working with companies such as IBM, Epico Tech, and Playtech across three different countries (Brazil, Hungary, and Sweden), and I have joined projects remotely in many others. I am super excited to share my interests in Databases, Cybersecurity, Cloud, Data Science, Data Engineering, Big Data, AI, Programming, Software Engineering, and data in general.
(Continue reading)