Materialized View with Examples

Materialized View

Materialized View:- Materialized view is just like a snapshot. We create materialized view when query take lot of time for execution and in our code it execute many times.

Syntax:-

CREATE MATERIALIZED VIEW VIEW_NAME
BUILD IMMEDIATE/DEFERRED
REFRESH FAST/COMPLETE/FORCE
ON DEMAND/COMMIT
AS SELECT .........;


•IMMEDIATE : The materialized view is populated immediately.
•DEFERRED : The materialized view is populated on the first requested refresh.(WHEN WE DONOT REFERESH MANULLY THEN DATA DOES NOT COMES IN MATERIALIZED VIEW)

•FAST : A fast refresh is attempted. If materialized view logs are not present against the source tables in advance, the creation fails.
•COMPLETE : The table segment supporting the materialized view is truncated and repopulated completely using the associated query.
•FORCE : A fast refresh is attempted. If one is not possible a complete refresh is performed.


•ON COMMIT : The refresh is triggered by a committed data change in one of the dependent tables.
•ON DEMAND : The refresh is initiated by a manual request or a scheduled task




EXAMPLE:--

CREATE MATERIALIZED VIEW XXC_ALL_OBJECTS
BUILD IMMEDIATE
REFRESH FAST
ON COMMIT
AS
SELECT * FROM ALL_OBJECTS;

SELECT COUNT(*) FROM ALL_OBJECTS;

SELECT COUNT(*) FROM XXC_ALL_OBJECTS;