Materialized Views

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 (in previous versions of oracle), Materialized Views can increase many times the speed of queries that access huge data records

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.

The following types of refresh methods are supported by Oracle.

Complete – build from scratch
Fast – only apply the data changes
Force – try a fast refresh, if that is not possible, do a complete refresh
Never – never refresh the materialized view

Materialized View Log:

When DML changes are made to master table data, Oracle Database stores rows describing those changes in the materialized view log and then uses the materialized view log to refresh materialized views based on the master table. This process is called incremental or fast refresh. Without a materialized view log, Oracle Database must re-execute the materialized view query to refresh the materialized view. This process is called a complete refresh. Usually, a fast refresh takes less time than a complete refresh.

A materialized view log is located in the master database in the same schema as the master table. A master table can have only one materialized view log defined on it. Oracle Database can use this materialized view log to perform fast refreshes for all fast-refreshable materialized views based on the master table.

To fast refresh a materialized join view, you must create a materialized view log for each of the tables referenced by the materialized view.

Privileges required:

create materialized view
create any materialized view
drop any materialized view
delete any table
insert any table
lock any table
select any table
under any table
update any table
create table
create view

There is one mandatory INIT.ORA parameter necessary for materialized views to function, this is the COMPATIBLE parameter. The value of COMPATIBLE should be set to 8.1.0, or above, in order for query rewrites to be functional. If this value is not set appropriately, query rewrite will not be invoked.

There are two other relevant parameters that may be set at either the system-level via the INIT.ORA file, or the session-level via the ALTER SESSION command.

       QUERY_REWRITE_ENABLED

Unless the value of this parameter is set to TRUE, query rewrites will not take place. The default value is FALSE.

       QUERY REWRITE INTEGRITY
This parameter controls how Oracle rewrites queries and may be set to one of three values:

ENFORCED – Queries will be rewritten using only constraints and rules that are enforced and guaranteed by Oracle. There are mechanisms, by which we can tell Oracle about other inferred relationships, and this would allow for more queries to be rewritten, but since Oracle does not enforce those relationships, it would not make use of these facts at this level.

TRUSTED – Queries will be rewritten using the constraints that are enforced by Oracle, as well as any relationships existing in the data that we have told Oracle about, but are not enforced by the database.

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). This might be useful in an environment where the summary tables are refreshed on a recurring basis, not on commit, and a slightly out-of-sync answer is acceptable.

Syntax (Fast Refresh)

CREATE MATERIALIZED VIEW <schema.name>
PCTFREE <integer>
PCTUSED <integer>
TABLESPACE <tablespace_name>
BUILD IMMEDIATE
REFRESH <FAST | FORCE> ON <COMMIT | DEMAND>
<USING INDEX | USING NO INDEX>
INITRANS <integer>
STORAGE CLAUSE
AS (<SQL statement>);

Example: (Here we need to create a materialized view log first)

CREATE MATERIALIZED VIEW LOG ON employees;

CREATE MATERIALIZED VIEW mv_fast
TABLESPACE example
BUILD IMMEDIATE
REFRESH FAST ON COMMIT AS
SELECT * FROM employees;

Syntax (Force Refresh)

CREATE MATERIALIZED VIEW <schema.name>
PCTFREE <integer>
PCTUSED <integer>
TABLESPACE <tablespace_name>
BUILD IMMEDIATE
REFRESH <FAST | FORCE> ON <COMMIT | DEMAND>
AS (<SQL statement>);

 Example:

CREATE MATERIALIZED VIEW mv_force
TABLESPACE example
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
WITH ROWID AS
SELECT * FROM employees;

Syntax (Complete Refresh)

CREATE MATERIALIZED VIEW <schema.name>
PCTFREE <integer>
PCTUSED <integer>
TABLESPACE <tablespace_name>
REFRESH <COMPLETE | FORCE>
START WITH <date>
NEXT <date_calculation>
[FOR UPDATE]
AS (<SQL statement>);

 Example:

CREATE MATERIALIZED VIEW mv_complete
TABLESPACE example
REFRESH COMPLETE
START WITH SYSDATE
NEXT SYSDATE + 1
AS SELECT a.employee_id,a.last_name,a.salary,b.department_id
from employees a,departments b
where a.department_id=b.department_id;
Materialized views significantly improves the performance of Oracle systems required to process complex SQL statements.

—————A Performance Test on Materialized views——————————————–

SQL> create table sample as select rownum as id, owner,object_name , object_type from all_objects ;

Table created.
SQL> CREATE INDEX ind ON sample(ID);

Index created.
SQL> analyze table sample compute statistics;

Table analyzed.
SQL> set autotrace traceonly;

SQL> set timing on

SQL>  select owner,count(object_name) from sample group by owner;

24 rows selected.

Elapsed: 00:00:00.10

Execution Plan
———————————————————-
Plan hash value: 3791231185

Execution Plan
———————————————————-
Plan hash value: 3791231185

—————————————————————————–
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————–
|   0 | SELECT STATEMENT   |        |    24 |   120 |    87  (11)| 00:00:02 |
|   1 |  HASH GROUP BY     |        |    24 |   120 |    87  (11)| 00:00:02 |
|   2 |   TABLE ACCESS FULL| SAMPLE | 49717 |   242K|    80   (3)| 00:00:01 |
—————————————————————————–

Statistics
———————————————————-
          1      recursive calls
          0     db block gets
        337  consistent gets
          0     physical reads
          0     redo size
        955  bytes sent via SQL*Net to client
        392  bytes received via SQL*Net from client
          3      SQL*Net roundtrips to/from client
          0      sorts (memory)
          0      sorts (disk)
         24     rows processed

—————————–Login as a SYS user—————————————————–
SQL> conn / as sysdba
Connected.

SQL> grant query rewrite to hr;

Grant succeeded.

 

—————————–Login Back to HR user—————————————————

SQL> conn hr/hr;
Connected.

SQL> alter session set query_rewrite_enabled=true;

Session altered.
SQL>  alter session set query_rewrite_integrity=enforced;

Session altered.

 

SQL> create materialized view mv_sample
  2  build immediate
  3  refresh on commit
  4  enable query rewrite
  5  as
  6 select owner,count(object_name) from sample group by owner;

Materialized view created.

Elapsed: 00:00:00.65
SQL> analyze table mv_sample compute statistics;

Table analyzed.

Elapsed: 00:00:00.03
SQL> set autotrace traceonly
SQL>  select owner,count(object_name) from sample group by owner;

24 rows selected.

Elapsed: 00:00:00.18

Execution Plan
———————————————————-
Plan hash value: 1060132680

——————————————————————————————
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
——————————————————————————————
|   0 | SELECT STATEMENT             |           |    24 |   216 |     3   (0)| 00:00:01 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| MV_SAMPLE |    24 |   216 |     3   (0)| 00:00:01 |
——————————————————————————————
Statistics
———————————————————-
          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        955  bytes sent via SQL*Net to client
        392  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         24  rows processed

Advertisements

3 thoughts on “Materialized Views

  1. Fantastic goods from you, man. I’ve understand your stuff previous to and you’re just too
    magnificent. I actually like what you have acquired here, really like
    what you’re saying and the way in which you say it. You make it enjoyable and you still care for to keep it wise. I cant wait to read far more from you. This is really a great web site.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s