Materialized Views in Oracle (8.1.5/8i/9i/10g/11g)

29 Apr

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.

A materialized view can query tables, views, and other materialized views. Collectively these are called master tables (a replication term) or detail tables (a data warehouse term).

For replication purposes, materialized views allow you to maintain copies of remote data on your local node. These copies are read-only. If you want to update the local copies, you have to use the Advanced Replication feature. You can select data from a materialized view as you would from a table or view.

For data warehousing purposes, the materialized views commonly created are aggregate views, single-table aggregate views, and join views.

Oracle uses materialized views (also known as snapshots in prior releases) to replicate data to non-master sites in a replication environment and to cache expensive queries in a data warehouse environment.

Working of Materialized Views:

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.

The needed privileges are as follows:

       • CREATE SESSION

       • CREATE TABLE

       • CREATE MATERIALIZED VIEW

       • QUERY REWRITE

Finally, you must be using the Cost Based Optimizer CBO in order to make use of query rewrite. If you do not use the CBO, query rewrite will not take place.

Example:

Here we will create a simple materialized view and prove that it will be faster then a normal query.

I am performing this on a table named cdr_data containing a huge data about 82 Million records for a month’s CDR, I will write a summary query and a materialized view and check the response time in both the cases. I have Oracle Ware House Builder 10g installed on my PC, You can test it on your machine if you have a huge data table or you can create a table and insert huge redundant data by inserting into itself.

SQL> Desc cdr_data ;

  MonthYear      VARCHAR2(7)
    ORIGINATOR      VARCHAR2(30)
    Terminator      VARCHAR2(30)
    callDuration      number(15,3)
    TRUNK_IN      VARCHAR2(15)
    TRUNK_OUT      VARCHAR2(15)
    ZONE_ID      number(17)
    ZONE_CALLS      number(10)
    ZONE_MIN      number(170)
    TRAFFICTYPE      VARCHAR2(1)
    PLATFORM      VARCHAR2(7)

SQL> Select count(*) from cdr_data ;

COUNT(*)
   ——–

82116144

SQL> set echo on

SQL> set termout off

SQL>analyze table cdr_data compute statistics;

Table analyzed.

Initially this quewry will require a full scan of the large table.

SQL> set autotrace on ;

SQL> set timing on

SQL> select originator, count(*) from cdr_data group by originator;

    Originator      COUNT(*)
    —————————— ———-
    PTCL            52703459
    Worldcall       27296541
    Callmate         2046952
    4B Gental             2168
    Burraq                64008
    Call 2 Phone         3016
Elapsed: 00:01:05.04
Execution Plan
Plan hash value: 2770205009
———————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time —-
——————————————————————-|
0 | SELECT STATEMENT | | 6 | 32 | 15791 (6)| 00:03:10 |
1 | HASH GROUP BY | | 6 | 32 | 15791 (6)| 00:03:10 |
2 | TABLE ACCESS FULL| cdr_data| 2116K| 16M| 15446 (2)| 00:03:06
———————————————————————–
Statistics
———————————————————-
1             recursive calls
0             db block gets
969909    consistent gets
452485    physical reads
0             redo size
6565        bytes sent via SQL*Net to client
4381        bytes received via SQL*Net from client
2             SQL*Net roundtrips to/from client
0             sorts (memory)
0             sorts (disk)
6             rows processed

I have a user named cdr_user, in order to create a Materialized view we have do the following:

SQL> grant query rewrite to cdr_user;

Grant succeeded

Elapsed: 00:00:00.09

SQL> alter session set query_rewrite_enabled=true;

Session altered.

Elapsed: 00:00:00.00

SQL> alter session set query_rewrite_integrity=enforced;

Session altered.

Now we create the Materialized View

SQL> create materialized view mv_cdr_data
build immediate
refresh on commit
enable query rewrite
as
select originator, count(*)
from cdr_data
group by originator ;

Materialized view created.

Elapsed: 00:01:12.78

SQL> analyze table mv_cdr_data compute statistics;

Table analyzed.

Elapsed: 00:00:00.17

Basically, what we’ve done is pre-calculate the object count, and define this summary information as a materialized view. We have asked that the view be immediately built and populated with data. You’ll notice that we have also specified REFRESH ON COMMIT and ENABLE QUERY REWRITE. Also notice that we may have created a materialized view, but when we ANALYZE, we are analyzing a table. A materialized view creates a real table, and this table may be indexed, analyzed, and so on.

Now let’s see the materialized view in action by issuing the same query again.

SQL> set timing on

SQL> set autotrace trace only

SQL> select originator, count(*)
from cdr_data
group by originator;

Elapsed: 00:00:00.22

Execution Plan

———————————————————-

Plan hash value: 2009291467

———————————————————————–

Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time

———————————————————————–

0 | SELECT STATEMENT | | 4 | 52 | 3 (0)| 00:00:01 |

1 |MAT_VIEW REWRITE ACCESS FULL| MV_CDR_DATA |4|52|3(0)| 00:00:01 |

———————————————————————–

Statistics

———————————————————-
    1      recursive calls
    0      db block gets
    4      consistent gets
    0      physical reads
    0      redo size
    565 bytes sent via SQL*Net to client
    381 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    6 rows processed

SQL> set autotrace off

SQL> set timing off

Here we can see that the time elapsed in first query without materialized view is far greater then the time consumed for the same query by using materialized view.

Time elapsed without Materialized view: 00:01:05.04

Time elapsed with Materialized view: 00:00:00.22

Uses of Materialized Views

This is relatively straightforward and is answered in a single word – performance. By calculating the answers to the really hard questions up front (and once only), we will greatly reduce the load on the machine, We will experience:

Less physical reads – There is less data to scan through.
Less writes – We will not be sorting/aggregating as frequently.
Decreased CPU consumption – We will not be calculating aggregates and functions on the data, as we will have already done that.
Markedly faster response times – Our queries will return incredibly quickly when a summary is used, as opposed to the details. This will be a function of the amount of work we can avoid by using the materialized view, but many orders of magnitude is not out of the question.

4 Responses to “Materialized Views in Oracle (8.1.5/8i/9i/10g/11g)”

  1. rahul October 29, 2009 at 1:58 am #

    Hi,

    Nice explanation. How to check which materialized view available in oracle database.Is there any depdencies between materialized view and AQ in Oracle?

  2. Terresa Salus January 16, 2010 at 4:40 pm #

    google

Trackbacks/Pingbacks

  1. Oracle10g Privileges to Create Materialized Views « Mike R’s Blog - June 30, 2008

    […] Additional information on materialized views from Oracle8i to Oracle11g can be found on this post. […]

  2. Icinga Database View Model - - Icinga - December 11, 2009

    […] data in the defined target table. I know this is not 100% true for every database, because we have various view types in oracle, but for now it is the only important thing. With this first early version we tried to solve these […]

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

%d bloggers like this: