Oracle 10g introduced the atomic refresh mechanism,
materialized view is refreshed as a whole, as a single
Now you might expect that an atomic refresh is faster
than a manual rebuild or a refresh full, but this is not
always the case.
When "atomic refresh" is set to TRUE (in
dbms_mview.refresh_all_mviews), than the whole refresh is
done in a single transaction.
If you do not specify an atomic refresh (by setting
"atomic refresh = FALSE" in dbms_mview.refresh_all_mviews)
then you can optimize the materialized view refresh with
- Parallel DML
- Truncate DDL
In other words, setting atomic_refresh=false tells Oracle
to truncate data instead of delete the rows, resulting in
better performance than setting "atomic_refresh=true".
Note that when a materialized view is refreshed in atomic
mode, it is eligible for query rewrite if the rewrite
integrity mode is set to stale_tolerated. Atomic
refresh cannot be guaranteed when refresh is performed on
You can use the DBMS_MVIEW
package to manually invoke either a fast refresh or a
complete refresh, where F equals Fast Refresh and
C equals Complete Refresh: