Materialized Views in Oracle

Materialized Views:
------------------
A materialized view provides access to table data by storing the results of a query in a
separate schema object.
Unlike an ordinary view, which does not take up any storage space or contain any data,
a materialized view contains the rows resulting from a query against one or more base tables or views.
A materialized view can be stored in the same database as its base tables or in a different database.
Materialized views stored in the same database as their base tables can
improve queryperformance through query rewrites.
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.

Refresh methods:
--------------
refresh Force on demand
refresh fast on commit
refresh Complete

Syntax:
-------
CREATE MATERIALIZED VIEW <schema.name>
BUILD IMMEDIATE
REFRESH <FAST | FORCE> ON <COMMIT | DEMAND>
<USING INDEX | USING NO INDEX>
AS (<SQL statement>);

Materialized View Logs:
---------------------
A materialized view log is a schema object that records changes to a master table’s
data so that a materialized view defined on the master table can be refreshed incrementally.
Each materialized view log is associated with a single master table. The materialized
view log resides in the same database and schema as its master table.

SQL> CREATE MATERIALIZED VIEW LOG ON Emp
     WITH PRIMARY KEY, ROWID;

Query Rewrite:
-------------
Query rewrite is a mechanism where Oracle or applications from the end user or database transparently
improve query responsetime, by automatically rewriting the SQL query to use the materialized view
instead of accessing the original tables.
Query rewrites are particularly useful in a data warehouse environment.

Refreshing materialized views:
-----------------------------
In Oracle, if you specify REFRESH FAST for a single-table aggregate Oracle materialized view,
you must have created a materialized view log for the underlying table, or the refresh command will fail.
To refresh the Oracle materialized view, call one of the procedures in DBMS_MVIEW.
The DBMS_MVIEW package provides three types of refresh operations:
DBMS_MVIEW.REFRESH:  Refreshes one or more Oracle materialized views
DBMS_MVIEW.REFRESH_ALL_MVIEWS:  Refreshes all Oracle materialized views
DBMS_MVIEW.REFRESH_DEPENDENT:  Refreshes all table-based Oracle materialized views

You can use the DBMS_MVIEW package to manually invoke either a fast refresh or a complete refresh,
 where "F" equals Fast Refresh and "C" equals Complete Refresh:
EXECUTE DBMS_MVIEW.REFRESH('emp_dept_sum','F');

Creating an Oracle materialized view:
------------------------------------
Step 1
------
SQl> select * from v$parameter
chack the above query if name  column  containe query_rewrite_enabled =TRUE then no change required else
Change it in the init.ora file
query_rewrite_enabled = true
query_rewrite_integrity = enforced
compatible = 8.1.5.0.0 (or greater)

Step 2------
CREATE  MATERIALIZED VIEW EMP_SUM
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
ENABLE QUERY REWRITE
AS
  SELECT deptno,job,SUM(sal) FROM emptest GROUP BY deptno,job;

Step 3------
execute dbms_mview.refresh('emp_sum');

Step 4------
CREATE MATERIALIZED VIEW LOG ON
   emp_sum
WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON
   dept
WITH ROWID;

Step 5------
EXECUTE DBMS_MVIEW.REFRESH('emp_sum');

Refresh Complete----------------
CREATE MATERIALIZED VIEW mv_complete
REFRESH COMPLETE
START WITH SYSDATE
NEXT SYSDATE + 1 AS
SELECT deptno,COUNT(*)
FROM emp
GROUP BY deptno;

Note: To create a materialized view that refreshes at 3:00am in the morning:
SQL> SELECT TO_CHAR(TRUNC(SYSDATE) + 3/24, 'MM/DD/YYYY HH:MI:SS') FROM dual;

CREATE MATERIALIZED VIEW mv_complete
REFRESH COMPLETE
START WITH SYSDATE
NEXT TO_CHAR(TRUNC(SYSDATE) + 3/24, 'MM/DD/YYYY HH:MI:SS') AS
SELECT deptno,COUNT(*)
FROM emp
GROUP BY deptno;

Materialized View Partition Tracking Change
------------------------------------------
When the source or detail tables have partitions,
the refresh mechanism follows the partition change tracking (PCT) method.
Let us look at an example to show the creation of a materialized view.
 Let us assume we have a table list_sales_time created as follows.
CREATE table list_sales_time (time_id, cust_id, month, week, quantity_sold, amount_sold)
PARTITION BY LIST (week)(
partition m1 values (48, 49, 50),
partition m3 values (5, 6, 7, 8, 9, 10, 11, 12),
partition others values (default)) ;
Then, the materialized view is defined.

CREATE MATERIALIZED VIEW pct_sales_materialized view
BUILD IMMEDIATE REFRESH FAST ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT ls.week, AVG(ls.quantity_sold) as avg,
COUNT(*) as cnt, COUNT(ls.quantity_sold) as cnt
FROM list_sales_time ls GROUP BY ls.week, ls.cust_id;

Creating Materialized Aggregate Views:
-------------------------------------
CREATE MATERIALIZED VIEW LOG ON times
   WITH ROWID, SEQUENCE (time_id, calendar_year)
   INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON products
   WITH ROWID, SEQUENCE (prod_id)
   INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW sales_mv
   BUILD IMMEDIATE
   REFRESH FAST ON COMMIT
   AS SELECT t.calendar_year, p.prod_id,
      SUM(s.amount_sold) AS sum_sales
      FROM times t, products p, sales s
      WHERE t.time_id = s.time_id AND p.prod_id = s.prod_id
      GROUP BY t.calendar_year, p.prod_id;

Periodic Refresh of Materialized Views:--------------------------------------
The following statement creates the primary key materialized view emp_data
CREATE MATERIALIZED VIEW LOG ON Emp
   WITH PRIMARY KEY
   INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW emp_data
   REFRESH FAST NEXT sysdate + 7
   AS SELECT * FROM emp;


Ur's
AmarAlam