A materialized view (MV) is similar to a view but the data is actually stored on disk (view that materializes). Materialized views are often used for summary and pre-joined tables, or just to make a snapshot of a table available on a remote system. A MV must be refreshed when the data in the underlying tables is changed.
Create a MV:
CREATE MATERIALIZED VIEW my_test_mv AS SELECT * FROM scott.emp;
Refresh a MV:
EXEC dbms_mview.refresh('MY_TEST_MV', method=>'C');
Change of refresh behaviour in 10g
Upgrading from Oracle 9i to Oracle 10g will change the MV refresh behaviour. Oracle 10g will use the DELETE command to remove rows and a normal INSERT to repopulate it. In Oracle 9i and earlier releases, Oracle did a TRUNCATE and INSERT /*+APPEND*/, which is more efficient, but had the side effect that users will see no rows while the refresh is taking place.
If you prefer the older truncate/append behaviour, change the refresh method to set atomic_refresh = false. Here is an example:
BEGIN -- use this with 10g/11g to return to truncate/append behavior dbms_mview.refresh('MY_TEST_MV', method=>'C', atomic_refresh=>false); END; /
|Glossary of Terms|