Materialized Views

Purpose

A materialized view is a database object that contains the results of a query. The FROM clause of the query can name tables, views, and other materialized views. Collectively these objects are called master tables (a replication term) or detail tables (a data warehousing term). This reference uses "master tables" for consistency. The databases containing the master tables are called the master databases. When you create a materialized view, Oracle Database creates one internal table and at least one index, and may create one view, all in the schema of the materialized view. Oracle Database uses these objects to maintain the materialized view data. You must have the privileges necessary to create these objects.

ALL_REFRESH_DEPENDENCIES	GV$MREFRESH	SNAP$
COL$	SLOG$	TAB$
DBA_REGISTERED_SNAPSHOT_GROUPS

-- plus the objects returned by the following query
SELECT view_name
FROM dba_views
WHERE view_name LIKE '%MVIEW%'
ORDER BY 1;

DBMS_OFFLINE_SNAPSHOT	DBMS_SNAPSHOT_LIB	DBMS_SNAPSHOT_UTIL
DBMS_SNAPSHOT

CREATE MATERIALIZED VIEW <schema.name> PCTFREE <integer> PCTUSED <integer> TABLESPACE <tablespace_name> BUILD IMMEDIATE REFRESH <FAST | FORCE> ON <COMMIT | DEMAND> <USING INDEX | USING NO INDEX> INITRANS <integer> STORAGE CLAUSE AS (<SQL statement>);

conn uwclass/uwclass@pdbdev CREATE MATERIALIZED VIEW mv_simple TABLESPACE uwdata BUILD IMMEDIATE REFRESH FAST ON COMMIT AS SELECT * FROM servers; -- create refresh log then repeat (Click Here) CREATE MATERIALIZED VIEW mv_simple TABLESPACE uwdata BUILD IMMEDIATE REFRESH FAST ON COMMIT AS SELECT * FROM servers; desc user_snapshots SELECT name, table_name, updatable, refresh_method, refresh_mode FROM user_snapshots; set long 100000 SELECT name, query FROM user_snapshots; SELECT name, last_refresh FROM user_mview_refresh_times; SELECT table_name FROM user_tables; SELECT constraint_name, table_name, constraint_type FROM user_constraints; CREATE OR REPLACE VIEW servers_view AS SELECT * FROM servers; desc servers desc servers_view desc mv_simple SELECT DISTINCT network_id FROM servers; SELECT DISTINCT network_id FROM servers_view; SELECT DISTINCT network_id FROM mv_simple; UPDATE servers SET network_id = 10 WHERE network_id = 6; SELECT DISTINCT network_id FROM servers; SELECT DISTINCT network_id FROM servers_view; SELECT DISTINCT network_id FROM mv_simple; COMMIT; SELECT DISTINCT network_id FROM servers; SELECT DISTINCT network_id FROM servers_view; SELECT DISTINCT network_id FROM mv_simple;