Gist of Materialized views:

People might be aware of Snapshots in ORACLE, now in recent versions of ORACLE the place of snapshots has been occupied by MATERIALIZED VIEWS. These materialized views have many uses:

1. We can copy the data from one instance to other with the same format, and we can query against the materialized view directly.
2. We can copy the aggregated (Summarized) data from one instance to other instance and we can fire queries against the materialized view directly.
3. We can create a summarized (aggregated) version of a table, the interesting point here is that if we query the base table, then the query is automatically be rewritten to use the materialized view.

Let’s have examples for each of the above uses of the materialized views.

1. Here all the code is in our local database LOCALDB. We have a remote database RMDB, where there is a table named employee. However, one time per day our materialized view wakes up, dips into the RMDB and grabs the data and brings the same to LOCALDB.

Because of this we can fire the required queries to the materialized view even though we don’t have access to the RMDB.

In our LOCALDB:

Create materialized view emp_mview_ex1
Refresh complete
Start with sysdate
Next sysdate+1 as
Select emp_name, dept_name from employee@RMDB_dblink;

Now we can query the Materialized view as such

Select * from emp_mview_ex1;

2. Now let’s discuss about the summary (aggregated) table example. Even her all the code is in the local database LOCALDB. We have a employee table in the RMDB, now this data is summarized daily – getting the number of employees per department, as well as the average salary in that particular department.

Even, here we can fire queries against our local database LOCALDB with no access to the RMDB, aside from the refreshing the materialized view daily.

In our LOCALDB:

Create materialized view emp_mview_ex2
Refresh complete
Start with sysdate
Next sysdate+1 as
Select dept_name,count(*), avg(salary) from employee@RMDB_dblink; group by dept_name;

Now we can query the Materialized view,

Select * from emp_mview_ex2;

3. Now the interesting use of the materialized view query rewrite. A materialized view is built with the help of a base table and data is summarized, The ORACLE optimizer is smart enough in such a way that it can realize that when a query is fired on a base table, the data is already been aggregated in a materialized view, and will go with the data in the materialized view.

In our LOCALDB:

Create materialized view emp_mview_ex3
Refresh complete
Start with sysdate
Next sysdate+1 as
Select dept_name, count(*) from employee group by dept_name;

Now we query the LOCALDB with the base table n the from clause

Select dept_name, count(*) from employee group by dept_name;

Even though the query is fired against the base table, at the time of execution the optimizer will switch to the existing materialized view with the information already aggregated (summarized) and it will use the emp_mview_ex3 instead.

To make optimizer to think of using the existing materialized views there are some init.ora parameters to be set they are:

1. query_rewrite_enabled needs to be set to a value TRUE and we should have the ‘query rewrite’ system privilege.
2. If the materialized view has to be refreshed based on a schedule the it is going to use dbms_job to do so, so job_queue_processes needs to be some value > 0.


Subscribe to Oracle Bullets by Email