MATERIALIZED VIEW

 CREATE MATERIALIZED VIEW 

syntax
CREATE MATERIALIZED VIEW view-name
BUILD [IMMEDIATE | DEFERRED]
REFRESH [FAST | COMPLETE | FORCE ]
ON [COMMIT | DEMAND ]
[[ENABLE | DISABLE] QUERY REWRITE]
AS
SELECT ...;



Pre-Built
CREATE MATERIALIZED VIEW view-name
ON PREBUILT TABLE
REFRESH [FAST | COMPLETE | FORCE ]
ON [COMMIT | DEMAND ]
[[ENABLE | DISABLE] QUERY REWRITE]
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.

Check Privileges
Check the user who will own the materialized views has the correct privileges.
At minimum they will require the CREATE MATERIALIZED VIEW privilege. If they are
creating materialized views using database links, you may want to grant them CREATE
DATABASE LINK privilege also.

CONNECT sys@db2
GRANT CREATE MATERIALIZED VIEW TO scott;
GRANT CREATE DATABASE LINK TO scott;

Create Materialized View
Connect to the materialized view owner and create the database link and the materialized view itself.

CONNECT scott/tiger@db2
CREATE DATABASE LINK DB1.WORLD CONNECT TO scott IDENTIFIED BY tiger USING 'DB1.WORLD';

CREATE MATERIALIZED VIEW emp_mv
BUILD IMMEDIATE
REFRESH FORCE
ON DEMAND
AS
SELECT * FROM emp@db1.world;
Alternatively, we could have used a prebuilt table, as shown below.

Create the tale first. This could be populated
 using an export/import.
CREATE TABLE emp_mv AS
SELECT * FROM emp@db1.world;

Build the materialized view using the existing table segment.
CREATE MATERIALIZED VIEW emp_mv
REFRESH FORCE
ON DEMAND
ON PREBUILT TABLE
AS
SELECT * FROM emp@db1.world;
Remember to gather stats after building the materialized view.

BEGIN
  DBMS_STATS.gather_table_stats(
    ownname => 'SCOTT',
    tabname => 'EMP_MV');
END;
/

Create Materialized View Logs
Since a complete refresh involves truncating the materialized view segment and
re-populating it using the related query, it can be quite time consuming and involve
a considerable amount of network traffic when performed against a remote table.
To reduce the replication costs, materialized view logs can be created to capture all
changes to the base table since the last refresh. This information allows a fast
refresh, which only needs to apply the changes rather than a complete refresh of the
materialized view.

To take advantage of the of the fast refresh, connect to the master instance and
create the materialized view log.

CONNECT scott/tiger@db1

CREATE MATERIALIZED VIEW LOG ON scott.emp
TABLESPACE users
WITH PRIMARY KEY
INCLUDING NEW VALUES;

Refresh Materialized Views
If a materialized view is configured to refresh on commit, you should never need to
manually refresh it, unless a rebuild is necessary. Remember, refreshing on commit is
a very intensive operation for volatile base tables. It makes sense to use fast
refreshes where possible.
For on demand refreshes, you can choose to manually refresh the materialized view or
refresh it as part of a refresh group.
The following code creates a refresh group defined to refresh every minute and assigns
a materialized view to it.

BEGIN
   DBMS_REFRESH.make(
     name                 => 'SCOTT.MINUTE_REFRESH',
     list                 => '',
     next_date            => SYSDATE,
     interval             => '/*1:Mins*/ SYSDATE + 1/(60*24)',
     implicit_destroy     => FALSE,
     lax                  => FALSE,
     job                  => 0,
     rollback_seg         => NULL,
     push_deferred_rpc    => TRUE,
     refresh_after_errors => TRUE,
     purge_option         => NULL,
     parallelism          => NULL,
     heap_size            => NULL);
END;
/

BEGIN
   DBMS_REFRESH.add(
     name => 'SCOTT.MINUTE_REFRESH',
     list => 'SCOTT.EMP_MV',
     lax  => TRUE);
END;
/
A materialized view can be manually refreshed using the DBMS_MVIEW package.
EXEC DBMS_MVIEW.refresh('EMP_MV');
Rather than using a refresh group, you can schedule DBMS_MVIEW.REFRESH called using the Oracle Scheduler


Cleaning Up
To clean up we must remove all objects.

CONNECT scott/tiger@db2
DROP MATERIALIZED VIEW emp_mv;
DROP DATABASE LINK DB1.WORLD;

BEGIN
  DBMS_REFRESH.destroy(name => 'SCOTT.MINUTE_REFRESH');
END;
/

CONNECT scott/tiger@db1
DROP MATERIALIZED VIEW LOG ON scott.emp;

Aggregations and Transformations
Materialized views can be used to improve the performance of a variety of queries,
including those performing aggregations and transformations of the data. This allows
the work to be done once and used repeatedly by multiple sessions, reducing the total
load on the server.

The following query does an aggregation of the data in the EMP table.
CONN scott/tiger
SET AUTOTRACE TRACE EXPLAIN

SELECT deptno, SUM(sal)
FROM   emp
GROUP BY deptno;

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     3 |    21 |     4  (25)| 00:00:01 |
|   1 |  HASH GROUP BY     |      |     3 |    21 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |    98 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Create a materialized view to perform the aggregation in advance, making sure you 
specify the ENABLE QUERY REWRITE clause.
CREATE MATERIALIZED VIEW emp_aggr_mv
BUILD IMMEDIATE
REFRESH FORCE
ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT deptno, SUM(sal) AS sal_by_dept
FROM   emp
GROUP BY deptno;

EXEC DBMS_STATS.gather_table_stats(USER, 'EMP_AGGR_MV');
The same query is now rewritten to take advantage of the pre-aggregated data in the
materialized view, instead of the session doing the work for itself.

--ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;
--ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;
SET AUTOTRACE TRACE EXPLAIN

SELECT deptno, SUM(sal)
FROM   emp
GROUP BY deptno;

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |     3 |    21 |     3   (0)| 00:00:01 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| EMP_AGGR_MV |     3 |    21 |     3   (0)| 00:00:01 |