Oracle: Materialized Views

 Materialized Views

Overview:
Materialized views are a data warehousing/decision support system tool that can increase by many orders of magnitude the speed of queries that access a large number of records.

A materialized view is a database object that contains the results of a query. They are local copies of data located remotely, or are used to create summary tables based on aggregations of a table's data. Materialized views, which store data based on remote tables are also, know as snapshots.

Types of Materialized Views:
Read-Only Materialized Views : No FOR UPDATE clause
Updatable Materialized Views : Having FOR UPDATE clause
Writeable materialized view :

Setup of Materialized Views:
Set the following mandatory INIT.ORA parameter :

• COMPATIBLE >= 8.1.0
• QUERY_REWRITE_ENABLED = TRUE
• QUERY REWRITE INTEGRITY
  
  • ENFORCED - Queries will be rewritten using only constraints and rules that are enforced and guaranteed by Oracle.
  • TRUSTED - Queries will be rewritten using the constraints that are enforced by Oracle.
  • STALE TOLERATED - Queries will be rewritten to use materialized views even if Oracle knows the data contained in the materialized view is ' stale ' (out-of-sync with the details).

The needed privileges are as follows:

• CREATE SESSION
• CREATE TABLE
• CREATE MATERIALIZED VIEW
• QUERY REWRITE

grant query rewrite to scott;
alter session set query_rewrite_enabled=true;
alter session set query_rewrite_integrity=enforced;

Syntax
create materialized view mv_emp
  build immediate
  refresh on commit
  enable query rewrite
as
select depno, count(*)
  from emp
  group by deptno;

REFRESH Clause:
    [refresh [fast|complete|force]
    [on demand | commit]
    [start with date] [next date]
    [with {primary key|rowid}]]

The following statement creates the primary-key materialized view.

Syntax
CREATE MATERIALIZED VIEW mv_emp_pk
REFRESH FAST START WITH SYSDATE
NEXT SYSDATE + 1/48
WITH PRIMARY KEY
AS SELECT * FROM emp;

Uses of Materialized Views
 - Biggeset performance improvement .
 - Greatly reduces the load on the machine.
 - Less physical reads - There is less data to scan through.
 - Less writes -  Sorting/aggregating is not done frequently witch could potentially decreas CPU
- Consumption  - Calculating aggregates and functions are not done on the original data which could markedly faster response times