Materialized View

From Oracle FAQ
Jump to: navigation, search

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.

Examples[edit]

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[edit]

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
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z #