Materialized View

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

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
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	#