What is difference between Materialized View and Materialized Log?
Materialized views are schema objects that can be used to summarize, precompute, replicate, and distribute data. E.g. to construct a data warehouse or in multi-master replication environment.
A materialized view is a database object that contains the results of a query. The FROM clause of the query can name tables, views, and other materialized views. Collectively these objects are called master tables (a replication term) or detail tables (a data warehousing term).The databases containing the master tables are called the master databases.
Materialized Views are mainly used for two reasons:
1) Replication of data to separate remote databases.
2) For improving the performance of queries by computing and storing the results of complex
aggregations of data.
In situations where complex sql queries are performed mainly in reporting or datawarehouse environments
Materialized Views are really helpful in improving performance.Because whenever a SQL query is executed
oracle database has to lot of work in order to retrieve the data, For example it may have to do sorting
(Memory or Disk Based), it has to decide the execution plan for the sql statement (Do a full tables scan or
a indexed based scan) and lots of other stuff before retrieving the requested data.
These type of queries if performed repeatedly will affect the performance of the server in a negative
way.
But with Materialized Views the performance can be improved significantly, because when a materialized
view is created it stores all the data along with the execution plans.So even if the query is executed
repeatedly it will not eat up all the resources as it did earlier.
The Materialized view can be created on the base of tables, views or other materialized views.
When a Materialized View is created, oracle also create a table with the same name as that of the
materialized view and also creates a materialized view object.
we will only cover two types of materialized views:
1) Complete – Refreshable Materialized Views
2) Fast-refresh Materialized Views
COMPLETE REFRESH MATERIALIZED VIEWS
In this type of materialized view there is a complete refresh of data at periodic intervals.
SQL> alter user Scott identified by tiger account unlock;
User altered.
SQL> grant create materialized view to Scott;
Grant succeeded.
SQL> conn scott;
Enter password:
Connected.
SQL> create table sales(
2 sales_id int,
3 sales_amt int,
4 region_id int,
5 sales_dtt timestamp,
6 constraint sales_pk primary key (sales_id));
Table created.
SQL> insert into sales values(1,101,100,sysdate-50);
SQL> insert into sales values(2,511,200,sysdate-20)
SQL> insert into sales values(3,11,100,sysdate)
SQL> commit;
Now lets create a materialized view.
SQL> create materialized view sales_mv
2 refresh
3 complete
4 next sysdate+1/1440
5 as
6 select sales_amt, sales_dtt from sales;
Materialized view created.
So above we have created a materialized view based on the sales table, which will completely refresh
itself after every one minute.
SQL> select mview_name, refresh_method, refresh_mode, build_mode,
fast_refreshable from user_mviews
where mview_name = ‘SALES_MV’;
MVIEW_NAME REFRESH_ REFRESH_MODE BUILD_MOD FAST_REFRESHABLE
SALES_MV COMPLETE DEMAND IMMEDIATE NO
Materialized views can also be refreshed by (ON DEMAND or ON COMMIT). Since i did not mention
either of these clauses the default refresh is on demand as seen above in REFRESH_MODE column.
If you query the user_objects you can see that several objects have been created.
SQL> col object_name format a20
SQL> select object_name, object_type from user_objects
where object_name like ‘SALES%’ order by object_name;
OBJECT_NAME OBJECT_TYPE
SALES TABLE
SALES_MV MATERIALIZED VIEW
SALES_MV TABLE
SALES_PK INDEX
The materialized view is basically a logical container that stores data in a regular table.
If you query the USER_SEGMENTS view you will find the base table its primary-key and the table
that stores the data returned by the Materialized View.
SQL> select segment_name,segment_type from user_segments
2 where segment_name like ‘SALES%’
3 order by segment_name;
SEGMENT_NAME SEGMENT_TYPE
———— —————
SALES TABLE
SALES_MV TABLE
SALES_PK INDEX
Now lets check the already existing data and some more.
SQL> select sales_amt, to_char(sales_dtt,’dd-mon-yyyy’) from sales_mv;
SALES_AMT TO_CHAR(SAL
101 22-nov-2011
511 22-dec-2011
11 11-jan-2012SQL> insert into sales values(4,99,200,sysdate);
1 row created.
SQL>insert into sales values(5,127,300,sysdate);
1 row created.
SQL> commit;
Commit complete.
After one minute the materialized view will get updated.
SQL> select sales_amt, to_char(sales_dtt,’dd-mon-yyyy’) from sales_mv;
SQL> SALES_AMT TO_CHAR(SAL
———- ———–
101 22-nov-2011
511 22-dec-2011
11 11-jan-2012
99 11-jan-2012
127 11-jan-2012
if you have set a longer refresh interval and you do not want to wait that long you can order the
refresh manually through the following command.
SQL> exec dbms_mview.refresh(‘SALES_MV’,’C’);
PL/SQL procedure successfully completed.
Where “C” stands for complete refresh.
So the whole process can ve summarized as following.
1) User or application creates transactions.
2) Base table is updated because of the transactions.
3) A complete refresh occurs or is done manually and the data in the materialized view is deleted
and completely refreshed with the contents of the master table(SALES).
4) The User or application can query the materialized view which contains a point in time snapshot
of the base table’s data.
FAST REFRESH MATERIALIZED VIEW
Fast refreshable materialized views work a little bit differently. When a fast refresh materialized
view is created it initially populates the materialized view table with data from the base or master table.
After the initial data is populated only modified data is applied to the materialized view table after
each refresh, Instead of a complete refresh like that in Complete refresh materialized views.
Three basic steps are required to create a fast refresh materialized view.
1) Create a base or master table if it does not exist.
2) Create a Materialized view log on the base table.
3) Create a fast refresh materialized view.
Since i have already created a materialized view and base table i am going to drop them and make
a fresh start.
SQL> drop materialized view sales_mv;
Materialized view dropped.
SQL> drop table sales purge;
Table dropped.
SQL> create table sales(
2 sales_id int,
3 sales_amt int,
4 region_id int,
5 sales_dtt timestamp
6 );
SQL> alter table sales add constraint sales_pk primary key(sales_id);
Table altered.
SQL> desc sales;
Name Null? Type
SALES_ID NOT NULL NUMBER(38)
SALES_AMT NUMBER(38)
REGION_ID NUMBER(38)
SALES_DTT TIMESTAMP(6)
SQL> insert into sales values(1,101,100,sysdate-50);
1 row created.
SQL> insert into sales values(2,511,200,sysdate-20);
1 row created.
SQL> insert into sales values(3,11,100,sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL>
When creating a fast refreshable materialized view a materialized view log is required.
The reason behind this is that the log keeps track of all the changes made to the master table, so
when a materialized view is refreshed only updated data is applied.
It is something similar to the “block change tracking file” feature in RMAN.
Further a materialized view can be created on the basis of primary key or by ROWID.
If the master table has a primary key then primary key clause can be used otherwise use ROWID.
Now lets create a materialized view log on the master table.
SQL> create materialized view log on sales with primary key;
Materialized view log created.
If your base table does not have a primary key then a following error will occur.
ORA-12014: table does not contain primary key constraint
In that case make materialized view log based on ROWID.
SQL> create materialized view log sales with rowid;
Also, when creating a materialized view you have to mention whether
the data is refreshed via PRIMARY KEY or ROWID.
We are creating a materialized view based on primary key refresh.
NOTE: The primary key columns must be part of the MV select query from the base table.
SQL> create materialized view sales_mv
refresh
with primary key
fast
next sysdate+3/1440
as
select sales_id, sales_amt, sales_dtt from sales
Materialized view created.
Now lets query the USER_OBJECTS view.
SQL> select object_name, object_type from user_objects
2 where object_name like ‘%SALES%’
3 order by object_name;
OBJECT_NAME OBJECT_TYPE
MLOG$_SALES TABLE
RUPD$_SALES TABLE
SALES TABLE
SALES_MV MATERIALIZED VIEW
SALES_MV TABLE
SALES_PK INDEX
SALES_PK1 INDEX
7 rows selected.
Explanation.
MLOG$_SALES = This is a table created along with the materialized view.
It contains data that has changed in the base table.
RUPD$_SALES = This table is created when a materialized view
uses primary key for fast refresh. This is used
to support updatable materialized views. But right
now we are creating Read only MVs so ignore this table.
SALES_PK1 = This index is automatically created and is based on the
primary key columns of the base table.
SQL> select sales_amt, to_char(sales_dtt,’dd-mon-yyyy’) from sales_mv;
SALES_AMT TO_CHAR(SAL
101 23-nov-2011
511 23-dec-2011
11 12-jan-2012Lets add some records.
SQL> insert into sales values(4,99,200,sysdate);
1 row created.
SQL> insert into sales values(5,127,300,sysdate);
1 row created.
SQL> commit;
Commit complete.
Now before refresh the mlog$_sales table will contain information about the two changes that
have been made to the base table.
SQL> select count(*) from mlog$_sales;
COUNT(*)
2Wait for three minutes or refresh the view manually.
SQL> exec dbms_mview.refresh(‘SALES_MV’,’F’);
Lets check the records.
SQL> select sales_amt, to_char(sales_dtt,’dd-mon-yyyy’) from sales_mv;
SALES_AMT TO_CHAR(SAL
101 23-nov-2011
511 23-dec-2011
11 12-jan-2012
99 12-jan-2012
127 12-jan-2012After the refresh is complete and the data is refreshed
the MLOG$_SALES table will contain no records.
SQL> select count(*) from mlog$_sales;
COUNT(*)
0You can also check time of the last refresh when it happened.
SQL> select mview_name, last_refresh_type, last_refresh_date
from user_mviews;
MVIEW_NAME LAST_REF LAST_REFR
SALES_MV FAST 12-JAN-12
The whole process above is summarized as following:
1) User or application creates transactions.
2) Data is commited in the base table.
3) Then the MVlog table is populated with the changes.
4) A fast refresh occurs automatically or manually.
5) All the changes that have been made since last refresh
are applied to the materialized view and rows that are no
longer required are deleted from MVlog table.
6) The users can query the materialized view which contains
point in time snapshot of master tables data.
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 reexecute 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.
Growing Materialized View (Snapshot) Logs
if you have a Fast Refresh Materialized View that hasn’t been refreshed for a considerable period of time, you will find that the Materialized View Log (“Snapshot Log” in earlier versions) would keep growing because it patiently expects the MV to come around for the next refresh. You can end up with very large MV Logs and also performance issues inserting into the base tables as each insert also has to update the MV Logs. Furthermore, other MVs that do refresh from the same base table may also suffer because they have to contend(struggle) with larger MV Logs.
Here is a demonstration with two users “ANOTHER_USR” and “LAZY_USR” having created their own Materialized Views against my source table. If LAZY_USR does not execute a Refresh of his MV, the Snapshot Log on my source table does not get purge.
23:39:21 SQL> — create two users that will be creating MVs
23:39:21 SQL> create user another_usr identified by another_usr default
tablespace users;
User created.
23:39:21 SQL> grant create session, create table, create materialized view
to another_usr;
Grant succeeded.
23:39:21 SQL> alter user another_usr quota unlimited on users;
User altered.
23:39:22 SQL> create user lazy_usr identified by lazy_usr default tablespace
users;
User created.
23:39:22 SQL> grant create session, create table, create materialized view to
lazy_usr;
Grant succeeded.
23:39:22 SQL> alter user lazy_usr quota unlimited on users;
User altered.
23:39:22 SQL> — create the source table and mv log on it
23:39:22 SQL> create table my_data_table
23:39:22 2 as select object_id as ID_Number, object_name as Data_Item,
created as Crtn_date
23:39:22 3 from dba_objects where object_id is not null;
Table created.
23:39:22 SQL> alter table my_data_table modify (ID_Number not null);
Table altered.
23:39:22 SQL> alter table my_data_table add constraint my_data_table_pk
primary key (ID_Number);
Table altered.
23:39:22 SQL> create materialized view log on my_data_table ;
Materialized view log created.
23:39:22 SQL> grant select on my_data_table to another_usr;
Grant succeeded.
23:39:22 SQL> grant select on mlog$_my_data_table to another_usr;
Grant succeeded.
23:39:22 SQL> grant select on my_data_table to lazy_usr;
Grant succeeded.
23:39:22 SQL> grant select on mlog$_my_data_table to lazy_usr;
Grant succeeded.
23:39:22 SQL> — create the mv in the another_usr account
23:39:22 SQL> connect another_usr/another_usr
Connected.
23:39:22 SQL> create materialized view my_mv
23:39:22 2 refresh fast on demand
23:39:22 3 as
23:39:22 4 select rowid as row_identifier, id_number, data_item
23:39:22 5 from hemant.my_data_table
23:39:22 6 /
Materialized view created.
23:39:22 SQL>
23:39:22 SQL> — query the data dictionary
23:39:22 SQL> connect hemant/hemant
Connected.
23:39:22 SQL> col owner format a12
23:39:22 SQL> col name format a12
23:39:22 SQL> col snapshot_site format a12
23:39:22 SQL> col refresh_method format a12
23:39:22 SQL> select name from v$database;
NAME
ORCL
23:39:22 SQL> select v.owner, v.name, v.snapshot_site, v.refresh_method,
23:39:22 2 to_char(l.current_snapshots,’DD-MON HH24:MI:SS’)
23:39:22 3 from dba_registered_snapshots v, dba_snapshot_logs l
23:39:22 4 where v.snapshot_id = l.snapshot_id
23:39:22 5 and l.log_owner = ‘HEMANT’
23:39:22 6 and l.master = ‘MY_DATA_TABLE’
23:39:22 7 order by 1,2
23:39:22 8 /
OWNER NAME SNAPSHOT_SIT REFRESH_METH TO_CHAR(L.CURRENT_SNAPSH
ANOTHER_USR MY_MV ORCL PRIMARY KEY 15-JAN 23:39:23
23:39:22 SQL> — insert into the source table
23:39:22 SQL> insert into my_data_table values (1000000,’A DUMMY’,sysdate);
1 row created.
23:39:22 SQL> commit;
Commit complete.
23:39:22 SQL> select count(*) from mlog$_my_data_table;
COUNT(*)
1 23:39:22 SQL> exec dbms_lock.sleep(30);
PL/SQL procedure successfully completed.
23:39:52 SQL> — check if the MV has been updated ?!
23:39:52 SQL> select v.owner, v.name, v.snapshot_site, v.refresh_method,
23:39:52 2 to_char(l.current_snapshots,’DD-MON HH24:MI:SS’)
23:39:52 3 from dba_registered_snapshots v, dba_snapshot_logs l
23:39:52 4 where v.snapshot_id = l.snapshot_id
23:39:52 5 and l.log_owner = ‘HEMANT’
23:39:52 6 and l.master = ‘MY_DATA_TABLE’
23:39:52 7 order by 1,2
23:39:52 8 /
OWNER NAME SNAPSHOT_SIT REFRESH_METH TO_CHAR(L.CURRENT_SNAPSH
ANOTHER_USR MY_MV ORCL PRIMARY KEY 15-JAN 23:39:23
23:39:52 SQL> exec dbms_lock.sleep(10);
PL/SQL procedure successfully completed.
23:40:02 SQL>
23:40:02 SQL> — refresh the MV
23:40:02 SQL> connect another_usr/another_usr
Connected.
23:40:03 SQL> exec dbms_mview.refresh(‘MY_MV’);
PL/SQL procedure successfully completed.
23:40:03 SQL> — re-query the data dictionary
23:40:03 SQL> connect hemant/hemant
Connected.
23:40:03 SQL> select v.owner, v.name, v.snapshot_site, v.refresh_method,
23:40:03 2 to_char(l.current_snapshots,’DD-MON HH24:MI:SS’)
23:40:03 3 from dba_registered_snapshots v, dba_snapshot_logs l
23:40:03 4 where v.snapshot_id = l.snapshot_id
23:40:03 5 and l.log_owner = ‘HEMANT’
23:40:03 6 and l.master = ‘MY_DATA_TABLE’
23:40:03 7 order by 1,2
23:40:03 8 /
OWNER NAME SNAPSHOT_SIT REFRESH_METH TO_CHAR(L.CURRENT_SNAPSH
ANOTHER_USR MY_MV ORCL PRIMARY KEY 15-JAN 23:40:03
23:40:03 SQL> select count(*) from mlog$_my_data_table;
COUNT(*)
0 23:40:03 SQL> — create another mv in the lazy_usr account
23:40:03 SQL> connect lazy_usr/lazy_usr
Connected.
23:40:03 SQL> create materialized view lazy_mv
23:40:03 2 refresh fast on demand
23:40:03 3 as
23:40:03 4 select rowid as row_identifier, id_number, data_item
23:40:03 5 from hemant.my_data_table
23:40:03 6 /
Materialized view created.
23:40:03 SQL> — insert new data
23:40:03 SQL> connect hemant/hemant
Connected.
23:40:03 SQL> insert into my_data_table values (2000000,’Another dummy’,sysdate);
1 row created.
23:40:03 SQL> commit;
Commit complete.
23:40:03 SQL> select count(*) from mlog$_my_data_table;
COUNT(*)
1 23:40:03 SQL> exec dbms_lock.sleep(30);
PL/SQL procedure successfully completed.
23:40:33 SQL> — refresh another_usr’s mv only
23:40:33 SQL> connect another_usr/another_usr
Connected.
23:40:33 SQL> exec dbms_mview.refresh(‘MY_MV’);
PL/SQL procedure successfully completed.
23:40:34 SQL> — requery the data dictionary
23:40:34 SQL> connect hemant/hemant
Connected.
23:40:34 SQL> select v.owner, v.name, v.snapshot_site, v.refresh_method,
23:40:34 2 to_char(l.current_snapshots,’DD-MON HH24:MI:SS’)
23:40:34 3 from dba_registered_snapshots v, dba_snapshot_logs l
23:40:34 4 where v.snapshot_id = l.snapshot_id
23:40:34 5 and l.log_owner = ‘HEMANT’
23:40:34 6 and l.master = ‘MY_DATA_TABLE’
23:40:34 7 order by 1,2
23:40:34 8 /
OWNER NAME SNAPSHOT_SIT REFRESH_METH TO_CHAR(L.CURRENT_SNAPSH
ANOTHER_USR MY_MV ORCL PRIMARY KEY 15-JAN 23:40:34
LAZY_USR LAZY_MV ORCL PRIMARY KEY 15-JAN 23:40:04
23:40:34 SQL> select count(*) from mlog$_my_data_table;
COUNT(*)
1 My data dictionary query, at the first execution at 23:39:22, showed
that ANOTHER_USR had an MV called “MY_MV” against my table
MY_DATA_TABLE.
Inserting a single row in MY_DATA_TABLE caused a corresponding row to
be inserted into the MV Log MLOG$_MY_DATA_TABLE. However, as
ANOTHER_USR had not refreshed his MV even as at 23:39:52, the Data
Dictionary showed that the MV was still as of 23:39:23.
After ANOTHER_USR did refresh his MV (at 23:40:03), the MV Log MLOG$_MY_DATA_TABLE was purged of the record that was used to track the one row DML against MY_DATA_TABLE.
However, when LAZY_USR created an MV but did not refresh the MV, even as at 23:40:34, the one row in MLOG$_MY_DATA_TABLE was not purged. Until LAZY_USR executes a Refresh of his MV, MLOG$_MY_DATA_TABLE will keep growing, even though ANOTHER_USR might be diligently refreshing his MV.
Over time, I will find MLOG$_MY_DATA_TABLE to have grown “excessively large !”.
The solution is to identify LAZY_USR (which the query against the Data Dictionary does do) and then :
0 (optional). Drop LAZY_USR’s MV “LAZY_MV”
- Purge MLOG$_MY_DATA_TABLE (using either DBMS_MVIEW.PURGE_LOG or a TRUNCATE)
- ReCreate or Refresh LAZY_USR’s MV “LAZY_MV”
I have seen situations where LAZY_USR is a an account in a “remote” database managed by another application / development / support / DBA team that doesn’t bother to inform HEMANT that they are no longer refreshing the MV even as HEMANT’s schema continues to grow only to hold entries in MLOG$_MY_DATA_TABLE.
The example I have posted is of 1 source table and 1 row DML. In real-life you may have a dozen such tables and tens of thousands of rows before you notice this (and you would notice it only if you are diligently monitoring all the schemas in your database).
1031924.6 on MetaLink.
Note that even if you do drop the non-refreshed MV as advised in the Note, it would still be preferable, if possible, to drop the Snapshot Logs on the base tables — because you would want to rebuild the Snapshot Log afresh as a small log — and rebuild other MVs that are still in use. (I say “if possible” because a complete rebuild of some MVs might also take too long to be acceptable “on-line”).
