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.
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.
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 ;
SQL> Select count(*) from cdr_data ;
SQL> set echo on
SQL> set termout off
SQL>analyze table cdr_data compute statistics;
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;
4B Gental 2168
Call 2 Phone 3016
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
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;
SQL> alter session set query_rewrite_enabled=true;
SQL> alter session set query_rewrite_integrity=enforced;
Now we create the Materialized View
SQL> create materialized view mv_cdr_data
refresh on commit
enable query rewrite
select originator, count(*)
group by originator ;
Materialized view created.
SQL> analyze table mv_cdr_data compute statistics;
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(*)
group by originator;
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 |
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.