Every time you use a normal view oracle has to execute the sql statement defined for that view (called view resolution), it must be done each time the view is used. If the view is complex this can take sometime, this is where a materialized views comes in (also known as snapshots in prior releases), unlike a view it contains space and storage just like a regular table.You can either use materialized view against a local table or a remote table .Using materialized views against remote tables is the simplest way to achieve replication of data between sites.
When we see the performance of Materialized view it is better than normal View because the data of materialized view will stored in table and table may be indexed so faster for joining also joining is done at the time of materialized views refresh time so no need to every time fire join statement as in case of view.
You can even partition them and create indexes on them. Materialized views take a snapshot of the underlying tables which means that data may not represent the source data. To get the materialized view data up to date you must refresh it.
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.
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.
Basic syntax:-
CREATE MATERIALIZED VIEW view-name
BUILD [IMMEDIATE | DEFERRED]
REFRESH [FAST | COMPLETE | FORCE ]
ON [COMMIT | DEMAND ]
[[ENABLE | DISABLE] QUERY REWRITE]
[ON PREBUILT TABLE]
AS
SELECT ...;
This is a very basic syntax , even you can specify storage level parameter while configuring materialized view .
The BUILD clause options are shown below,
IMMEDIATE(Default) : The materialized view is populated immediately.
DEFERRED : The materialized view is populated on the first requested refresh.
The REFRESH clause options are shown below,
FAST : A fast refresh is attempted. If materialized view logs are not present against the source tables in advance, the creation fails.
COMPLETE : The table segment supporting the materialized view is truncated and repopulated completely using the associated query.- and its time consuming .
Note: If a materialized view is complete refreshed, then set it's PCTFREE to 0 and PCTUSED to 99 for maximum efficiency.
FORCE (Default): A fast refresh is attempted. If one is not possible a complete refresh is performed.If you do not specify a refresh method (FAST, COMPLETE, or FORCE), then FORCE is the default.
Note:- A materialized view get locked while its being refreshed .
A refresh can be triggered in one of two ways,
ON COMMIT : Specify ON COMMIT to indicate that a fast refresh is to occur whenever the database commits a transaction that operates on a master table of the materialized view. This clause may increase the time taken to complete the commit, because the database performs the refresh operation as part of the commit process.
Note:- If you want to refresh materialized view automatically they you must set job_queue_processes=n
ON DEMAND (Default) : The refresh is initiated by a manual request or a scheduled task.
if your refresh interval is very large and you need to refresh in between use the follwoing procedure.
SQL> EXEC DBMS_MVIEW.REFRESH('mv_name', 'C');
C -> Complete refresh
F ->FAST refresh
? -> Force refresh
When you create a materialized view a table segment(with same name) will get automatically created to hold the data represented by the materialized view .
If you have a large table , then creating materialized through normal method take time, especially when base table on a remote box. In this case you can make use of PREBUILT TABLE . Here you can export the table from remote box and import the table on the target box .Depends on the requirement you can either import the full table or import a part of the table by using impdp parameter QUERY.
That is ON PREBUILT TABLE clause tells the database to use an existing table segment, which must have the same name as the materialized view and support the same column structure as the query.
One simple example is given below ,
SQL> conn hr/hr
Connected.
SQL> create table my_objects as select object_name,object_type,created from user_objects;
Table created.
SQL> create materialized view mv_objects on prebuilt table as select object_name,object_type,created from user_objects;
create materialized view my_objectsd on prebuilt table as select object_name,object_type,created from user_objects
*
ERROR at line 1:
ORA-12059: prebuilt table "HR"."MV_OBJECTS" does not exist
That is for materialized view we need to use the same name that we used for prebuilt table.
SQL> create materialized view my_objects on prebuilt table as select object_name,object_type,created from user_objects;
Materialized view created.
SQL> create table t as select * from employees;
Table created.
SQL> col object_name for a15
SQL> set lines 222
SQL> select object_name,object_type,created from user_objects where object_name='T';
OBJECT_NAME OBJECT_TYPE CREATED
--------------- ----------------------- ---------
T TABLE 07-FEB-16
Check the contents of the materialized view
SQL> select object_name,object_type,created from my_objects where object_name='T';
no rows selected
As Materialized view is not yet refreshed , I did a manual refresh .
SQL> EXEC DBMS_MVIEW.REFRESH('MY_OBJECTS','C');
PL/SQL procedure successfully completed.
SQL> select object_name,object_type,created from my_objects where object_name='T';
OBJECT_NAME OBJECT_TYPE CREATED
--------------- ----------------------- ---------
T TABLE 07-FEB-16
SQL>
Note:- You can't do any DML on the underlying table that hold the data of the materialized view . if you tried to do so , you will get error like following .
SQL> insert into MY_OBJECTS values ('SAMPLE','TABLE','07-FEB-16');
insert into MY_OBJECTS values ('SAMPLE','TABLE','07-FEB-16')
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view
SQL>
ENABLE QUERY REWRITE
A materialized view can be stored in the same database as it's base table(s) or in a different database. Materialized views stored in the same database as their base tables can improve query performance through query rewrites. Query rewrites are particularly useful in a data warehouse environment.
The QUERY REWRITE clause tells the optimizer if the materialized view should be consider for query rewrite operations.
http://www.sqlsnippets.com/en/topic-12918.html
CREATE MATERIALIZED VIEW LOG
A complete refreshes of materialized views can be expensive operations. Fortunately there is a way to refresh only the changed rows in a materialized view's base table. This is called fast refreshing. Before a materialized view can perform a fast refresh however it needs a mechanism to capture any changes made to its base table. This mechanism is called a Materialized View Log.
Common usage syntax:-
create materialized view log on table with primarykey|rowid ;
where ,
PRIMARY KEY
Specify PRIMARY KEY to indicate that the primary key of all rows changed should be recorded in the materialized view log(this is default)
Note:- Specify WITH PRIMARY KEY to create a primary key materialized view. This is the default and should be used in all cases except those described for WITH ROWID.
ROWID
Specify ROWID to indicate that the rowid of all rows changed should be recorded in the materialized view log.
Note:- The materialized view log supports fast refresh for primary key materialized views only. If you omit this clause, or if you specify the clause without PRIMARY KEY, ROWID, or OBJECT ID, then the database stores primary key values by default. However, the database does not store primary key values implicitly if you specify only OBJECT ID or ROWID at create time.
If your table have a PRIMARY KEY, you don't need to creata materialized view log WITH ROWID. If you don't have a PRIMARY KEY, you have to add WITH ROWID.
As more often rowids can change.Partitioned tables with enable row movement allow rowids to change. ALTER TABLE t MOVE will change rowids. In 10g more and more things will change rowids.So oracle recommented to create primary key based materialized view log.
Eg:- create materialized view log on employees with primary key;
Note how the materialized view log is not given a name. This is because a table can only ever have one materialized view log related to it at a time, so a name is not required.
Basic example for creating a materialized view ,
CREATE MATERIALIZED VIEW mv_emp_pk
REFRESH FAST START WITH SYSDATE
NEXT SYSDATE + 1/1440 ---- > every one minute
WITH PRIMARY KEY -- this is default , no need to specify though
AS SELECT employee_id,name,salary FROM emp@remote_db;
ON COMMIT
In some situations it would be convenient to have Oracle refresh a materialized view automatically whenever changes to the base table are committed. This is possible using the ON COMMIT refresh mode. Here is an example.
Assuming that employee table have primary key , so create a materialized view log on employee table.If your table does'nt have a primary key defined you will get error like ORA-12014: table does not contain primary key constraint.
sql>conn hr/hr
Connected
SQL> create materialized view log on employees with primary key;
Materialized view log created.
You can see following table got created while creating materialized view log on employees table ,
MLOG$_EMPLOYEES:- This is a table created along with the materialized view. It contains data that has changed in the base table.
RUPD$_EMPLOYEES:- This table is created when a materialized view uses primary key for fast refresh. This is used to support updatable materialized views.
SQL> conn / as sysdba
Connected.
SQL> grant select any table to mahi;
Grant succeeded.
SQL> grant create any materialized view to mahi;
Grant succeeded.
SQL> create database link hrlink connect to hr identified by hr using 'study';
Database link created.
SQL> conn mahi/mahi
Connected.
SQL>
SQL> create materialized view mv_hr_employee REFRESH FAST ON COMMIT as select * from employees@hrlink;
create materialized view mv_hr_employee
*
ERROR at line 1:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
While googling I learned following -
Things learned:- This materialized view is selecting from a remote table over a database link (a distributed materialized view). For "on commit", you can use only if you have your master table in the same database where you are creating the materialized view. Therefore, on commit is not supported in remote databases.
SQL> conn hr/hr
Connected.
SQL> create materialized view mv_employee REFRESH FAST ON COMMIT as select * from employees;
Materialized view created.
SQL> select count(1) from employees;
COUNT(1)
----------
107
SQL> select count(1) from mv_employee;
COUNT(1)
----------
107
SQL> delete from employees where EMPLOYEE_ID=206;
1 row deleted.
SQL> select count(1) from employees;
COUNT(1)
----------
106
SQL> select count(1) from mv_employee;
COUNT(1)
----------
107
SQL> commit;
Commit complete.
SQL> select count(1) from mv_employee;
COUNT(1)
----------
106
SQL>
As soon as I put commit , my materialized view got reflected
Check the details of the materialized view using following query ,
SQL> select mview_name, refresh_method, refresh_mode, build_mode, fast_refreshable from user_mviews where mview_name = 'MV_EMPLOYEE';
How to know when was the last refresh happened on materialized views:
SQL> select MVIEW_NAME, to_char(LAST_REFRESH_DATE,'YYYY-MM-DD HH24:MI:SS') from dba_mviews;
(or)
SQL> select MVIEW_NAME, to_char(LAST_REFRESH_DATE,'YYYY-MM-DD HH24:MI:SS') from dba_mview_analysis;
(or)
SQL> select NAME, to_char(LAST_REFRESH,'YYYY-MM-DD HH24:MI:SS') from dba_mview_refresh_times;
Difference between View vs Materialized View in database
Based upon on our understanding of View and Materialized View, Let’s see, some short difference between them :
1) First difference between View and materialized view is that, In Views query result is not stored in the disk or database but Materialized view allow to store query result in disk or table.
2) Another difference between View vs materialized view is that, when we create view using any table, rowid of view is same as original table but in case of Materialized view rowid is different.
3) One more difference between View and materialized view in database is that, In case of View we always get latest data but in case of Materialized view we need to refresh the view for getting latest data.
4) Performance of View is less than Materialized view.
5) Last difference between View vs Materialized View is that, In case of Materialized view we need extra trigger or some automatic method so that we can keep MV refreshed, this is not required for views in database.
Points to note:-
*It is always recommended to gather the statistics of the underlying table after materialized view got created .
When we see the performance of Materialized view it is better than normal View because the data of materialized view will stored in table and table may be indexed so faster for joining also joining is done at the time of materialized views refresh time so no need to every time fire join statement as in case of view.
You can even partition them and create indexes on them. Materialized views take a snapshot of the underlying tables which means that data may not represent the source data. To get the materialized view data up to date you must refresh it.
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.
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.
Basic syntax:-
CREATE MATERIALIZED VIEW view-name
BUILD [IMMEDIATE | DEFERRED]
REFRESH [FAST | COMPLETE | FORCE ]
ON [COMMIT | DEMAND ]
[[ENABLE | DISABLE] QUERY REWRITE]
[ON PREBUILT TABLE]
AS
SELECT ...;
This is a very basic syntax , even you can specify storage level parameter while configuring materialized view .
The BUILD clause options are shown below,
IMMEDIATE(Default) : The materialized view is populated immediately.
DEFERRED : The materialized view is populated on the first requested refresh.
The REFRESH clause options are shown below,
FAST : A fast refresh is attempted. If materialized view logs are not present against the source tables in advance, the creation fails.
COMPLETE : The table segment supporting the materialized view is truncated and repopulated completely using the associated query.- and its time consuming .
Note: If a materialized view is complete refreshed, then set it's PCTFREE to 0 and PCTUSED to 99 for maximum efficiency.
FORCE (Default): A fast refresh is attempted. If one is not possible a complete refresh is performed.If you do not specify a refresh method (FAST, COMPLETE, or FORCE), then FORCE is the default.
Note:- A materialized view get locked while its being refreshed .
ON COMMIT : Specify ON COMMIT to indicate that a fast refresh is to occur whenever the database commits a transaction that operates on a master table of the materialized view. This clause may increase the time taken to complete the commit, because the database performs the refresh operation as part of the commit process.
Note:- If you want to refresh materialized view automatically they you must set job_queue_processes=n
ON DEMAND (Default) : The refresh is initiated by a manual request or a scheduled task.
if your refresh interval is very large and you need to refresh in between use the follwoing procedure.
SQL> EXEC DBMS_MVIEW.REFRESH('mv_name', 'C');
C -> Complete refresh
F ->FAST refresh
? -> Force refresh
When you create a materialized view a table segment(with same name) will get automatically created to hold the data represented by the materialized view .
If you have a large table , then creating materialized through normal method take time, especially when base table on a remote box. In this case you can make use of PREBUILT TABLE . Here you can export the table from remote box and import the table on the target box .Depends on the requirement you can either import the full table or import a part of the table by using impdp parameter QUERY.
That is ON PREBUILT TABLE clause tells the database to use an existing table segment, which must have the same name as the materialized view and support the same column structure as the query.
One simple example is given below ,
SQL> conn hr/hr
Connected.
SQL> create table my_objects as select object_name,object_type,created from user_objects;
Table created.
SQL> create materialized view mv_objects on prebuilt table as select object_name,object_type,created from user_objects;
create materialized view my_objectsd on prebuilt table as select object_name,object_type,created from user_objects
*
ERROR at line 1:
ORA-12059: prebuilt table "HR"."MV_OBJECTS" does not exist
That is for materialized view we need to use the same name that we used for prebuilt table.
SQL> create materialized view my_objects on prebuilt table as select object_name,object_type,created from user_objects;
Materialized view created.
SQL> create table t as select * from employees;
Table created.
SQL> col object_name for a15
SQL> set lines 222
SQL> select object_name,object_type,created from user_objects where object_name='T';
OBJECT_NAME OBJECT_TYPE CREATED
--------------- ----------------------- ---------
T TABLE 07-FEB-16
Check the contents of the materialized view
SQL> select object_name,object_type,created from my_objects where object_name='T';
no rows selected
As Materialized view is not yet refreshed , I did a manual refresh .
SQL> EXEC DBMS_MVIEW.REFRESH('MY_OBJECTS','C');
PL/SQL procedure successfully completed.
SQL> select object_name,object_type,created from my_objects where object_name='T';
OBJECT_NAME OBJECT_TYPE CREATED
--------------- ----------------------- ---------
T TABLE 07-FEB-16
SQL>
Note:- You can't do any DML on the underlying table that hold the data of the materialized view . if you tried to do so , you will get error like following .
SQL> insert into MY_OBJECTS values ('SAMPLE','TABLE','07-FEB-16');
insert into MY_OBJECTS values ('SAMPLE','TABLE','07-FEB-16')
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view
SQL>
ENABLE QUERY REWRITE
A materialized view can be stored in the same database as it's base table(s) or in a different database. Materialized views stored in the same database as their base tables can improve query performance through query rewrites. Query rewrites are particularly useful in a data warehouse environment.
The QUERY REWRITE clause tells the optimizer if the materialized view should be consider for query rewrite operations.
The following query does an aggregation of the data in the
EMP table.CONN scott/tiger
SET AUTOTRACE TRACE EXPLAIN
SELECT deptno, SUM(sal)
FROM emp
GROUP BY deptno;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 21 | 4 (25)| 00:00:01 |
| 1 | HASH GROUP BY | | 3 | 21 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 98 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Create a materialized view to perform the aggregation in advance, making sure you specify the
ENABLE QUERY REWRITE clause.CREATE MATERIALIZED VIEW emp_aggr_mv
BUILD IMMEDIATE
REFRESH FORCE
ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT deptno, SUM(sal) AS sal_by_dept
FROM emp
GROUP BY deptno;
EXEC DBMS_STATS.gather_table_stats(USER, 'EMP_AGGR_MV');
The same query is now rewritten to take advantage of the pre-aggregated data in the materialized view, instead of the session doing the work for itself.
--ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;
--ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;
SET AUTOTRACE TRACE EXPLAIN
SELECT deptno, SUM(sal)
FROM emp
GROUP BY deptno;
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 21 | 3 (0)| 00:00:01 |
| 1 | MAT_VIEW REWRITE ACCESS FULL| EMP_AGGR_MV | 3 | 21 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
More example of the query rewrite functionality is given in below link.http://www.sqlsnippets.com/en/topic-12918.html
CREATE MATERIALIZED VIEW LOG
A complete refreshes of materialized views can be expensive operations. Fortunately there is a way to refresh only the changed rows in a materialized view's base table. This is called fast refreshing. Before a materialized view can perform a fast refresh however it needs a mechanism to capture any changes made to its base table. This mechanism is called a Materialized View Log.
Common usage syntax:-
create materialized view log on table with primarykey|rowid ;
where ,
PRIMARY KEY
Specify PRIMARY KEY to indicate that the primary key of all rows changed should be recorded in the materialized view log(this is default)
Note:- Specify WITH PRIMARY KEY to create a primary key materialized view. This is the default and should be used in all cases except those described for WITH ROWID.
ROWID
Specify ROWID to indicate that the rowid of all rows changed should be recorded in the materialized view log.
Note:- The materialized view log supports fast refresh for primary key materialized views only. If you omit this clause, or if you specify the clause without PRIMARY KEY, ROWID, or OBJECT ID, then the database stores primary key values by default. However, the database does not store primary key values implicitly if you specify only OBJECT ID or ROWID at create time.
If your table have a PRIMARY KEY, you don't need to creata materialized view log WITH ROWID. If you don't have a PRIMARY KEY, you have to add WITH ROWID.
As more often rowids can change.Partitioned tables with enable row movement allow rowids to change. ALTER TABLE t MOVE will change rowids. In 10g more and more things will change rowids.So oracle recommented to create primary key based materialized view log.
Eg:- create materialized view log on employees with primary key;
Note how the materialized view log is not given a name. This is because a table can only ever have one materialized view log related to it at a time, so a name is not required.
Basic example for creating a materialized view ,
CREATE MATERIALIZED VIEW mv_emp_pk
REFRESH FAST START WITH SYSDATE
NEXT SYSDATE + 1/1440 ---- > every one minute
WITH PRIMARY KEY -- this is default , no need to specify though
AS SELECT employee_id,name,salary FROM emp@remote_db;
ON COMMIT
In some situations it would be convenient to have Oracle refresh a materialized view automatically whenever changes to the base table are committed. This is possible using the ON COMMIT refresh mode. Here is an example.
Assuming that employee table have primary key , so create a materialized view log on employee table.If your table does'nt have a primary key defined you will get error like ORA-12014: table does not contain primary key constraint.
sql>conn hr/hr
Connected
SQL> create materialized view log on employees with primary key;
Materialized view log created.
You can see following table got created while creating materialized view log on employees table ,
MLOG$_EMPLOYEES:- This is a table created along with the materialized view. It contains data that has changed in the base table.
RUPD$_EMPLOYEES:- This table is created when a materialized view uses primary key for fast refresh. This is used to support updatable materialized views.
SQL> conn / as sysdba
Connected.
SQL> grant select any table to mahi;
Grant succeeded.
SQL> grant create any materialized view to mahi;
Grant succeeded.
SQL> create database link hrlink connect to hr identified by hr using 'study';
Database link created.
SQL> conn mahi/mahi
Connected.
SQL>
SQL> create materialized view mv_hr_employee REFRESH FAST ON COMMIT as select * from employees@hrlink;
create materialized view mv_hr_employee
*
ERROR at line 1:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
While googling I learned following -
Things learned:- This materialized view is selecting from a remote table over a database link (a distributed materialized view). For "on commit", you can use only if you have your master table in the same database where you are creating the materialized view. Therefore, on commit is not supported in remote databases.
SQL> conn hr/hr
Connected.
SQL> create materialized view mv_employee REFRESH FAST ON COMMIT as select * from employees;
Materialized view created.
SQL> select count(1) from employees;
COUNT(1)
----------
107
SQL> select count(1) from mv_employee;
COUNT(1)
----------
107
SQL> delete from employees where EMPLOYEE_ID=206;
1 row deleted.
SQL> select count(1) from employees;
COUNT(1)
----------
106
SQL> select count(1) from mv_employee;
COUNT(1)
----------
107
SQL> commit;
Commit complete.
SQL> select count(1) from mv_employee;
COUNT(1)
----------
106
SQL>
As soon as I put commit , my materialized view got reflected
Check the details of the materialized view using following query ,
SQL> select mview_name, refresh_method, refresh_mode, build_mode, fast_refreshable from user_mviews where mview_name = 'MV_EMPLOYEE';
How to know when was the last refresh happened on materialized views:
SQL> select MVIEW_NAME, to_char(LAST_REFRESH_DATE,'YYYY-MM-DD HH24:MI:SS') from dba_mviews;
(or)
SQL> select MVIEW_NAME, to_char(LAST_REFRESH_DATE,'YYYY-MM-DD HH24:MI:SS') from dba_mview_analysis;
(or)
SQL> select NAME, to_char(LAST_REFRESH,'YYYY-MM-DD HH24:MI:SS') from dba_mview_refresh_times;
Difference between View vs Materialized View in database
Based upon on our understanding of View and Materialized View, Let’s see, some short difference between them :
1) First difference between View and materialized view is that, In Views query result is not stored in the disk or database but Materialized view allow to store query result in disk or table.
2) Another difference between View vs materialized view is that, when we create view using any table, rowid of view is same as original table but in case of Materialized view rowid is different.
3) One more difference between View and materialized view in database is that, In case of View we always get latest data but in case of Materialized view we need to refresh the view for getting latest data.
4) Performance of View is less than Materialized view.
5) Last difference between View vs Materialized View is that, In case of Materialized view we need extra trigger or some automatic method so that we can keep MV refreshed, this is not required for views in database.
Points to note:-
*It is always recommended to gather the statistics of the underlying table after materialized view got created .
*Although materialized view logs improve the performance of materialized view refreshes, they do increase the work needed to perform DDL on the base table.
*If regular refreshes are not performed, materialized view logs can grow very large, potentially reducing the performance of their maintenance and blowing tablespace limits
No comments:
Post a Comment