Basic Syntax
The full syntax description for the CREATE MATERIALIZED VIEW command is available in the documentation. Here we will only concern ourselves with the basics.
CREATE MATERIALIZED VIEW view-name BUILD [IMMEDIATE | DEFERRED] REFRESH [FAST | COMPLETE | FORCE ] ON [COMMIT | DEMAND ] [[ENABLE | DISABLE] QUERY REWRITE] [ON PREBUILT TABLE] AS SELECT ...;
The
BUILD clause options are shown below.- IMMEDIATE : The materialized view is populated immediately.
- DEFERRED : The materialized view is populated on the first requested refresh.
The following refresh types are available.
- 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.
A refresh can be triggered in one of two ways.
- 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.
The
QUERY REWRITE clause tells the optimizer if the materialized view should be consider for query rewrite operations. An example of the query rewrite functionality is shown below.
The
ON PREBUILT TABLE clause tells the database to use an existing table segment, which must have the same name as the materialized view and support the same column structure as the query.CREATE MATERIALIZED VIEW MV_RFIDS
REFRESH FORCE ON DEMAND
AS
SELECT RFIDS.RFID_NO RFID_NO,RFIDS.RFID_TYPE RFID_TYPE,
RFIDS.STATUS STATUS
FROM DBA.RFIDS@Remote DB RFIDS;
begin
dbms_mview.refresh('rfids');
end;
