Big Bigger BI

All this started with the observation that the initial create of an MV is much faster than a complete refresh of the same MV. Although the same amount of data has to be processed in the same way. Why?!

Since Oracle 10g a complete refresh of an MV is implemented as a Delete + Insert instead of an Truncate + Insert as in Oracle 9i and older. The point in Delete+Insert is there is always data provided. As long as the refresh is running the old state. As soon as the commit occured the refreshed data. This can be crucial in an OLTP environment. In a nightly batch run in an DW environment it's not.

DBMS_MVIEW.REFRESH ( { list IN VARCHAR2, | tab IN OUT DBMS_UTILITY.UNCL_ARRAY,} method IN VARCHAR2 := NULL, rollback_seg IN VARCHAR2 := NULL, push_deferred_rpc IN BOOLEAN := true, refresh_after_errors IN BOOLEAN := false, purge_option IN BINARY_INTEGER := 1, parallelism IN BINARY_INTEGER := 0, heap_size IN BINARY_INTEGER := 0, atomic_refresh IN BOOLEAN := true); (Source: http://docs.oracle.com/cd/B10501_01/server.920/a96568/rarmviea.htm)

There is an option called "atomic_refresh". By default this otion is true. Oracle says: "If this parameter is set to true, then the list of materialized views is refreshed in a single transaction. All of the refreshed materialized views are updated to a single point in time. If the refresh fails for any of the materialized views, none of the materialized views are updated. If this parameter is set to false, then each of the materialized views is refreshed in a separate transaction." (Source: http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_mview.htm ).

So if there's just one MV in the list, it shouldn't change anything. However it does. To put it in easy words: it changes the behaviour to a truncate+insert -> http://www.orafaq.com/wiki/Materialized_View